Code smarter, not harder

I have some SQL stored procedures that munge data from quite complex table structures. One such stored procedure fetches me twenty seven columns of data, all of which needs mapping into a suitably created C# object so I can process it. I can’t just use a dataset because the data needs verifying, processing and formatting for display depending on complex bits of logic.

I’m using the SqlParameter and SqlCommand objects to communicate with the database, resulting is code like this:
[sourcecode language="csharp"]
SqlParameter[] paramsToStore = new SqlParameter[10];
#region paramsToStore
paramsToStore[0] = new SqlParameter(”@category_ref”, SqlDbType.Int);
paramsToStore[0].Value = category_ref;
paramsToStore[1] = new SqlParameter(”@metal_ref”, SqlDbType.Int);
paramsToStore[1].Direction = ParameterDirection.Output;
paramsToStore[2] = new SqlParameter(”@grade_ref”, SqlDbType.Int);
paramsToStore[2].Direction = ParameterDirection.Output;
paramsToStore[3] = new SqlParameter(”@finish_ref”, SqlDbType.Int);
paramsToStore[3].Direction = ParameterDirection.Output;
// and so on …
[/sourcecode]

And then some rather tedious
[code='csharp']
SqlCommand command = m_xSqlConnection.CreateCommand();
#region paramsAdd
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(paramsToStore[0]);
command.Parameters.Add(paramsToStore[1]);
command.Parameters.Add(paramsToStore[2]);
// blah blah …
command.CommandText =”HG_stock_categories_single”;
#endregion paramsAdd
command.ExecuteNonQuery();
[/code]

And if that’s not enough to drive database programmers insane, the next bit will surely finish off any remaining sanity:
[code='csharp']
output.category_ref = category_ref;
output.category_code = paramsToStore[9].Value.ToString();
output.back_to_back = (bool)paramsToStore[8].Value;
[/code]

Now it’s not exactly hard code to produce, being a fairly straight-forward copy and paste exercise… until you need to do it seven times over, with large amounts of fields. It can easily consume an hour mapping all this crap up. Fortunately some clever person has written a stored procedure that generates most of that code. I was given this magic bit of SQL and life was great and easy for a while.

Then I thought it’d be a great idea to modify the stored proc so it generated code that was exactly what I wanted - no time wasted on renaming things, writing function headers, error checking, etc. So I modified the code slightly

Stored Procedure C# builder

Just execute that in your SQL client then run it. It will ask for one input value which should be the name of your stored procedure to generate code for.

This was fantastic, I could now write a stored procedure, run it through this code generator and paste the resulting C# into my application. The only drawback was when the stored procedure I was working on wanted inputs, rather than outputs (saving stuff to the database, rather than querying it). I had to go through a tedious and fiddly session of mapping the C# variables to their SQL parameters.

So I got busy again

GetDBType Scalar SQL Function
GetDBType Scalar SQL Function C# version
Stored Procedure C# builder, Input version

To make this work you first have to execute the two fucntion definitions (probably changing them to “CREATE” not “ALTER”) as they are used by the stored procedure. Then execute the stored procedure’s SQL to add it to your database.

If this is all a bit confusing, then in short:

These SQL stored procedures automatically generate the code I showed you at the start of this post. Just feed them the stored procedure you want to use in your C# app and collect the code at the other end. At worse you will have to do a search-and-replace to rename things to fit your code, but that’s it.

Visit my other sites: Photo Gallery | Insane in the Membrane | Main website

Tags: automation, blog365, csharp, Programming, sql, stored procedure