• Download StoredProcedureCaller05 - 36.95 KB

Call Your Stored Procedures With Ease

• Introduction
• Calling Stored Procedures
• Autogenerated Code
• Conclusion

Introduction

Most ADO.NET developers do not relish the mindless drudgery of writing plain ADO.NET code to call stored procedures. Writing the stored procedure often involves repetitive code but they often varies enough to warrant a rewrite for each new stored procedure which our application must call. This article presents a library called Stored Procedure Caller, to enable developers to call SQL Server stored procedures with mininal effort. 

Calling Stored Procedures

For purpose of this article and the next example, I'll use this table, Employee and the following stored procedure, sp_InsertEmp.

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]

CREATE PROCEDURE [dbo].[sp_InsertEmp](
    @ID int OUTPUT,
    @Name nvarchar(30),
    @Title varchar(20),
    @Address varchar(30),
    @Salary money,
    @JoinedDate datetime,
    @Children tinyint)
AS
    INSERT INTO [dbo].[Employee] ([Name], [Title], [Address], [Salary], [JoinedDate], [Children]) VALUES (@Name, @Title, @Address, @Salary, @JoinedDate, @Children);
    Select @ID = Scope_Identity();
GO

This is the typical plain ADO.NET code to call this stored procedure, sp_InsertEmp.

public static DataSet InsertEmployee(
    string connectionString,	
    ref int id,
    string name,
    string title,
    string address,
    decimal salary,
    DateTime joinedDate,
    byte? children)
{
    DataSet ds = new DataSet();
    SqlConnection connection = new SqlConnection(connectionString);
    try
    {
        connection.Open();

        SqlCommand command = new SqlCommand("sp_InsertEmp", connection);
        command.CommandType = CommandType.StoredProcedure;

        SqlParameter parameterOut = new SqlParameter("@ID", SqlDbType.Int);
        parameterOut.Direction = System.Data.ParameterDirection.Output;
        parameterOut.Value = id;
        command.Parameters.Add(parameterOut);

        SqlParameter 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.NVarChar, 20);
        if (title == null)
            parameter.Value = DBNull.Value;
        else
            parameter.Value = title;
        command.Parameters.Add(parameter);

        parameter = new SqlParameter("@Address", SqlDbType.NVarChar, 30);
        if (address == null)
            parameter.Value = DBNull.Value;
        else
            parameter.Value = address;
        command.Parameters.Add(parameter);

        parameter = new SqlParameter("@Salary", SqlDbType.Money);
        parameter.Value = salary;
        command.Parameters.Add(parameter);

        parameter = new SqlParameter("@JoinedDate", SqlDbType.DateTime);
        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);

        SqlDataAdapter adapter = new SqlDataAdapter(command);
        adapter.Fill(ds);

        ID = Convert.ToInt32(command.Parameters["@ID"].Value);
    }
    catch (Exception exp)
    {
        // handle exception here
    }
    finally
    {
        connection.Close();
    }
    return ds;
}

The above ADO.NET code takes about 75 lines of code. How many lines of code does Stored Procedure Caller take to call sp_InsertEmp? Let Stored Procedure Caller perform some voodoo magic for you!

