Stored Procedure Caller Generator
Posted by Wong Shao Voon
This article presents an application which can generate C# methods to call your stored procedure
Rating: 0.00 /5
|Sign Up to vote for this article|
I have recently make a transition from a C++ job to a C# job 4 months ago. There is so many new and exciting stuff for me to learn in the .NET world that I got carried away and wrote a very naive article on calling store procedure from C#. This article presents an application which can generate C# methods to call your stored procedure; I modified the library from previous article to write this Stored Procedure Caller Generator. The old library is called Stored Procedure Caller. The code generation is mainly done by the new SPCallerGen class. It is a very simple class.
SPCG needs the stored procedure signature to generate the method. There are 3 things we need to supply to SPCG: stored procedure signature, method name and the return type of the stored procedure. There are 3 return types to choose from:
Tables option will generate a method which return a
Integer option will return a integer from the stored procedure. And
None option is for stored procedure which returns nothing. For our first example, we will call the
sp_InsertEmp is a very simple store procedure to insert a employee record. There is 1
OUTPUT parameter called
ID which is the new employee
If anyone is interested, this is the table creation script for
Below is the input, we use for SPCG application.
Method Name : InsertEmployee Return type : None
This is the generated method. The developer may need to change the connection string name according to what is already defined in the source file. Notice the ID parameter is a ref type which will hold the outputted value from
sp_InsertEmp. The reader may also notice that all the value types are nullable types. Nullable type declaration sometimes are done by appending
?, than using the full proper declaration, for instance,
Nullable<int>. If the value type parameter is passed by reference to hold the output value, then it is not declared as a nullable type. See no
ID parameter. Developer may want to edit the code to change the nullable type to a normal value type for some parameters which are for table columns which cannot be null.
The second example we use
sp_GetNum as our stored procedure.
sp_GetNum has no parameters for simplistic sake as we have seen all the parameter related tidbits in the previous example.
sp_GetNum returns an integer.
CREATE PROCEDURE [dbo].[sp_GetNum]
This is the values we will supply to the SPCG application.
Method Name : GetNum Return type : Integer
Here is our generated GetNum method. It used
"@RetValue254165" as a return parameter name. The developer may need to edit
"@RetValue254165" to a suitable name. But it is okay to leave the name as it is.
In our last example,
sp_GetAllEmployee is also parameterless stored procedure for brevity sake and it returns a table from the
SQL SELECT statement.
CREATE PROCEDURE [dbo].[sp_GetAllEmployee]
I set Tables as the return type in the SPCG application.
Method Name : InsertEmployee Return type : Tables
As the reader may have noticed in the generated code in the 1st 2 examples that the
SqlCommand.ExecuteNonQuery method is used to execute our SQL command. In the last example which the stored procedure returns a
DataSet, the generated code use data adapter class to populate the
This is the code in the Generate Code button's OnClick event handler. Note: code which validates the input is removed for clarity.
First, we set the return type, then we instantiate the SPSignature object to parse the signature into stored procedure name and the parameters. Each parameter information is stored in the Column class. When SPSignature parses the parameter, it is looking for the parameter name, its T-SQL type, the length of the parameter (which is applicable for string type such as VARCHAR) and lastly its direction (INPUT, INPUTOUTPUT or OUTPUT). Absence of direction information will default it to INPUT.
In the Column class, the _name stores the parameter name and the parameterName is the name which is prepended with a "@". netType stored the .NET type which is mapped to this sqlType. netTypeStr is the C# type string for netType. For example, if the netType is System.Int32, then netTypeStr is "int". sqlType holds the T-SQL type for parameters. direction indicates if the direction is INPUT, INPUTOUTPUT or OUTPUT. nullableType indicates if netType is a value type that is nullable; reference types are not nullable. There is a TypeSetter class which holds the mapping information of the T-SQL type should be mapped to the .NET type. For example, System.Boolean should be mapped to Bit. The mapping information is stored in a Dictionary with sqlType (string) as its key and Column as its value. Below is the method, InitDict, used to initialize the dictionary. Note: I only shown the 1st 3 column assignments for brevity.
FillType method is to fill up the type information based on the sqlType.
Lastly, SPCallerGen class's GenCode which generates the C# ADO.NET code based on the stored procedure name and parameter information. GenCode will call the appropriate code generator method based on the return type.
This is GenDataSetProcCode implementation. Note: I do not show the GenIntProcCode and GenVoidProcCode here because they are similar in nature with minor difference. For readers who are interested, can download and read the source code.
There is all what this simple library does.
In this article, we use the SPCG application to auto-generate 3 examples of the code. There is no doubt developers would save time to focus their effort on code elsewhere. However, I would only recommend developer learn and understand the basics of ADO.NET before using SPCG as a tool to generate code. Every elementary student has to master addition, subtraction, multiplication and division before they are taught to use calculator to help them do these basic arithmetic. This is the same principle. Without simple understanding of ADO.NET, there is always the danger of inexperienced developer not catching the error in the generated code.
Stored Procedure Caller Generator is hosted at Codeplex. Feel free to report any problems that you encounter to Codeplex.
Thank you for reading!
This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)
|Wong Shao Voon||Rather than to write an accolade of skills which I currently possess, these are the technologies, I am currently exploring:|
3D Graphics (Lighting and Shadow)
ASP.NET MVC 4
HTML5 and CSS3
SQL Server 2012
Sign up to post message on the article message board!