Click here to Skip to main content
15,867,771 members
Articles / Programming Languages / JScript .NET
Article

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

Rate me:
Please Sign up or sign in to vote.
4.92/5 (24 votes)
14 Jun 20038 min read 386.4K   3.4K   133   56
Stored Procedure Invocation Code Generator for VB, C# and JScript.NET

Sample Image

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) ...

SQL
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
<?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:

Image 2

SP/Invoke generates the code ...

Image 3

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

Image 4

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

C#
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:

This maintenance release adds support for Microsoft Visual Studio .NET 2003 Edition. The 2002 Edition of Visual Studio is also supported by this release. v1.2 therefore supercedes v1.1 for all Visual Studio .NET users.
  • 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:

    C#
    SqlConnection, SqlCommand, SqlParameter, SqlTransaction.
    These classes are no longer used. Instead, the following provider-independent interfaces are used:

    C#
    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:

    C#
    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

License

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


Written By
Switzerland Switzerland
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionFeatures needed Pin
cbumstead4-Aug-10 18:20
cbumstead4-Aug-10 18:20 
GeneralFREE C# Code Generator - http://www.elencysolutions.co.uk Pin
CroweMan21-Jul-10 1:05
CroweMan21-Jul-10 1:05 
I have evaluated many ORM modellers and code generators over the years, none of them gave me the code I wanted. Over the last few years in my spare time I have created my own stored procedure and C# code generator.

The generator is very intuitive to use and generates CRUD stored procedures, Data Components, Business components and Business Entities. The generated code uses SqlDataReaders for it's data retrieval and no reflection.

It will even generate a n-Unit test project.

Caching support is available through business objects as well as IoC for Data Components.

Performance benchmarks comparing it to the ADO.NET Entity Framework and Linq to SQL can be found on the site.

Find it here http://www.elencysolutions.co.uk
QuestionIs there a way to cancel a async request? Pin
Gongas18-Sep-09 6:54
Gongas18-Sep-09 6:54 
GeneralHint to increase performance Pin
danclax2-Jun-07 22:58
danclax2-Jun-07 22:58 
QuestionHow can i call oracle an stored procedure? Pin
psabatino28-Jul-06 6:00
psabatino28-Jul-06 6:00 
QuestionProblems using SP/Invoke with MSVS2005(.Net 2.0) Pin
RonRejwan27-Jul-06 2:06
RonRejwan27-Jul-06 2:06 
NewsVS2005 Pin
squawkbox7-Jul-06 2:40
squawkbox7-Jul-06 2:40 
GeneralRe: VS2005 Pin
Souheil.25-Aug-06 3:15
Souheil.25-Aug-06 3:15 
GeneralRe: VS2005 Pin
bnieland12-Jun-07 11:29
bnieland12-Jun-07 11:29 
GeneralQuestion about the row classes Pin
Jeffster17-May-06 7:41
Jeffster17-May-06 7:41 
GeneralRe: Question about the row classes Pin
Jeffster18-May-06 12:18
Jeffster18-May-06 12:18 
GeneralNeed VB code to validate if strings are in UTF-8 or not Pin
jolanar3-Apr-06 9:44
jolanar3-Apr-06 9:44 
QuestionWorking with SP/Invoke and .net and SQL 2005? Pin
Daniel Santillanes16-Nov-05 7:32
professionalDaniel Santillanes16-Nov-05 7:32 
GeneralStored procedures generator Pin
Li Xin24-Oct-05 10:15
Li Xin24-Oct-05 10:15 
GeneralA simple utility Pin
chris Liang30-Aug-05 9:18
chris Liang30-Aug-05 9:18 
GeneralRe: A simple utility - a free one! Pin
Thomas Schittli28-Sep-05 3:14
Thomas Schittli28-Sep-05 3:14 
Generaloutput parameters Pin
mohd_osman24-May-05 0:27
mohd_osman24-May-05 0:27 
GeneralAuto generate XML file Pin
odegnan6-Apr-05 3:16
sussodegnan6-Apr-05 3:16 
Questionhow this program run ??? Pin
denise8384052-Sep-04 21:30
denise8384052-Sep-04 21:30 
AnswerRe: how this program run ??? Pin
Anonymous29-Nov-04 3:16
Anonymous29-Nov-04 3:16 
Generalstupid simple problem Pin
Steel City Phantom28-May-04 10:27
Steel City Phantom28-May-04 10:27 
GeneralReturning multiple tables in one stored procedure call Pin
ddehghan5-Oct-03 10:08
ddehghan5-Oct-03 10:08 
GeneralRe: Returning multiple tables in one stored procedure call Pin
Simon Wilson5-Oct-03 13:56
Simon Wilson5-Oct-03 13:56 
Generalinternal access modifier Pin
Trumpi6-Aug-03 3:39
Trumpi6-Aug-03 3:39 
GeneralRe: internal access modifier Pin
Simon Wilson7-Aug-03 2:09
Simon Wilson7-Aug-03 2:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.