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.
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)
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)
SqlDataAdapter resultDA = new SqlDataAdapter();
resultDA.SelectCommand = comm;
resultDA.SelectCommand.Connection = Connection;
DataSet resultDS = new DataSet();
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
There are two other methods defined which do not need to be described. They are:
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
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
SimpleStoredProcedure SP = new SimpleStoredProcedure();
group.Key = "@groupNumber";
group.Value = 2;
DataSet PermissionDS = SP.ExecuteSPReader
("varifyGroupPermission", "PERMISSION_MATRIX", group);
DataTableReader PermissionReader = PermissionDS.CreateDataReader();
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.