Click here to Skip to main content
Click here to Skip to main content

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

, 14 Jun 2003
Rate this:
Please Sign up or sign in to vote.
Stored Procedure Invocation Code Generator for VB, C# and JScript.NET
<!--

Note:

A windows installer package (i.e. setup) for Visual Studio .NET 2003 can be downloaded here. Visual Studio .NET 2002 users should download from here.

-->

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

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:

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:

    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

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

Share

About the Author

Simon Wilson

Switzerland Switzerland
No Biography provided

Comments and Discussions

 
QuestionFeatures needed Pinmembercbumstead4-Aug-10 19:20 
GeneralFREE C# Code Generator - http://www.elencysolutions.co.uk PinmemberCroweMan21-Jul-10 2:05 
QuestionIs there a way to cancel a async request? PinmemberGongas18-Sep-09 7:54 
I have a particular problem:
 
Imagine the following scenario:
 
I have a search button that will launch a SP.Invoke ... I've added code to this project where you can actually set Sql Command Timeout parameter in run-time.
What I want is to be able to cancel that SP.Invoke if another search is made.
At the moment, a SP.Invoke can go for too long, and it will block the correspondent dataset, and no new requests will be made. This results in problems: if an user performs a search (sp.invoke) with parameters that will make the query long enough, no new searchs will be made till that first sp.invokes get's back from the sql server (which can takes hours).
 
What i what is to be able to release that invokation (and therefore release the correspondent dataset), as in Sp.CancelInvoke (which, of course, does not exist), so that if an user presses search (one sp.Invoke) then press search again, the first sp.invoke gets canceled and another can be made.
 
Any thoughts on this?
 
Thx for your attention,
 
Gonçalo
GeneralHint to increase performance Pinmemberdanclax2-Jun-07 23:58 
QuestionHow can i call oracle an stored procedure? Pinmemberpsabatino28-Jul-06 7:00 
QuestionProblems using SP/Invoke with MSVS2005(.Net 2.0) PinmemberRonRejwan27-Jul-06 3:06 
NewsVS2005 Pinmemberswg.com7-Jul-06 3:40 
GeneralRe: VS2005 PinmemberSouheil.25-Aug-06 4:15 
GeneralRe: VS2005 Pinmemberbnieland12-Jun-07 12:29 
GeneralQuestion about the row classes PinmemberJeffster17-May-06 8:41 
GeneralRe: Question about the row classes PinmemberJeffster18-May-06 13:18 
GeneralNeed VB code to validate if strings are in UTF-8 or not Pinmemberjolanar3-Apr-06 10:44 
QuestionWorking with SP/Invoke and .net and SQL 2005? Pinmemberdaniero16-Nov-05 8:32 
GeneralStored procedures generator PinsussLi Xin24-Oct-05 11:15 
GeneralA simple utility Pinmemberchris Liang30-Aug-05 10:18 
GeneralRe: A simple utility - a free one! PinmemberThomas Schittli28-Sep-05 4:14 
Generaloutput parameters Pinmembermohd_osman24-May-05 1:27 
GeneralAuto generate XML file Pinsussodegnan6-Apr-05 4:16 
Questionhow this program run ??? Pinmemberdenise8384052-Sep-04 22:30 
AnswerRe: how this program run ??? PinsussAnonymous29-Nov-04 4:16 
Generalstupid simple problem PinmemberSteel City Phantom28-May-04 11:27 
GeneralReturning multiple tables in one stored procedure call Pinmemberddehghan5-Oct-03 11:08 
GeneralRe: Returning multiple tables in one stored procedure call PinsussSimon Wilson5-Oct-03 14:56 
Generalinternal access modifier PinmemberCode Demon6-Aug-03 4:39 
GeneralRe: internal access modifier PinsussSimon Wilson7-Aug-03 3:09 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 15 Jun 2003
Article Copyright 2002 by Simon Wilson
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid