Introduction

Ever got fed up with creating all the code behind parameters for your stored procedures? I have. So, I wrote this stored proc to do the code for me. (I love code that writes code). This has been written for use with "Microsoft Data Application Block" (SQLHelper.cs), however it could be hacked around to write the code without using the MDAB or even to write the code in VB. The script handles both input and output parameters, setting the size of text types, and you could even use it for direct access to a view or table.

Using the code

To implement, just copy the code into Query Analyzer and run. This will create a SPROC called "tools_CS_SPROC_Builder".

To use, just execute the SPROC passing the name of the SPROC you want the code for (see below). Note: don't include any owner prefix, e.g.: (dbo.).

EXEC tools_CS_SPROC_Builder 'mySprocsNameHere'

The message window in the Query Analyzer will write out all the code required for your class or code behind. There is a variable in "tools_CS_SPROC_Builder" called "@connName" which you can set to the name of your connection instance. By default, it's set to "conn.Connection" - just alter this for your own project's naming convention.

All you then have to do is copy-paste and fill in the values you want to pass and catch any exceptions... (Yes you do have to write some code!).

Points of Interest

In order for me to work out the @col_xtype variable from sysobjects, I ran lots of tests on a table I created with every type variation in both directions. It certainly works fine for all the usual data types, but I haven't had a chance to test all types within .NET, so if you find a bug, let me know. Enjoy!

History

Feb 20, 2006 - updated the script to version 1.1 which includeds two additions:

UniqueIdentifier data type support.

Support for ParameterDirection.Output.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

I have evaluated many ORM modellers and code generators over the years, none of them gave me the code I wanted. Over the last few years in my spare time I have created my own SP and C# code generator.

The generator is very intuitive to use and generates CRUD stored procedures, Data Components, Business components and Business Entities. The generated code uses SqlDataReaders for it's data retrieval and no reflection.

A web administration project can also be generated as well as an nunit test project.

Performance benchmarks comparing it to the ADO.NET Entity Framework and Linq to SQL can be found on the site.

I took the liberty of adding a couple of things to the script to fit some of my projects. Here's what I added:

a) Updated to include 'Scale' for 'Numeric' and 'Decimal'.b) Updated to include 'Precision' for 'Numeric' and 'Decimal'.c) Added the column type (@col_xtype) for types that are "missing" from the definition for further debugging.

If you get a chance have a look at GENNIT @ http://gennit.com it is a code generator tool which uses industrial strength foundations (NHibernate / ActiveRecord) to build a DAL from scratch (as well as other generators such as XHTML and Facebook API), all using online tools - you download a zip file containing the VS project with everything you designed, ready to go.

This is a good way. However, consider a professional web project, you may want to use SqlHelperParameterCache from Microsoft instead.

SqlHelperParameterCache has overhead if your project only calls most stored procedures once in a life time. Of course that's not the real case because most web projects are not to be used for only 1 visit then scrap the website.

How about a SQL stored procedure generator and c# DAL generator for almost nothing! Then check out my www.dashSQL.com. dashSQL generaters all the code for stored procedures and as well as a base data access layer and then another c# DAL class that gets created per your table. No need for parameter types, int, varchar, etc. All parameters in the dashSQL code generator are strings!! SQL automatically converts everything for you! Give it a try!www.dashsql.com

A Visual Studio add-in that would allow you to generate C# calling code for a sproc simply by right-clicking the sproc in the server explorer and selecting "generate C# method". It would then analyze the sproc and generate a corresponding C# static class with methods to execute the sproc, get the SqlParameter[] for the sproc, overloads to execute the sproc with a specified connection, transaction, etc...

The method would have overloads for supplying connection, transactions, etc. Additionally, there would be more methods in the class that would just generate the SqlCommand with parameters and connection, allowing the consumer code to actually perform the execution. This would be important as many data tiers have custom execution with custom error handling.