static void InsertEmployee(
    ref int ID, 
    string Name, 
    string Title, 
    string Address, 
    decimal Salary, 
    DateTime JoinedDate, 
    byte? Children)
{
    try
    {
        string str = "CREATE PROCEDURE [dbo].[sp_InsertEmp](";
        str += "@ID int OUTPUT," + "@Name nvarchar(30),";
        str += "@Title varchar(20)," + "@Address varchar(30),";
        str += "@Salary money," + "@JoinedDate datetime,";
        str += "@Children tinyint)";

        // Parse the stored procedure signature
        SPSignature signature = new SPSignature(str);

        SPCaller caller = new SPCaller(signature);
        caller.ConnectionStr = ConnectionStr;

        Output outputID = new Output(ID);
        caller.CallVoidProc("[dbo].[sp_InsertEmp]",
            outputID, Name, Title, Address, Salary, JoinedDate, Children);

        ID = outputID.GetInt();
    }
    catch (System.Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

The Stored Procedure Caller takes 34 lines of code to do the same call, which is about half of the original code. We can see from the code: SPSignature takes in a string which contains the sp_InsertEmp signature in its constructor and parse it into parameters. SPCaller takes in SPSignature object in its constructor. For performance reason, you should keep SPSignature object around because it is expensive to parse the signature everytime you need to call your stored procedure. You can save SPSignature parsed information into a XML file and load that instead of parsing the signature string; Its Load and Save method allows you to do that. XML parsing and saving is done using the Elmax library. This is how the XML file format looks like.

I guess everyone, by now, is very curious about the SPCaller.CallVoidProc. I'll explain this right away. Below is the declaration of the CallVoidProc method and its sibling methods.

public void CallVoidProc(string spname, params object[] param);
public int CallIntProc(string spname, params object[] param);
public DataSet CallDataSetProc(string spname, params object[] param);

CallVoidProc is used for calling stored procedure which return nothing. Whereas CallIntProc returns integer from stored procedure and CallDataSetProc is for stored procedure that returns table(s).

You see that InsertEmployee method takes in ref integer parameter called ID. I wrapped this argument in a Output object before passing to CallVoidProc. That's because CallVoidProc takes in a variable number of Object objects for the stored procedure parameters; I am not allowed to pass in a ref argument to the CallVoidProc. As you might be wondering why I did not make Output class a generic class. The reason is because there is no way to cast the object back to a generic Output class inside CallVoidProc. If the stored procedure has the InputOutput parameter, then you should use a InputOutput class to wrap your variable. But right now, InputOutput and Output implementation has virtually no difference: Output is an empty class which inherits from InputOutput class.

For those readers who is interested in the CallVoidProc code, I list it below. However, I refrain from listing the CallIntProc and CallDataSetProc because they are very similar with some minor differences. For your information, these 3 methods compares the supplied stored procedure name with the signature name and also checks the .NET parameter types with the SqlType parameter types in the signature; They can only call stored procedures with parameters' SqlType which can be mapped to a corresponding .NET type.

public void CallVoidProc(string spname, params object[] param)
{
    if(string.IsNullOrEmpty(spname))
    {
        string msg = "spname is empty!";
        throw new InvalidDataException(msg);
    }
    if (Signature.Columns.Count != param.Length)
    {
        string msg = string.Format("Signature.Columns.Count({0}) is not equal to param.Length({1})",
            Signature.Columns.Count, param.Length);
        throw new InvalidDataException(msg);
    }
    if (Signature.Name.ToLower() != spname.ToLower())
    {
        string msg = "Signature.Name is not equal to spname";
        throw new InvalidDataException(msg);
    }

    SqlConnection connection = new SqlConnection(ConnectionStr);
    try
    {
        connection.Open();

        SqlCommand command = new SqlCommand(spname, connection);
        command.CommandType = CommandType.StoredProcedure;

        List<Column> listInputOutput = new List<Column>();
        for (int i = 0; i < Signature.Columns.Count; ++i)
        {
            object obj = param[i];
            Column col = Signature.Columns[i];
            if (col.direction == ParameterDirection.Input && obj!=null && obj.GetType() != col.netType)
            {
                string msg = string.Format("Type mismatch: Inputted type({0}) do not match with internal type({1})",
                    obj.GetType().ToString(), col.netType.ToString());
                throw new InvalidDataException(msg);
            }
            else if (col.direction == ParameterDirection.InputOutput)
            {
                InputOutput inputoutput = new InputOutput();
                if (obj.GetType() != inputoutput.GetType())
                {
                    string msg = string.Format("Type mismatch: Inputted type({0}) do not match with internal type({1})",
                        obj.GetType().ToString(), inputoutput.GetType().ToString());
                    throw new InvalidDataException(msg);
                }
                col.inputOuput = (InputOutput)(obj);
                listInputOutput.Add(col);
            }
            else if (col.direction == ParameterDirection.Output)
            {
                Output output = new Output();
                if (obj.GetType() != output.GetType())
                {
                    string msg = string.Format("Type mismatch: Inputted type({0}) do not match with internal type({1})",
                        obj.GetType().ToString(), output.GetType().ToString());
                    throw new InvalidDataException(msg);
                }
                col.inputOuput = (InputOutput)(obj);
                listInputOutput.Add(col);
            }

            SqlParameter parameter = null;
            if (col.length == 0)
                parameter = new SqlParameter(col.parameterName, col.sqlType);
            else
                parameter = new SqlParameter(col.parameterName, col.sqlType, (int)(col.length));

            if (obj == null)
                parameter.Value = DBNull.Value;
            else
            {
                if (col.direction == ParameterDirection.Input)
                    parameter.Value = obj;
                else
                {
                    parameter.Direction = col.direction;
                    parameter.Value = col.inputOuput.Value;
                }
            }
            command.Parameters.Add(parameter);
        }

        command.ExecuteNonQuery();

        // assign back all the InputOutput values
        foreach (Column col in listInputOutput)
        {
            col.inputOuput.Value = command.Parameters[col.parameterName].Value;
        }
    }
    catch (Exception exp)
    {
        throw exp;
    }
    finally
    {
        connection.Close();
    }
}

Auto-generated Code

For some readers who may be concerned about the overhead of Stored Procedure Caller and would like to use hand coded ADO.NET to call their stored procedures. I am happy to let you know Stored Procedure Caller lets you have your cake and eat it. Stored Procedure Caller can auto-generate ADO.NET code for you. However, it can only do that in runtime. These are the methods which allows us to auto-generate code.

public string GenVoidProcCode(string spname, params object[] param);
public string GenIntProcCode(string spname, params object[] param);
public string GenDataSetProcCode(string spname, params object[] param);

This is how I'll modify the original Stored Procedure Caller to generate the code.

public static void TestInsertEmployee()
{
    int ID = -1;
    string code = InsertEmployeeCode(
        ref ID,
        "Jack Nillis",
        "Acct Manager",
        "Lilac garden",
        5000.0m,
        DateTime.Now,
        3);

    SaveTextFile("InsertEmployee.cs", code);
}

static string InsertEmployeeCode(
    ref int ID,
    string Name,
    string Title,
    string Address,
    decimal Salary,
    DateTime JoinedDate,
    byte? Children)
{
    string code = null;
    try
    {
        string str = "CREATE PROCEDURE [dbo].[sp_InsertEmp](";
        str += "@ID int OUTPUT," + "@Name nvarchar(30),";
        str += "@Title varchar(20)," + "@Address varchar(30),";
        str += "@Salary money," + "@JoinedDate datetime,";
        str += "@Children tinyint)";

        // Parse the stored procedure signature
        SPSignature signature = new SPSignature(str);

        SPCaller caller = new SPCaller(signature);
        caller.ConnectionStr = ConnectionStr;

        Output outputID = new Output(ID);
        code = caller.GenVoidProcCode("[dbo].[sp_InsertEmp]",
            outputID, Name, Title, Address, Salary, JoinedDate, Children);

        ID = outputID.GetInt();
    }
    catch (System.Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    return code;
}

Here is the generated code.

    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);
        parameter.Value = Salary;
        command.Parameters.Add(parameter);

        parameter = new SqlParameter("@JoinedDate", SqlDbType.DateTime);
        parameter.Value = JoinedDate;
        command.Parameters.Add(parameter);

        parameter = new SqlParameter("@Children", SqlDbType.TinyInt);
        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;

You may need to edit your connection string variable name and your parameter names to match the generated code. You will notice the code generated for the Children nullable parameter is wrong. It should include a check for null.

parameter = new SqlParameter("@Children", SqlDbType.TinyInt);
if (Children == null)
    parameter.Value = DBNull.Value;
else
    parameter.Value = Children;
command.Parameters.Add(parameter);

The reason for this error. The Object object wraps integer, instead of the nullable integer. The code below prints "System.Int32" both for the integer and nullable integer type. In case the reader is not aware of, ? is a shorthand for declaring a nullable type.

static void Main(string[] args)
{
    int a = 10;
    int? b = 11;
    Nullable<int> c = 12;
    Test(a, b, c);
}

static void Test(params object[] objs)
{
    foreach (object obj in objs)
    {
        if (obj != null)
        {
            Type type = obj.GetType();
            Console.WriteLine("{0}", type.ToString());
        }
        else
        {
            Console.WriteLine("obj is null");
        }
    }
}

To fix the error, the developer just need to pass null for all nullable parameters and the code generated, will be correct.

public static void TestInsertEmployee()
{
    int ID = -1;
    string code = InsertEmployeeCode(
        ref ID,
        "Jack Nillis",
        "Acct Manager",
        "Lilac garden",
        5000.0m,
        DateTime.Now,
        null);

    SaveTextFile("InsertEmployee.cs", code);
}
// InsertEmployeeCode method is not shown because it remains unchanged.

Conclusion

Using Stored Procedure Caller, developer are freed with more time to surf facebook and do tweeting to focus on reviewing, refining and refactoring their code elsewhere to higher quality. Stored Procedure Caller is hosted at Codeplex. Feel free to report any problems, you encounter to Codeplex. Lastly, I would love to hear your feedback on things I am doing right and the things I am doing wrong.

Thank you for reading!