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.
<storedProcedures xmlns="http://www.objectnation.com/Schemas/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 ...
Remember Me