Stored Procedure Caller Generator

Introduction

I have recently made the transition from a C++ job to a C# job. There are so many new and exciting things 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 that can generate C# methods to call your stored procedure; I modified the library from the 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 three things we need to supply to SPCG: stored procedure signature, method name, and the return type of the stored procedure. There are three return types to choose from: Tables, Integer and None. The Tables option will generate a method, which return a DataSet. The Integer option will return an integer from the stored procedure. The None option is for stored procedures that return nothing. For our first example, we will call the sp_InsertEmp. sp_InsertEmp is a very simple store procedure to insert an employee record. There is one 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 is done by appending ?, rather than using the full proper declaration, for instance, Nullable. 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. Developers may want to edit the code to change the nullable type to a normal value type for some parameters that are for table columns that 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;
}

Stored Procedure Caller Generator

2nd Example: Stored Procedure Returns Integer

In the second example, we use sp_GetNum as our stored procedure. sp_GetNum has no parameters, for simplisities sake, as we have seen all of the parameter related tidbits in the previous example. sp_GetNum returns an integer.

CREATE PROCEDURE [dbo].[sp_GetNum]

These are 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 parameter-less 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 first two examples, the SqlCommand.ExecuteNonQuery method is to execute our SQL command. In the last example, in which the stored procedure returns a DataSet, the generated code uses the 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;
}

4th Example: Stored Procedure With Table-Value Parameter

For the fourth example, we will pass a table of EmpType type to the stored procedure, sp_InsertManyEmp. This is the sql script we will use. sp_InsertManyEmp inserts the table's records into the Employee table.

CREATE TYPE EmpType AS TABLE
(
	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
);

GO

CREATE PROCEDURE [dbo].[sp_InsertManyEmp](
	@RowsInserted int OUTPUT,
	@Employees EmpType READONLY)
AS
	INSERT INTO [dbo].[Employee] ([Name], [Title], [Address], [Salary], [JoinedDate], [Children]) 
	SELECT [Name], [Title], [Address], [Salary], [JoinedDate], [Children] FROM @Employees;
	Set @RowsInserted = @@ROWCOUNT;
GO

Below is the signature of the sp_InsertManyEmp that we will use to input into SPCG.

CREATE PROCEDURE [dbo].[sp_InsertManyEmp](
	@RowsInserted int OUTPUT,
	@Employees EmpType READONLY)

These are the application options we will use.

Method Name : InsertManyEmployees
Return type : None

Upon clicking the Generate Code button, SPCG detected the presence of a custom table type, through the READONLY keyword in the stored procedure signature and developer will be presented with another dialog to input the table signature. Below is an example of what is input.

CREATE TYPE EmpType AS TABLE
(
	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
);

The EmpType class is generated as below. A method to convert a list of EmpType objects into a DataTable is also generated. Note: Inside InsertManyEmployees, the DataTable is passed into the SqlParameter as SqlDbType.Structured.

public class EmpType
{
    // Default Constructor
    public EmpType()
    {
        Name = null;
        Title = null;
        Address = null;
        Salary = 0m;
        JoinedDate = new DateTime();
        Children = null;
    }

    // Constructor
    public EmpType(
        string NameTemp,
        string TitleTemp,
        string AddressTemp,
        decimal SalaryTemp,
        DateTime JoinedDateTemp,
        byte? ChildrenTemp)
    {
        Name = NameTemp;
        Title = TitleTemp;
        Address = AddressTemp;
        Salary = SalaryTemp;
        JoinedDate = JoinedDateTemp;
        Children = ChildrenTemp;
    }

    public string Name;
    public string Title;
    public string Address;
    public decimal Salary;
    public DateTime JoinedDate;
    public byte? Children;
}

DataTable GetDataTable(List<EmpType> list)
{
    if(list==null)
        return null;
    if(list.Count<=0)
        return null;

    //Create DataTable and add Columns
    DataTable tbl = new DataTable();
    tbl.Columns.Add("Name", System.Type.GetType("System.String"));
    tbl.Columns.Add("Title", System.Type.GetType("System.String"));
    tbl.Columns.Add("Address", System.Type.GetType("System.String"));
    tbl.Columns.Add("Salary", System.Type.GetType("System.Decimal"));
    tbl.Columns.Add("JoinedDate", System.Type.GetType("System.DateTime"));
    tbl.Columns.Add("Children", System.Type.GetType("System.Byte"));

    foreach(EmpType obj in list)
    {
        DataRow newRow = tbl.NewRow();
        if(obj.Name==null)
            newRow["Name"] = DBNull.Value;
        else
            newRow["Name"] = obj.Name;

        if(obj.Title==null)
            newRow["Title"] = DBNull.Value;
        else
            newRow["Title"] = obj.Title;

        if(obj.Address==null)
            newRow["Address"] = DBNull.Value;
        else
            newRow["Address"] = obj.Address;

        newRow["Salary"] = obj.Salary;

        newRow["JoinedDate"] = obj.JoinedDate;

        if(obj.Children==null)
            newRow["Children"] = DBNull.Value;
        else
            newRow["Children"] = obj.Children;

        tbl.Rows.Add(newRow);
    }
    return tbl;
}

public void InsertManyEmployees(
    ref int RowsInserted,
    List<EmpType> Employees)
{
    SqlConnection connection = new SqlConnection(ConnectionStr);
    try
    {
        connection.Open();

        SqlCommand command = new SqlCommand("[dbo].[sp_InsertManyEmp]", connection);
        command.CommandType = CommandType.StoredProcedure;

        SqlParameter parameter = null;
        parameter = new SqlParameter("@RowsInserted", SqlDbType.Int);
        parameter.Direction = System.Data.ParameterDirection.Output;
        parameter.Value = RowsInserted;
        command.Parameters.Add(parameter);

        parameter = new SqlParameter("@Employees", SqlDbType.Structured);
        parameter.Value = GetDataTable(Employees);
        command.Parameters.Add(parameter);

        command.ExecuteNonQuery();

        RowsInserted = Convert.ToInt32(command.Parameters["@RowsInserted"].Value);

    }
    catch (Exception exp)
    {
        throw exp;
    }
    finally
    {
        connection.Close();
    }
    return;
}

Stored Procedure Caller Generator

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 the stored procedure name and the parameters. Each parameter's 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. The 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 that 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 show the first three 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 = "long";
    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);
}

The 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 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;
}

Stored Procedure Caller Generator

This is GenDataSetProcCode implementation. Note: I do not show the GenIntProcCode and GenVoidProcCode here because they are similar in nature with only minor differences. 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();
}

That's all that this simple library does.

Conclusion

In this article, we use the SPCG application to auto-generate three 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!



About the Author

Wong Shao Voon

I guess I'll write here what I does in my free time, than to write an accolade of skills which I currently possess. I believe the things I does in my free time, say more about me.

When I am not working, I like to watch Japanese anime. I am also writing some movie script, hoping to see my own movie on the big screen one day.

I like to jog because it makes me feel good, having done something meaningful in the morning before the day starts.

I also writes articles for CodeGuru; I have a few ideas to write about but never get around writing because of hectic schedule.

Related Articles

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • It's hardly surprising that half of small businesses fail within the first 1-5 years. It's not easy to launch a new product, single-handedly manage everything from IT to accounting, fend off the competition, and grow a customer base – all at the same time – even with a great concept. Offering awesome customer service can make the difference between a startup that flies and a startup that dies. Read this white paper to learn nine ways customer support can help you beat the competition and grow your …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds