<!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 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.</P>
<P>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 procedures'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.
</P>
<P>NOTE: DBNull 's 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. Thats all!
Hopefully there wont be too many!</P>
<H2>New information (version 1.5):</H2>
<P>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 a XSD for strongly typed DataSet/Tables.</P>
<P>Also note VS.NET is NO longer required for Typed DataSet generation and
compilation (overlooked a static method).</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>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