Click here to Skip to main content
11,412,516 members (69,739 online)
Click here to Skip to main content

SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

, 7 Dec 2002 BSD
Rate this:
Please Sign up or sign in to vote.
This a small tool that will generate static methods in a class that acts as wrapper for SQL stored procedures. It either outputs a source file or a compiled assembly. Also supports automatic DataSet generation.

What is this?

DBHelper is a small tool that will generate either a source file or a compiled assembly for selected stored procedures on your SQL server. It uses the CodeDom namespace to output the code. Currently it supports C# and VB.NET code generation (and other language support is likely in the future), but the generated assembly is usable from any .NET language. Also included in version 1.5 is the ability to compile a complete assembly that includes a typed DataSet based on your SQL stored procedures output.

Basically, this is an all-in-one tool for wrapping a SQL database. Great for existing databases, and for rapidly changing databases. E.g. changes gets made to stored procedure's parameters or the output of stored procedures. No problem for DBHelper, just a few clicks and you a have all the methods remapped/DataSet regenerated. All you have to do it adjust your corresponding .NET methods.

NOTE: DBNulls are still not supported. I'm still looking for a way. The alternative is to still generate the source file and the XSD file, and change all the method parameters from their strong type to System.Object. That's all! Hopefully there wont be too many!

New information (version 1.5+)

As of version 1.4, the package comes as a tool rather than a library. Please select the SQL provider (no others will work and I have NO plans to support it). If you tick Create, it will create a corresponding method, and CreateStrong will generate an XSD for strongly typed DataSet/Tables.

Also note VS.NET is NO longer required for typed DataSet generation and compilation (overlooked a static method).

Version 1.6 includes plenty code optimization. Also, typed datasets now get created based on return values. The is no more option to select which ones you want. I have tested it successfully on the MASTER database.

Why?

Some people might ask "Why?". The answer is sweet. Take the following for example:

Every time you even want to execute a simple stored procedure you need something like the following:

SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = "ChangeEmail";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", SqlDbType.BigInt).Value = id_param;
cmd.Parameters.Add("@oldemail", SqlDbType.VarChar).Value = oldemail_param;
cmd.Parameters.Add("@newemail", SqlDbType.VarChar).Value = newemail_param;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();

Now that's only 3 parameters, how about some nasty UPDATE procedures with 10+ parameters? That can be a real time waster, especially doing all the type mapping by hand. On the other hand you could always go for DataSets, and let VS.NET do the work. My personal liking is to use DataSets only for SELECT queries and I usually do all the other statements via SqlCommand's as above. The library will generate all the required parameters and present you with a strongly typed wrapper method for the stored procedure.

UPDATE: I have now updated the library to return a DataTable of the output. For example:

public static DataTable ChangeEmail(SqlConnection connection
    DataTable table, long id_param, 
    string oldemail_param, string newemail_param)

The code from the former example is automatically inserted into a class of choice. Now that looks a lot better, and will probably save you countless hours.

How does it work?

These methods are a bit redundant as of version 1.4 as it run as a tool rather than a library. Firstly your database will be queried for stored procedures. After that we get the name and type of the parameters for each procedure. With the help of CodeDom we create a new class to output to a source file or an assembly.

Constructor

  • SP(SqlConnection conn, CodeDomProvider cp);

    Just a reference to a predefined SqlConnection. See example for CodeDomProvider usage.

    E.g. SP sp = new SP(myConnection, new CSharpCodeProvider);

Properties

  • public SPCollection Names;

    Returns a strongly typed string collection of all the stored procedure names. This allows you to remove the ones you don't need methods for (as in the UI example).

Methods

  • public CompilerResults Compile(string ns, string classname, string asmname);

    Compiles to an assembly (asmname) using values from Names property to generate. Returns results (primarily for debugging).

  • public CompilerResults Compile(string[] storedprocs, string ns, string classname, string asmname);

    Compiles to an assembly (asmname) with own defined string array of stored procedures to generate. Returns results (primarily for debugging).

  • public void CreateFile(string[] storedprocs, string ns, string classname, string filename);

    Same as compiler, but will output source file.

  • public void CreateFile(string ns, string classname, string filename);

    Same as compiler, but will output source file.

Notes

I use a very, very simple mapping from SqlDbType to Type and DBNull values are not supported either in this release.

For the test app to work you will need to change the SqlConnection to your linking.

Free plug

Kristopher Johnson - Typed Collection Class Generator for creating all the collections and dictionaries in DBHelper (although the CodeDom code was not looked at).

Conclusion

This was really my first successful use of CodeDom and it seems really powerful, especially if you use it correctly as output to any language should be possible. As usual, bugs, problems, praise, suggestions, gifts can be added below.

UPDATE: I have created support for VB.NET code as well.

Cheers Smile | :)

License

This article, along with any associated source code and files, is licensed under The BSD License

Share

About the Author

leppie
Software Developer
South Africa South Africa
No Biography provided
Follow on   Twitter

