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:

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

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 25 of 53 (Total in Forum: 53) (Refresh) FirstPrevNext

Hint to increase performance danclax 23:58 2 Jun '07   This is a very good codegen ! But! I think there is a possibility to slightly increase performance. To do this it is necessary to initialize commands and parameters once a time, not each time when I call Invoke method. Sign In·View Thread·PermaLink How can i call oracle an stored procedure? psabatino 7:00 28 Jul '06   I am using Visual Studio 2003 and C#. The stored procedure is a simply insert in oracle. Thanks a lot. Sign In·View Thread·PermaLink 1.60/5 (7 votes) Problems using SP/Invoke with MSVS2005(.Net 2.0) RonRejwan 3:06 27 Jul '06   SP/Invoke worked great on MSVS2003 (.Net 1.1), no problems. However, when I recently moved my project to .NET 2.0, the compiler could not find the SP/Invoke tool for the XML ("Custom tool not found on system"). I compiled the SPInvoke project on MSVS2005 and re"regasm"ed it, but it did not help as well. Did anyone manage to use this project on .NET 2.0 or had my problem and fixed it? Please help, thanks Sign In·View Thread·PermaLink 2.33/5 (3 votes) VS2005 swg.com 3:40 7 Jul '06   Hi Simon. I can't get you on the email address supplied in this article so I'm posting in the discussion area here. I've just been fortunate enough to stumble across your SPInvoke app (whilst looking for something completely unrelated). Isn't it always the way with CodeProject? Anyway, it's a great little design you have, I congratulate you. I plan to introduce it into our dev process. We use VS2005 and SQL2005 and I've just gone through the app and replaced all the deprecated code. In case you haven't done this yourself, I will happily return the converted app back to you by way of thanks, if you want it (so long as you accept that it's not been fully tested as yet) Additionally, I have used the app slightly differently from your published instructions. I don't know if this applies to earlier versions of VS but in VS2005 you can specify custom build tool for a given file extension. So, instead of leaving the source files as .XML I simply called them .SPI and set up SPInvoke as the build tool. Now I just add an XML file, save it as say MyFile.SPI and I can skip setting up the Custom Tool property. There are some additional registry entries that are needed to get the tool working under VS2005 so I have these too if you want them. I also plan (at some point) to modify it to use a VS DB connection instead of the having the settings in the XML file itself. This would make it a little more robust when sharing projects in a team environment. If you want all this info, I can bundle it up and send it to you. I won't clutter your inbox with this stuff if you've already done it though. Many Thanks --------- ~swg Sign In·View Thread·PermaLink 1.00/5 (1 vote) Re: VS2005 Souheil. 4:15 25 Aug '06   Hi SWG I saw yor reply t hte original post. what you did and planned on doing is what i was hoping to find. I would love to see the changes you have made to the original code along with the reg entries... I was wondering it you could share it with us. Thanks SC. Sign In·View Thread·PermaLink Re: VS2005 bnieland 12:29 12 Jun '07   I would love a copy of the 2.0 version of spinvoke! Any chance? DTrent Sign In·View Thread·PermaLink Question about the row classes Jeffster 8:41 17 May '06   In the row classes there is code like the following: reader.GetValue(1) Is there anyway to configure to use column names like reader["ColumnName"] within these generated classes? I am asking because I have some stored procs that use SELECT * FROM TABLE and I have to change the column orders some times when adding indexes, etc. which breaks the spinvoke generated class. Sign In·View Thread·PermaLink Re: Question about the row classes Jeffster 13:18 18 May '06   Ok, I solved this problem by building my own version where it uses indexers with a column name in the code generation for the rowclasses. I was also wondering how you embed the schema in the resx file in case I want to mod or add some options? Sign In·View Thread·PermaLink Need VB code to validate if strings are in UTF-8 or not jolanar 10:44 3 Apr '06   Hi ; I use Access Data Base and and I need Vb code to validate if the data in field is in UTF-8 or not . Thank's for your help regards Sign In·View Thread·PermaLink Working with SP/Invoke and .net and SQL 2005? daniero 8:32 16 Nov '05   Has anyone made this? I've setup the XML file's custom tool property, and created the databse and a sample stored procedure, but it's not generating the code... may be SQL 2005 changed a lot so this isn't working anymore? daniero Sign In·View Thread·PermaLink 1.00/5 (1 vote) Stored procedures generator Li Xin 11:15 24 Oct '05   Below is link of Lattice.SPGen, a stored procedure generator which support Oracle, SQL Server, and IBM DB2 UDB, Lattice.SPGen can generate business entity class in both C# and VB.net as well. http://www.latticesoft.com Sign In·View Thread·PermaLink 1.67/5 (2 votes) A simple utility chris Liang 10:18 30 Aug '05   A while ago, I searched the internet for a code generator of stored procedures with simple feactures: No further modification to the generated code Synchronizing with procedure changes Directly interacting with data provider methods Flexible for effecient use of ADO.Net functionality Static data binding SQL Server, OleDb, ODBC, Oracle, MySQL data providers There are a lot of such kind but I had no success. Now, I have created one and if you are interested, take a look at http://www.povb.com/posharp Chris Liang Sign In·View Thread·PermaLink 1.67/5 (2 votes) Re: A simple utility - a free one! Thomas Schittli 4:14 28 Sep '05   ... or just use one of the many free tools like MyGeneration: www.mygenerationsoftware.com Kind regards, thomas People who wait until the eleventh hour to call on Jesus die at 10:30. Sign In·View Thread·PermaLink 1.00/5 (1 vote) output parameters mohd_osman 1:27 24 May '05   If suppose we have store procedure as follows CREATE PROCEDURE dbo.GetCount ( @country varchar(11), @Count int OUTPUT ) AS SET NOCOUNT ON Select @Count = count(customerid) from customers where country = @country RETURN GO How do we use this tool for the above kind of stored procedure? GetCount.Result result = GetCount.Invoke(conn, "USA", ref count); Tha above usage gives me an error message Thanks in advance Sign In·View Thread·PermaLink 2.00/5 (1 vote) Auto generate XML file odegnan 4:16 6 Apr '05   Ideally the custom tool should auto generate the XML file with all stored procedures from a given database. (just a thought) Oliver Sign In·View Thread·PermaLink how this program run ??? denise838405 22:30 2 Sep '04   hi i wan to ask how does this program run ?? does this program need any other things in order to run this program code? when i run the code in visual studio.net it prompt me the following error:- 1) Program 'C:\Documents and Settings\ipcl\Desktop\denise work folder\SPInvoke\Visual Studio .NET 2003\SPInvoke\obj\Debug\SPInvoke.exe' does not have an entry point defined 2) C:\Documents and Settings\ipcl\Desktop\denise work folder\SPInvoke\Visual Studio .NET 2003\SPInvoke\StoredProcedures.xml The custom tool 'objectnation.SPInvoke' failed. SQL Server does not exist or access denied. can any plz help me. thanx alot denise Sign In·View Thread·PermaLink Re: how this program run ??? Anonymous 4:16 29 Nov '04   The documentation included with SP/Invoke contains detailed instructions on how to use it from within Visual Studio .NET. Sign In·View Thread·PermaLink stupid simple problem Steel City Phantom 11:27 28 May '04   my procedure inserts a record and ends with "return select @@IDENTITY" now logic would dictate that the returnvalue of my object would have that identity in it, but it doesn't. why? Sign In·View Thread·PermaLink 1.00/5 (1 vote) Returning multiple tables in one stored procedure call ddehghan 11:08 5 Oct '03   First of all I would like to say how amazing this tool is. Great job. Is it possible to return multiple tables in one stored procedure call? -- Objectnation xml <storedProcedureClass source="spReturn2Tables" access="public"> <rowClass /> </storedProcedureClass> --DB Create PROCEDURE [dbo].[spReturn2Tables] AS select * from table1 Select * from table2 GO When I use the above code only the fisrt table is returned as a strong typed object. Thanks David Sign In·View Thread·PermaLink 2.00/5 (1 vote) Re: Returning multiple tables in one stored procedure call Simon Wilson 14:56 5 Oct '03   Hi David I'm afraid that this isn't currently possible. Modifying the generated code to extract from multiple tables wouldn't be very difficult. However, extending the tool's internal meta-data structures to describe multiple result sets would require a good deal of thought and is therefore something that will probably have to wait for the next major release. We don't currently know when the next release will be completed but I doubt it will be before the end of the year - sorry... Best regards, Simon Wilson Sign In·View Thread·PermaLink internal access modifier Code Demon 4:39 6 Aug '03   This tool is very cool.   There is one feature that I would like included and that is to allow the attribute access="internal" within the <storedProcedureClass> element to generate classes with internal access. Sign In·View Thread·PermaLink Re: internal access modifier Simon Wilson 3:09 7 Aug '03   You can use the access attribute on the storedProcedureClass element to specify the access individually for that stored procedure class. See section 8.4 in the documentation. Sign In·View Thread·PermaLink Native XML Database for .NET trevium 14:39 18 Feb '03   I´m looking for a royalty free NAX for .NET. Almost anyone I see, are created in Java. Someone knows any native database written or developed for using in .NET? Sign In·View Thread·PermaLink Re: Native XML Database for .NET ENewton 20:58 27 Mar '04   Try Mattisse, www.mattise.com, a Post-Relational database that isnt exactly an XML database, but more of a Class type of database storage Sign In·View Thread·PermaLink Como Ejecutar STORE Procedure RVC 11:23 29 Jan '03   Como Ejecutar Stored Procedure DB2 desde C# con Oledb IBMDA400.1? Atte. Sign In·View Thread·PermaLink 1.00/5 (1 vote)

Last Visit: 15:00 6 Jul '09     Last Update: 15:00 6 Jul '09 123 Next »