CREATE PROCEDURE tools_CS_SPROC_Builder_2005(@objName nvarchar(100),@Executetype nvarchar(100) -- ExecuteNonQuery -or- ExecuteReader -or- ExecuteScalar)AS/*___________________________________________________________________Name: CS SPROC BuilderVersion: 1Date: 10/09/2004Author: Paul McKenzieDescription: Call this stored procedue passing the name of your database object that you wish to insert/update from .NET (C#) and the code returns code to copy and paste into your application. This version is for use with "Microsoft Data Application Block".

DECLARE cur CURSOR FOR SELECT * FROM #t_obj OPEN cur -- Perform the first fetch. FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype

--if(@source_type=N'U') SET @parameterAt='' -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN SET @col_redef=(SELECT CASE @col_xtype WHEN 34 THEN 'Binary' WHEN 35 THEN 'String' WHEN 36 THEN 'Guid' WHEN 48 THEN 'Byte' WHEN 52 THEN 'Int16' WHEN 56 THEN 'Int32' WHEN 58 THEN 'DateTime' WHEN 59 THEN 'Single' WHEN 60 THEN 'Currency' WHEN 61 THEN 'DateTime' WHEN 62 THEN 'Double' WHEN 99 THEN 'String' WHEN 104 THEN 'Boolean' WHEN 106 THEN 'Decimal' WHEN 122 THEN 'Currency' WHEN 127 THEN 'Int64' WHEN 165 THEN 'Binary' WHEN 167 THEN 'String' WHEN 173 THEN 'Binary' WHEN 175 THEN 'String' WHEN 231 THEN 'String' WHEN 239 THEN 'String' ELSE '!MISSING' END AS C)

DECLARE cur CURSOR FOR SELECT * FROM #t_obj OPEN cur -- Perform the first fetch. FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype

--if(@source_type=N'U') SET @parameterAt='' -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN SET @col_redef=(SELECT CASE @col_xtype WHEN 34 THEN 'Binary' WHEN 35 THEN 'String' WHEN 36 THEN 'Guid' WHEN 48 THEN 'Byte' WHEN 52 THEN 'Int16' WHEN 56 THEN 'Int32' WHEN 58 THEN 'DateTime' WHEN 59 THEN 'Single' WHEN 60 THEN 'Currency' WHEN 61 THEN 'DateTime' WHEN 62 THEN 'Double' WHEN 99 THEN 'String' WHEN 104 THEN 'Boolean' WHEN 106 THEN 'Decimal' WHEN 122 THEN 'Currency' WHEN 127 THEN 'Int64' WHEN 165 THEN 'Binary' WHEN 167 THEN 'String' WHEN 173 THEN 'Binary' WHEN 175 THEN 'String' WHEN 231 THEN 'String' WHEN 239 THEN 'String' ELSE '!MISSING' END AS C)

Besides, I've just discovered that you can call ExecuteDataSet/ExecuteNonQuery(ConnectionString, StoredProcedureName, object1, object2,...)where object1 is value to pass for Parameter1, object2 us value to pass for Parameter2, etc...(SqlHelper will automatically discover the parameter types & assign the values accordingly)

It uses DerivedParameters (i'm assuming) to determine all of the parameter information. So yes, there's overhead, but it's only overhead once. It then caches the parameters. Then, each time something else calls the same stored procedure, the parameters are pulled from the cache.

So, you get the power of Derived parameters, and the speed of cached SqlParameter objects.

To produce the code, you can use the SqlHelperParameterCache to retrieve the parameters, then loop through them and create

param(0).value = null;param(2).value = null;param(3).value = null;

etc.

Actually, the code is so simple anyway, when you reach that point, maybe you don't need the generator at all.

Hi Interesting idea but not so applicable to the "Microsoft Data Application Block".

Maybe I use a different version of the MDAB, but isn't there a set of overloaded methods with params object array? These useinbuilt parameter discovery functions which cache the parameters. - This would make the example code something like the following (assumingthe values are in the variables specified):try { SqlHelper.ExecuteNonQuery(conn.Connection, CommandType.StoredProcedure, "usp_Insert_Organisation", organisationid, DisplayName, DefaultCurrencyFID, TaxCode, UserFID, IsClient, IsContractor, IsSupplier, IsDesigner);

True enough. On a 2Ghz class system, I can do about 500 DeriveParamaters() per second against a simple stored procedure. This would probably not be acceptable if performed within a loop. However, it is a good practice to cache the parameters. The Microsoft Data Application Block can help out with caching the SqlParameters.

Incidentally, the MS Data Block also has a considerable overhead in calls to functions such as SqlHelper.ExecuteNonQuery() as compared with the SqlClient's ExecuteNonQuery(). These calls do tend to be inside loops or other very frequently used code.

I've used codesmith... great tool. The thing that I've found though it that I end up re-writing most of the automatically generated code in the end anyway. My aim here was to make it quick, simple and easily portable (one script). A tool that generates *.cs would be quite cool however, like codesmith, I suspect that most of the output may end up re-written anyway.

A tool that generates *.cs would be quite cool however, like codesmith, I suspect that most of the output may end up re-written anyway.

I'm surprised you say that. My CodeSmith DAL template, DAL.cst generates code that I NEVER edit - that's the whole point of the template. I just stick each generated method in a class that inherits from my base class and off I go. I never have to worry about bugs in that part of the code...