Comments and Discussions

 
GeneralRe: Exception running example program Pin
quincykong at 23-May-04 23:46
memberquincykong23-May-04 23:46 
GeneralRe: Exception running example program Pin
MrFishy at 21-Jan-05 2:11
memberMrFishy21-Jan-05 2:11 
GeneralUsing with IDENTITY Field Pin
Daniel Bright at 25-May-03 10:55
memberDaniel Bright25-May-03 10:55 
GeneralRe: Using with IDENTITY Field Pin
leppie at 25-May-03 11:12
memberleppie25-May-03 11:12 
GeneralRe: Using with IDENTITY Field Pin
Daniel Bright at 25-May-03 15:21
memberDaniel Bright25-May-03 15:21 
Generalissue with image data type parameter Pin
andy_net1 at 8-May-03 4:23
memberandy_net18-May-03 4:23 
GeneralRe: issue with image data type parameter Pin
leppie at 8-May-03 8:36
memberleppie8-May-03 8:36 
Generalsql server 7.0 fix Pin
Vorn Mom at 14-Apr-03 13:22
memberVorn Mom14-Apr-03 13:22 
GeneralLeppie Pin
Paul Evans at 31-Mar-03 0:25
memberPaul Evans31-Mar-03 0:25 
GeneralAdded output and return parameters Pin
Andreas Gratz at 20-Mar-03 3:21
sussAndreas Gratz20-Mar-03 3:21 
Generalbug in xsd Pin
uarndt at 14-Mar-03 4:18
memberuarndt14-Mar-03 4:18 
GeneralRe: bug in xsd Pin
leppie at 14-Mar-03 21:39
memberleppie14-Mar-03 21:39 
QuestionHow to Use?? Pin
shobapond at 22-Jan-03 15:57
membershobapond22-Jan-03 15:57 
AnswerRe: How to Use?? Pin
bwical at 26-Feb-03 8:20
memberbwical26-Feb-03 8:20 
GeneralRe: How to Use?? Pin
leppie at 26-Feb-03 8:32
memberleppie26-Feb-03 8:32 
AnswerRe: How to Use?? Pin
leppie at 26-Feb-03 8:29
memberleppie26-Feb-03 8:29 
GeneralSimilar project... Pin
Mauricio Ritter at 19-Dec-02 1:01
memberMauricio Ritter19-Dec-02 1:01 
GeneralRe: Similar project... Pin
leppie at 19-Dec-02 7:13
memberleppie19-Dec-02 7:13 
GeneralExecute Only SP's Pin
OldRob at 26-Nov-02 8:28
memberOldRob26-Nov-02 8:28 
GeneralRe: Execute Only SP's Pin
leppie at 26-Nov-02 11:43
memberleppie26-Nov-02 11:43 
GeneralRe: Execute Only SP's Pin
leppie at 26-Nov-02 15:17
memberleppie26-Nov-02 15:17 
GeneralRe: Execute Only SP's Pin
OldRob at 27-Nov-02 8:42
memberOldRob27-Nov-02 8:42 
GeneralRe: Execute Only SP's Pin
leppie at 27-Nov-02 9:22
memberleppie27-Nov-02 9:22 
Generalmistake Pin
vjedlicka at 24-Nov-02 22:33
membervjedlicka24-Nov-02 22:33 
GeneralRe: mistake Pin
leppie at 25-Nov-02 8:15
memberleppie25-Nov-02 8:15 
GeneralDBNull's important Pin
MarcHoeppner at 19-Nov-02 23:47
memberMarcHoeppner19-Nov-02 23:47 
GeneralRe: DBNull's important Pin
leppie at 20-Nov-02 10:17
memberleppie20-Nov-02 10:17 
GeneralRe: DBNull's important Pin
MarcHoeppner at 20-Nov-02 10:44
memberMarcHoeppner20-Nov-02 10:44 
GeneralRe: DBNull's important Pin
leppie at 20-Nov-02 11:03
memberleppie20-Nov-02 11:03 
GeneralRe: DBNull's important Pin
MarcHoeppner at 20-Nov-02 11:08
memberMarcHoeppner20-Nov-02 11:08 
GeneralRe: DBNull's important Pin
leppie at 20-Nov-02 11:15
memberleppie20-Nov-02 11:15 
GeneralRe: DBNull's important Pin
MarcHoeppner at 20-Nov-02 11:18
memberMarcHoeppner20-Nov-02 11:18 
GeneralRe: DBNull's important Pin
leppie at 26-Nov-02 7:23
memberleppie26-Nov-02 7:23 
GeneralRe: DBNull's important Pin
Erlend at 26-Aug-03 2:43
memberErlend26-Aug-03 2:43 
GeneralRe: DBNull's important Pin
Richard_D at 26-Nov-02 3:33
memberRichard_D26-Nov-02 3:33 
GeneralRe: DBNull's important Pin
leppie at 26-Nov-02 7:16
memberleppie26-Nov-02 7:16 
GeneralRe: DBNull's important Pin
rberman at 13-Mar-03 15:13
memberrberman13-Mar-03 15:13 
GeneralRe: DBNull's important Pin
OldRob at 26-Nov-02 6:57
memberOldRob26-Nov-02 6:57 
GeneralLovely... Pin
Paul Watson at 19-Nov-02 6:53
sitebuilderPaul Watson19-Nov-02 6:53 
GeneralRe: Lovely... Pin
leppie at 19-Nov-02 7:19
memberleppie19-Nov-02 7:19 
GeneralRe: Lovely... Pin
Paul Watson at 19-Nov-02 7:25
sitebuilderPaul Watson19-Nov-02 7:25 
GeneralRe: Lovely... Pin
leppie at 19-Nov-02 7:51
memberleppie19-Nov-02 7:51 
GeneralNew language (VB/JS) support + updates Pin
leppie at 19-Nov-02 0:30
memberleppie19-Nov-02 0:30 
GeneralJScript.NET support removed Pin
leppie at 19-Nov-02 7:22
memberleppie19-Nov-02 7:22 

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
Web04 | 2.8.150427.1 | Last Updated 8 Dec 2002
Article Copyright 2002 by leppie
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid