|
|
<!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 = "SelectUser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", SqlDbType.BigInt).Value = @id;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
</pre>
<P>Now thats only one parameter, 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 statements via SqlCommand's as above. The
library will generate all the required parameters and present you with a
strongly typed wrapper for the stored procedure. For example:</P>
<P></P>
<PRE>public static void SelectUser(System.Data.SqlClient.SqlConnection connection,
long @id)</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);</code> just a reference to a predefined
SqlConnection.</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>public void CreateFile(string ns, string classname, string filename);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>
<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.</P>
<P> </P>
<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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip