Stored Procedure Invocation Code Generator for VB, C# and JScript.NET

• Download source files - 680 Kb

Detailed documentation (including a tutorial) is provided in spinvoke_v2_1.pdf which is included in the download.

Summary

SP/Invoke generates code to allow C#, VB or JScript.NET programmers to invoke SQL Server stored procedures as if they were static methods on CLR classes. It was objectnation's submission to Chris Sell's 'Spend a Day With .NET' contest where it won the 'Best Use of SQL' prize (click here for information about the winners). The version featured here includes numerous improvements which were made subsequent to the original submission.

Problems Solved/Technologies Applied

This code serves as a useful example for the following:

• Code generation using the CodeDOM.
• Visual Studio .NET Custom Build Tool development (code originally taken from Chris Sell's XsdClassesGen tool (link here).
• Inspection of SQL Server metadata.

Example Usage

For example, for the following stored procedure (taken from the Northwind SQL Server example database) ...

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
  SELECT
    ProductName, 
    Total=SUM(Quantity)
  FROM 
    Products P, 
    [Order Details] OD, 
    Orders O, 
    Customers C
  WHERE
    C.CustomerID = @CustomerID AND
    C.CustomerID = O.CustomerID AND
    O.OrderID = OD.OrderID AND
    OD.ProductID = P.ProductID
  GROUP BY
    ProductName

... define an XML file in your C#/VB project and describe the stored procedure(s) for which code should be generated:

<?xml version="1.0" encoding="UTF-8" ?>
<storedProcedures>
  <server name="localhost">
    <database name="Northwind">
      <storedProcedureClass source="CustOrderHist">
        <rowClass></rowClass>
      </storedProcedureClass>
    </database>
  </server>
</storedProcedures>

You then associate the XML file with SP/Invoke:

SP/Invoke generates the code ...

... and adds it to the project ...

... allowing you to invoke the stored procedure using ...

  SqlConnection conn = new SqlConnection("...");
  conn.Open();

  CustOrderHist.Result r = CustOrderHist.Invoke(conn, "OTTIK");

  if (r.ReturnValue != 0)
    return;

  foreach (CustOrderHist.Row order in r)
  {
    Console.WriteLine(order.ProductName);
    Console.WriteLine(order.Total);
  }

v1.0 Features:

• Support for transactions. Two overloads are now generated, one with an SqlConnection parameter, the other with an SqlTransaction.
• C#, Visual Basic .NET and JScript .NET code can be generated.
• Support for OUTPUT parameters. Always generates ref params (OUTPUT parameters can be in/out and out-only).
• Row classes can now either be nested or declared directly within the namespace. This allows for multiple stored procedures to share a common row type. It also allows row classes to be factored out into a different assembly.
• A type-safe collection class is also generated for each row type.
• The name of both the row and collection types can be specified.
• Command-line tool allowing for the integration of SP/Invoke into an automated build process.
• Documentation was added, including a tutorial.
• An XML file is used to define how code should be generated. A custom build tool can be associated with the XML file. This build tool will be executed automatically whenever the XML file is modified.

v1.1 Fixes and Features:

• Fix: money and smallmoney are now correctly supported money and smallmoney types are represented as SqlMoney instead of SqlDecimal.
  
• Fix: variable length output parameters are now handled correctly.
  Fixed in/out variable length parameters (i.e. varchar, nvarchar and varbinary).
  Size of parameter was not being specified and results were being truncated. Code is now generated to specify the size of a parameter's buffer from the stored procedure's parameter meta data.
  
• Doc: Explanation of warning when installing the custom build tool with the /codebase switch.
  
• Feature: Inspector Tool.
  Inspector tool added which generates a default XML input file for a database. This is most helpful if SP/Invoke is being added to a project whose database already contains tens or hundreds of stored procedures. Enter objectnation.SPInvoke.Inspector at the command-line for a description of the tool's parameters.
  
• Feature: XML Schema provided. XML Validation Performed using Schema.
  An XML schema is supplied which describes the input XML schema. This schema is used by the SP/Invoke generator to validate input if the following namespace reference is added to the input file's root element:
  
  xmlns="http://www.objectnation.com/Schemas/SPInvoke"
  
  While validation is extremely useful (SP/Invoke will generate validation errors which will help you to find and fix typos etc in your XML), it might cause your existing XML files to break. If this is the case then simply omit the xmlns attribute as described above to return to the v1.0 XML parsing behaviour.
  
• Feature: The visibility of the generated code can be controlled from input XML
  The <storedProcedureClass> and <rowClass> elements support a new attribute: access. This can be used to control the access/visibility of the generated classes. By default, the generated visibility is not public (i.e. internal for C#). A class can be made public by specifying public for the access attribute.
  
  Note: This behaviour is different to v1.0.1 which always generated public types.

v1.2 Fixes and Features:

• Fix: Compilation Error when Compiling under Microsoft Visual Studio .NET 2003 Edition Final Beta
  Microsoft made changes to the IDE extensibility classes for the 2003 edition of Microsoft Visual Studio .NET. As a result, previous versions of SP/Invoke (v1.1 and earlier) fail to compile with the following compilation error:
  
  C:\Visual Studio Projects\SPInvoke\CustomBuild\CustomBuild.cs(243): 'Microsoft.VSDesigner.CodeGenerator.BaseCodeGeneratorWithSite' is inaccessible due to its protection level
  
  objectnation has obtained an alternate implementation of BaseCodeGeneratorWithSite from Microsoft. This code has been integrated into SP/Invoke v1.2. A reference to the Microsoft.Designer assembly is no longer required in the Custom Build project.

v2.0 Fixes and Features:

• Fix: SQL Server authentication failure not caught when server configured for mixed mode
  CustomBuild was not correctly handling SqlExceptions with error code 18452. This has been fixed.
  Thanks to Efran Cobisi at sercom Srl in Italy for isolating this issue.
  
• Fix: DataReader disposed of correctly in generated Invoke method
  IDisposable.Dispose was not being called in all instances when an exception was thrown by IDbCommand.ExecuteReader. This has been fixed.
  Thanks to Efran Cobisi at sercom Srl in Italy for isolating this issue.
  
• Feature: Support added for User-Defined Data-Types
  v2.0 adds support for SQL Server user-defined data-types, which are automatically mapped to their base-types.
  
• Feature: Provider-Independent ADO.NET Types are Used in Generated Code
  Prior to v2.0, SP/Invoke generated code which referenced the following concrete classes from the System.Data.SqlClient namespace:
  
  

  SqlConnection, SqlCommand, SqlParameter, SqlTransaction.

  These classes are no longer used. Instead, the following provider-independent interfaces are used:
  
  

  IDbConnection, IDbCommand, IDbDataParameter, IDbTransaction.

  This enables the use of the OleDb or Oracle managed providers with code generated by SP/Invoke at run-time. SP/Invoke still however, only supports SQL Server at design-time.
  
  Nonetheless, this feature allows the developer to use SQL Server in the development environment but to generate code to target other database servers as well (providing of course that the stored procedure's prototypes/signatures are type-compatible across database servers).
  
• Feature: Support for events, both on a per-stored-procedure and global level
  All generated stored procedure classes now contain a public static property called Events. This object has three public events:
  
  

  PreExecute, PostExecute, Exception.

  These events can be handled to add centralised diagnostic tracing, input parameter validation, SqlException-to-persistence-layer exception mapping etc. to your application.
  
  In addition, the new StoredProcedures class contains an equivalent Events property. Subscribing to these events causes events to be handled for all generated stored procedure classes in the assembly.
  
• Change: All Invoke overloads are now generated with public access
  SP/Invoke generates three overloaded Invoke methods for each stored procedure class. Prior to v2.0, two of these overloads were defined as public, each delegating to a third, private implementation.
  
  The common implementation overload (which takes both IDbConnection and IDbTransaction parameters) is now declared public.
  
  This improvement was suggested by Efran Cobisi at sercom Srl in Italy.
  
• Feature: Support for NDoc-compatible documentation comments added
  All public classes, methods, events and properties are now documented using documentation comments which can be compiled into a help file for automated documentation of the SP/Invoke-generated programming interface.
  
  The documentation comments generated by SP/Invoke were tested with NDoc v1.1.1116.
  
  NDoc is an open-source code documentation generator for .NET which is far superior to the "Build Comment Web Pages..." tool which Microsoft includes with Visual Studio .NET.
  
  For more information on NDoc, visit http://ndoc.sourceforge.net/.
  
• Feature: Generated stored procedure classes marked with StoredProcedureAttribute
  Prior to v2.0, it was was not easily possible to isolate SP/Invoke-generated stored procedures classes using reflection. All stored procedure classes are now marked with the StoredProcedureAttribute class, which also contains the name of the stored procedure encapsulated by the class.

v2.1 Fixes and Features

• Fix: CustomBuild project was not compiling in the release build
  This has been fixed.
  
• Fix: XSD schema required one or more <server> elements, thus preventing creation of common type-only XML file
  This has been fixed. The XSD schema was modified to allow for XML files without <server> or <database> elements.
  
• Feature: Support added for returning the number of rows affected following invocation of stored procedure
  Each generated stored procedure class contains a nested Result class. A new property was added to this class with the name RowsAffected. It contains the number of rows affected by INSERT, UPDATE and DELETE statements in the stored procedure. This value will be -1 if the stored procedure did not execute any of these statements.
  
• Feature: RowsAffected and ReturnValue are available to PostExecute event handlers
  The PostExecuteEventArgs class has been extended to include ReturnValue and RowsAffected properties.
  
• Feature: Access/visibility of generated code can now be controlled for all types using a single attribute
  A new "access" attribute was added to the storedProcedures element which allows the visibility of the generated types to be controlled from a single location within the XML file.

Build/Compilation Information

This version of SP/Invoke is provided in source-code only form. Visual Studio .NET is required to build the assemblies before use. NOTE: spinvoke_v2_1.pdf (in the downloadable .ZIP file) contains detailed instructions for compiling the source code and installing the binaries.

Links

Check objectnation's downloads page for updates. Please send feedback and suggestions to feedback@objectnation.com

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here