Stored Procedure Caller Generator

• Download SPCallerGenSrc.zip-31.55KB

Stored Procedure Caller Generator

• Introduction
• 1st Example: Stored Procedure Returns No Value
• 2nd Example: Stored Procedure Returns Integer
• 3rd Example: Stored Procedure Returns Table
• How it works
• Conclusion
• History

Introduction

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.

1st Example: Stored Procedure Returns No Value

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, Integer and None. Tables option will generate a method which return a DataSet. 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. 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 ID.

CREATE PROCEDURE [dbo].[sp_InsertEmp]( @ID int OUTPUT, @Name nvarchar(30), @Title varchar(20), @Address varchar(30), @Salary money, @JoinedDate datetime, @Children tinyint)

If anyone is interested, this is the table creation script for Employee.

CREATE TABLE [dbo].[Employee]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](30) NOT NULL, [Title] [varchar](20) NOT NULL, [Address] [varchar](30) NOT NULL, [Salary] [money] NOT NULL, [JoinedDate] [datetime] NOT NULL, [Children] [tinyint] NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

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 ? for 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.

public void InsertEmployee( ref int ID, string Name, string Title, string Address, decimal? Salary, DateTime? JoinedDate, byte? Children) { SqlConnection connection = new SqlConnection(ConnectionStr); try { connection.Open(); SqlCommand command = new SqlCommand("[dbo].[sp_InsertEmp]", connection); command.CommandType = CommandType.StoredProcedure; SqlParameter parameter = null; parameter = new SqlParameter("@ID", SqlDbType.Int); parameter.Direction = System.Data.ParameterDirection.Output; parameter.Value = ID; command.Parameters.Add(parameter); parameter = new SqlParameter("@Name", SqlDbType.NVarChar, 30); if (Name == null) parameter.Value = DBNull.Value; else parameter.Value = Name; command.Parameters.Add(parameter); parameter = new SqlParameter("@Title", SqlDbType.VarChar, 20); if (Title == null) parameter.Value = DBNull.Value; else parameter.Value = Title; command.Parameters.Add(parameter); parameter = new SqlParameter("@Address", SqlDbType.VarChar, 30); if (Address == null) parameter.Value = DBNull.Value; else parameter.Value = Address; command.Parameters.Add(parameter); parameter = new SqlParameter("@Salary", SqlDbType.Money); if (Salary == null) parameter.Value = DBNull.Value; else parameter.Value = Salary; command.Parameters.Add(parameter); parameter = new SqlParameter("@JoinedDate", SqlDbType.DateTime); if (JoinedDate == null) parameter.Value = DBNull.Value; else parameter.Value = JoinedDate; command.Parameters.Add(parameter); parameter = new SqlParameter("@Children", SqlDbType.TinyInt); if (Children == null) parameter.Value = DBNull.Value; else parameter.Value = Children; command.Parameters.Add(parameter); command.ExecuteNonQuery(); ID = Convert.ToInt32(command.Parameters["@ID"].Value); } catch (Exception exp) { throw exp; } finally { connection.Close(); } return; }

2nd Example: Stored Procedure Returns Integer

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.

public int GetNum() { int RetValue = -1; SqlConnection connection = new SqlConnection(ConnectionStr); try { connection.Open(); SqlCommand command = new SqlCommand("[dbo].[sp_GetNum]", connection); command.CommandType = CommandType.StoredProcedure; SqlParameter parameterRet = new SqlParameter("@RetValue254165", SqlDbType.Int); parameterRet.Direction = System.Data.ParameterDirection.ReturnValue; parameterRet.Value = -1; command.Parameters.Add(parameterRet); command.ExecuteNonQuery(); RetValue = Convert.ToInt32(command.Parameters["@RetValue254165"].Value); } catch (Exception exp) { throw exp; } finally { connection.Close(); } return RetValue; }

3rd Example: Stored Procedure Returns Table

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 DataSet.

