Stored Procedure Caller Generator

SPCG.png

Introduction
1st Example: Stored Procedure Returns No Value
2nd Example: Stored Procedure Returns Integer
3rd Example: Stored Procedure Returns Table
4th Example: Stored Procedure With Table-Value Parameter
How it works

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 spInsertEmp. spInsertEmp 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 spGetNum as our stored procedure. spGetNum 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 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;
}

4th Example: Stored Procedure With Table-Value Parameter

For the fourth example, we will pass a table of EmpType type to the stored procedure, spInsertManyEmp. This is the sql script will use. spInsertManyEmp 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, 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 inputted.

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

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 = "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);
}

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.

Last edited Aug 10, 2011 at 6:39 AM by shaovoon, version 5

Comments

No comments yet.