SP/Invoke is a simple, nice,
lightweight, custom add-in tool for Visual studio.NET to generate wrapper
classes for your Stored Procedures. If you don’t want the baggage of a gigantic
ORM layer but still would like to have ease of SP invocation without having to
worry about underlying types and usual param collection dance, you should
definitely check this out.
All you’d have to do is to add an
XML file with the name of your SP inside with the custom tool property
populated as objectnation.SPInvoke.
<server name="SERVERNAME">
<database name="DATABASENAME">
<storedProcedureClass source="LogAnError">
<rowClass></rowClass>
</storedProcedureClass>
</database>
</server>
</storedProcedures>
...
SP/Invoke generates code allowing you to invoke the stored procedure as easy as
the following.
SqlConnection
conn = new SqlConnection("...");
conn.Open();
LogAnError.Invoke(conn, 0, exp.ToString (), processName,
Assembly.GetExecutingAssembly().GetName().Name,
Assembly.GetExecutingAssembly().GetName().Version.ToString(),string.Empty, 0);
It
also builds transaction and multiple overloads for the methods if you’d want to
use alternate params. The underlying code generated is also really neat and you
can generate NDOC on this very easily.
/// <summary>Wrapper class for
stored procedure [LogAnError]</summary>
///
<remarks>
/// Original stored procedure
parameters:
///
<list type="table">
///
<listheader><term>Name</term><description>Type</description></listheader>
///
<item><term>@pErrNumber</term><description>int</description></item>
///
<item><term>@pErrDescription</term><description>varchar(1000)</description></item>
///
<item><term>@pProcessName</term><description>varchar(128)</description></item>
///
<item><term>@pUserId</term><description>varchar(128)</description></item>
///
<item><term>@pAppVersion</term><description>varchar(20)</description></item>
///
<item><term>@pTableName</term><description>varchar(100)</description></item>
///
<item><term>@pTablePK</term><description>int</description></item>
///
</list>
///
</remarks>
[StoredProcedure("LogAnError")]
class LogAnError {
private
static StoredProcedureEvents _events = new StoredProcedureEvents();
/// <summary>The events supported by the stored procedure</summary>
public
static StoredProcedureEvents Events {
get {
return _events;
}
}
/// <summary>Invokes [LogAnError] on a connection with an optional local
transaction (specify null if the connection does not have an active local
transaction).</summary>
public static
Result Invoke(System.Data.IDbConnection connection, System.Data.IDbTransaction
transaction, System.Data.SqlTypes.SqlInt32 pErrNumber,
System.Data.SqlTypes.SqlString pErrDescription, System.Data.SqlTypes.SqlString
pProcessName, System.Data.SqlTypes.SqlString pUserId,
System.Data.SqlTypes.SqlString pAppVersion, System.Data.SqlTypes.SqlString
pTableName, System.Data.SqlTypes.SqlInt32 pTablePK) {
….
For
the select statements, the corrosponding resultset items can be referenced as
Result.<FieldName> which is strongly typed. Upon passing the parameters
to a stored procedure, the chances of passing in wrong length or data type to
an SP are not a threat anymore because the calls are now strongly typed. Hence,
the invocation method is build using the tyes used in the stored procedure.What
I also liked about SP/Invoke is the ability to support namespace definition, in
case you want the generated code to be used in the same namespace.
As
mentioned on their website
“SP/Invoke generates code
using the CodeDOM and then compiles it to an assembly which can be used from
any VS.NET project. C# code can optionally be generated for direct inclusion
into a C# project.”
References:
SP/Invoke -
Stored Proc Wrapper Code Generator
Sanjay's Coding Tips ::
SP/Invoke -- Stored Procedure Invocation ...
