Simple Stored Procedures in .NET

• Download source files - 965 B

Introduction

I was working on a simple CMS (Content Management System) project where I was faced with a tiring and exhausting procedure of defining each of the variables that I used in my SQL Stored Procedures. It was a tedious procedure specially when there were a lot of them and sometimes or shall I say most of the time, it was not easy to remember their data types. So I decided to develop a simpler way of communicating with Stored Procedures without having to write a bunch of code to define the command type and the variables used.

Using the Code

The class itself is self describing. The SimpleStoredProcedure class contains three methods. I have followed the .NET naming convention for my methods since we are all familiar with them.

The SimpleStoredProcedure class with its constructor is as follows:

public class SimpleStoredProcedure 
{
private string myConnectionString;
private SqlConnection Connection;
private SqlCommand comm;
private SqlParameter samparam;

public SimpleStoredProcedure(string ConnectionString)
{
myConnectionString=ConnectionString;
Connection = new SqlConnection(myConnectionString);
comm = new SqlCommand();

samparam = new SqlParameter();
}

The first method is called ExecuteSPReader which functions in the same way as the general ExecuteReader method in .NET.

public DataSet ExecuteSPReader
    (string StoredProcedure,string tableName, params DictionaryEntry[] ParamName)
{
comm = new SqlCommand(StoredProcedure);
comm.CommandType = CommandType.StoredProcedure;

foreach (DictionaryEntry paramV in ParamName)
{
comm.Parameters.AddWithValue(paramV.Key.ToString(), paramV.Value);
}

SqlDataAdapter resultDA = new SqlDataAdapter();
resultDA.SelectCommand = comm;
resultDA.SelectCommand.Connection = Connection;

Connection.Open();
try{
DataSet resultDS = new DataSet();
resultDA.Fill(resultDS, tableName);
}
finally{
Connection.Close();
}
return resultDS;
}

This method accepts three parameters:

• The stored procedure to call on
• Name of the table to get data from
• A list of the stored procedure parameters as DictionaryEntry.

There are two other methods defined which do not need to be described. They are:

• ExecuteScalarSP
• ExecuteNonQuerySP

Usage

To use this solution, you need to import the SimpleStoredProcedure class and use its method that best fits your need.

Let's say that we have an SQL Stored Procedure defined as follows:

ALTER PROCEDURE dbo.varifyGroupPermission 
(
@groupNumber int
)
AS
SELECT permission FROM PERMISSION_MATRIX WHERE groupNumber=@groupNumber

We want to have a list of permissions when running the above SQL procedure and use it in our program.

First we create an object called SP for example. Then list the variables we have in our SQL procedure above, give the variables a value. As you can see, I have defined a DictionaryEntry group and I do not have to worry about the type of this variable. Call the ExecuteSPReader method with the appropriate parameters and have its results in a DataSet. Now we can use its data from the DataSet.

SimpleStoredProcedure SP = new SimpleStoredProcedure();

DictionaryEntry group;
group.Key = "@groupNumber";
group.Value = 2;
DataSet PermissionDS = SP.ExecuteSPReader
    ("varifyGroupPermission", "PERMISSION_MATRIX", group);
DataTableReader PermissionReader = PermissionDS.CreateDataReader();

while (PermissionReader.Read())
{
Permission = PermissionReader.GetString(0);
}

That's it. You're done!! To use the other methods in this class, follow the same idea as above. It is as simple as that.