public DataSet GetAllEmployee() { DataSet ds = new DataSet(); SqlConnection connection = new SqlConnection(ConnectionStr); try { connection.Open(); SqlCommand command = new SqlCommand("[dbo].[sp_GetAllEmployee]", connection); command.CommandType = CommandType.StoredProcedure; SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(ds); } catch (Exception exp) { throw exp; } finally { connection.Close(); } return ds; }

How it works

This is the code in the Generate Code button's OnClick event handler. Note: code which validates the input is removed for clarity.

private void btnGenCode_Click(object sender, EventArgs e) { SPCallerGen.ReturnType retType = SPCallerGen.ReturnType.Tables; if(radioTables.Checked) retType = SPCallerGen.ReturnType.Tables; else if (radioInteger.Checked) retType = SPCallerGen.ReturnType.Integer; else if (radioNone.Checked) retType = SPCallerGen.ReturnType.None; else { MessageBox.Show("No return type is selected."); return; } SPSignature signature = new SPSignature(); string code = null; try { signature.Parse(txtSignature.Text); code = SPCallerGen.GenCode(signature, txtMethodName.Text, retType); } catch (System.Exception ex) { MessageBox.Show("Error:" + ex.Message); return; } if (string.IsNullOrEmpty(code) == false) { System.Windows.Forms.Clipboard.SetText(code); MessageBox.Show("The auto-generated code has been copied to clipboard!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); } else MessageBox.Show("No code is generated", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }

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.

public class Column { public Column() { netType = System.Type.GetType("System.Int32"); netTypeStr = "int"; sqlType = SqlDbType.Int; _name = null; parameterName = null; direction = ParameterDirection.Input; length = 0; nullableType = false; } private string _name; public Type netType; public string netTypeStr; public SqlDbType sqlType; public string name { get { return _name; } set { _name = value; parameterName = "@" + _name; } } public string parameterName { get; private set; } public ParameterDirection direction; public uint length; public bool nullableType; }

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.

private static void InitDict() { if (dict == null) return; Column col = null; col = new Column(); col.netType = System.Type.GetType("System.Int64"); col.netTypeStr = "Int64"; col.sqlType = SqlDbType.BigInt; col.nullableType = true; dict.Add("BigInt".ToLower(), col); col = new Column(); col.netType = System.Type.GetType("System.Byte[]"); col.netTypeStr = "byte[]"; col.sqlType = SqlDbType.Binary; col.nullableType = false; dict.Add("Binary".ToLower(), col); col = new Column(); col.netType = System.Type.GetType("System.Boolean"); col.netTypeStr = "bool"; col.sqlType = SqlDbType.Bit; col.nullableType = true; dict.Add("Bit".ToLower(), col); }

FillType method is to fill up the type information based on the sqlType.

public static bool FillType(string sqlType, Column col) { lock (dict) { string lower = sqlType.ToLower(); if (dict.ContainsKey(lower)) { Column found = dict[lower]; col.netType = found.netType; col.sqlType = found.sqlType; col.nullableType = found.nullableType; col.netTypeStr = found.netTypeStr; return true; } } throw new System.IO.InvalidDataException("Type cannot be found : " + sqlType.ToString()); }

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.

public static string GenCode(ISignature signature, string spname, ReturnType retType) { if (retType == ReturnType.Tables) return GenDataSetProcCode(signature, spname); else if (retType == ReturnType.Integer) return GenIntProcCode(signature, spname); else if (retType == ReturnType.None) return GenVoidProcCode(signature, spname); return null; }

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.

public static string GenDataSetProcCode(ISignature signature, string spname) { string msg = string.Empty; if (string.IsNullOrEmpty(spname)) { msg = "spname is empty!"; throw new InvalidDataException(msg); } StringBuilder strBuilder = new StringBuilder(10000); // write function declaration. strBuilder.Append("public DataSet " + spname + "("); for (int i = 0; i < signature.Columns.Count; ++i) { Column col = signature.Columns[i]; if (col.direction != ParameterDirection.Input) // output type. { strBuilder.Append("\n ref " + col.netTypeStr + " " + col.name); } else { if (col.nullableType) strBuilder.Append("\n " + col.netTypeStr + "? " + col.name); else strBuilder.Append("\n " + col.netTypeStr + " " + col.name); } if (i != (signature.Columns.Count - 1)) strBuilder.Append(","); else strBuilder.Append(")\n"); } if (signature.Columns.Count < = 0) strBuilder.Append(")\n"); strBuilder.Append("{\n"); strBuilder.Append(" DataSet ds = new DataSet();\n"); strBuilder.Append(" SqlConnection connection = new SqlConnection(ConnectionStr);\n"); strBuilder.Append(" try\n"); strBuilder.Append(" {\n"); strBuilder.Append(" connection.Open();\n\n"); msg = " SqlCommand command = new SqlCommand(\"" + signature.Name + "\", connection);\n"; strBuilder.Append(msg); strBuilder.Append(" command.CommandType = CommandType.StoredProcedure;\n\n"); if (signature.Columns.Count > 0) strBuilder.Append(" SqlParameter parameter = null;\n"); List<Column> listInputOutput = new List<Column>(); for (int i = 0; i < signature.Columns.Count; ++i) { Column col = signature.Columns[i]; if (col.direction == ParameterDirection.InputOutput) { listInputOutput.Add(col); } else if (col.direction == ParameterDirection.Output) { listInputOutput.Add(col); } if (col.length == 0) { msg = " parameter = new SqlParameter(\"" + col.parameterName + "\", SqlDbType." + col.sqlType.ToString() + ");\n"; } else { msg = " parameter = new SqlParameter(\"" + col.parameterName + "\", SqlDbType." + col.sqlType.ToString() + ", " + col.length + ");\n"; } strBuilder.Append(msg); if (col.direction == ParameterDirection.InputOutput) { strBuilder.Append(" parameter.Direction = System.Data.ParameterDirection.InputOutput;\n"); } else if (col.direction == ParameterDirection.Output) { strBuilder.Append(" parameter.Direction = System.Data.ParameterDirection.Output;\n"); } if ((col.nullableType && col.direction == ParameterDirection.Input) || col.netType == System.Type.GetType("System.Byte[]") || col.netType == System.Type.GetType("System.String")) // if the parameter can be null, check for null! { strBuilder.Append(" if (" + col.name + " == null)\n"); strBuilder.Append(" parameter.Value = DBNull.Value;\n"); strBuilder.Append(" else\n"); strBuilder.Append(" parameter.Value = " + col.name + ";\n"); strBuilder.Append(" command.Parameters.Add(parameter);\n\n"); } else // if the parameter cannot be null, assign directly { strBuilder.Append(" parameter.Value = " + col.name + ";\n"); strBuilder.Append(" command.Parameters.Add(parameter);\n\n"); } } strBuilder.Append(" SqlDataAdapter adapter = new SqlDataAdapter(command);\n"); strBuilder.Append(" adapter.Fill(ds);\n\n"); // assign back all the InputOutput values foreach (Column col in listInputOutput) { if (col.netType.ToString().IndexOf("Byte[]") == -1) // not a byte array type { string sType = col.netType.ToString().Substring(7); msg = " " + col.name + " = Convert.To" + sType + "(command.Parameters[\"" + col.parameterName + "\"].Value);\n\n"; } else // cast to byte array { msg = " " + col.name + " = (byte[])" + "(command.Parameters[\"" + col.parameterName + "\"].Value);\n\n"; } strBuilder.Append(msg); } strBuilder.Append(" }\n"); strBuilder.Append(" catch (Exception exp)\n"); strBuilder.Append(" {\n"); strBuilder.Append(" throw exp;\n"); strBuilder.Append(" }\n"); strBuilder.Append(" finally\n"); strBuilder.Append(" {\n"); strBuilder.Append(" connection.Close();\n"); strBuilder.Append(" }\n"); strBuilder.Append(" return ds;\n"); strBuilder.Append("}\n"); return strBuilder.ToString(); }

There is all what this simple library does.

Conclusion

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!

History

• 2011-07-12 : 1st release
• 2011-07-13 : Added code explanation

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

About the author