Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Utilities » Downloads
 
Add your own
alternative version
Go to top

SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET

, 7 Dec 2002
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.
dbhelper-1.0.zip
DBHelper
CVS
Base
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
SP
CVS
Base
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
SPTestApp
App.ico
CVS
Base
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.1.zip
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.2.zip
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.3.zip
Baserev
Entries
Repository
Root
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.4.zip
Baserev
Entries
Repository
Root
dbh.gif
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.5.demo.zip
SPTestApp.exe
DBHelper.dll
Interop.MSDASC.dll
dbhelper-1.5.src.zip
Baserev
Entries
Repository
Root
dbh.gif
DBHelper.suo
DBHelper.vssscc
Baserev
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-1.6.demo.zip
SPTestApp.exe
DBHelper.dll
Interop.MSDASC.dll
dbhelper-1.6.src.zip
Baserev
Entries
Repository
Root
dbh.gif
DBHelper.suo
DBHelper.vssscc
Baserev
Diff
Entries
Repository
Root
DBHelper.csproj.user
DBHelper.csproj.vspscc
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
SPTestApp.csproj.vspscc
dbhelper-16demo.zip
SPTestApp.exe
DBHelper.dll
Interop.MSDASC.dll
dbhelper-16src.zip
Baserev
Entries
Repository
Root
dbh.gif
Baserev
Entries
Repository
Root
DBHelper.csproj.user
App.ico
Baserev
Entries
Repository
Root
SPTestApp.csproj.user
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
	<head>
		<title></title>
		<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
		<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
	</head>
	<body>
		<h2>What is this?</h2>
		<P>DBHelper is a small library 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 only C# code 
			generation (and other language support is likely in the future), but the 
			generated assembly is usable from any .NET language.</P>
		<h2>Why?</h2>
		<P>Some people might ask "Why?". The answer is sweet. Take the following for 
			example:</P>
		<P>Everytime you even want to execute a simple stored procedure you need something 
			like the following:&nbsp;</P>
		<pre>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();</pre>
		<P>Now thats 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 likeing it to use DataSets only for SELECT 
			queries and I ussually 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. <STRONG>UPDATE:</STRONG>
			I have now updated the library to return a DataTable of the output. For 
			example:</P>
		<P></P>
		<PRE> public static DataTable ChangeEmail(SqlConnection connection
		DataTable table, long id_param, string oldemail_param, string newemail_param)</PRE>
		<p>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.</p>
		<h2>How does it work?</h2>
		<P>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.</P>
		<P><b>Constructor:</b></P>
		<P><code>SP(SqlConnection conn, CodeDomProvider cp);</code> just a reference to a 
			predefined SqlConnection. See example for CodeDomProvider usage.</P>
		<P>Eg <code>SP sp = new SP(myConnection, new CSharpCodeProvider);</code></P>
		<P><b>Properties:</b></P>
		<P><code>public SPCollection Names;</code> returns a strongly typed string 
			collection of all the stored procedure names. This allows you to remove the 
			ones you dont need methods for (as in the UI example).</P>
		<P><b>Methods:</b></P>
		<P><code>public CompilerResults Compile(string ns, string classname, string asmname);</code>
			compiles to an assembly (asmname)&nbsp;using values from Names property&nbsp; 
			to generate. Returns results (primarily for debugging).</P>
		<P><code>public CompilerResults Compile(string[] storedprocs, string ns, string 
				classname, string asmname);</code> compiles to an assembly (asmname) with 
			own defined string array of stored procedures to generate. Returns results 
			(primarily for debugging).</P>
		<P><code>public void CreateFile(string[] storedprocs, string ns, string classname, 
				string filename);</code> Same as compiler, but will output source file.</P>
		<P><code>public void CreateFile(string ns, string classname, string filename);</code>Same 
			as compiler, but will output source file.</P>
		<h2>Notes:</h2>
		<P>I use a very, very simple mapping from SqlDbType to Type and DBNull values are 
			not supported either in this release.</P>
		<p>For the test app to work you will need to change the SqlConnection to your 
			linking.</p>
		<h2>Free Plug</h2>
		<P>Kristopher Johnson - Typed Collection Class Generator for creating all the 
			collections and dictionaries in DBHelper (although the CodeDom code was not 
			looked at).</P>
		<h2>Conclusion</h2>
		<P>This was really my first successfull 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. <STRONG>UPDATE</STRONG>: I have created support for VB.NET code as well.</P>
		<h2>Changlog</h2>
		<pre>
Version 1.0
===========

- Initial release
- C# support

Version 1.1
===========

- VB.NET, JScript.NET support

Version 1.2
===========

- JScript support dropped (VB is confusing enough)
- All generated methods now output a strongly typed DataTable, that can be add to a dataset. 
  Also, all stored procedure types are supported, IOW SELECT, INSERT, UPDATE, etc.

Version 1.3
=========== 
- Now takes a DataTable as an input parameter, allowing to pass a  
  typed dataset table. Passing null will just generate a DataTable based on the SP. 
- Added a static funtion to add TableSchema to typed dataset. 
  It assumes the app is being run from either /bin/Debug or /bin/Release (iow ../../ to the project). 
  To get the DataTable to pass, just run a SP method with the DataTable parameter as null and use
  the returned table.</pre>		
		<P>Cheers :)</P>
	</body>
</html>

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 8 Dec 2002
Article Copyright 2002 by leppie
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid