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

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read