<!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: </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) using values from Names property
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>New information (version 1.4):</h2>
<P>The new 1.4 version 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 a XSD for strongly typed DataSet/Tables.</P>
<P>Also new in this version is auto typed dataset compilation (yes, allmost all
the SQL functions you need in ONE precompiled assembly!). For this to work, you
will need VS.NET and you must reference Microsoft.VSDesigner.dll from the
Common7\IDE directory. If you donot have VS.NET, you will need to undefine
VSNET in form1.cs in the SPTestApp project. You will lose this functionality as
well, but you can allways use the commandline compiler to generate the classes
from from the generated XSD file. Then manually compiler the 2 source files
into one assembly.</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>
Submit an article or tip