Click here to Skip to main content
Click here to Skip to main content

Simple Stored Procedures in .NET

, 7 Feb 2006
Rate this:
Please Sign up or sign in to vote.
Use stored procedures in your code - as simple as 1,2,3

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

HRiazi
Engineer
Iran (Islamic Republic Of) Iran (Islamic Republic Of)
I was born in Shiraz (Iran). The city of popular poets and flowers.
Studied more than 8 years in Canada and was at UNB (University of New Brunswick) for a year.
Graduated from Shiraz University in field of Computer Eng.
MBA - Management at Khazar University
Love to play Soccer and write C# code.

Comments and Discussions

 
GeneralUse Microsoft's SqlHelper or Enterprise Library instead PinmemberJeff Firestone7-Feb-06 7:35 
GeneralRe: Use Microsoft's SqlHelper or Enterprise Library instead PinmemberH.Riazi7-Feb-06 18:52 
GeneralRe: Use Microsoft's SqlHelper or Enterprise Library instead Pinmembernsimeonov7-Feb-06 20:19 
GeneralRe: Use Microsoft's SqlHelper or Enterprise Library instead PinmemberBrian Leach8-Feb-06 6:01 
GeneralRe: Use Microsoft's SqlHelper or Enterprise Library instead Pinmembercomputerguru9238225-Feb-06 10:50 
GeneralClever Use of DictionaryEntry PinmemberBrian Leach7-Feb-06 5:53 
GeneralRe: Clever Use of DictionaryEntry PinmemberH.Riazi7-Feb-06 18:55 
GeneralRe: Clever Use of DictionaryEntry PinmemberH.Riazi7-Feb-06 20:11 
GeneralQuite Impressive PinmemberM_Rizwan7-Feb-06 0:27 
GeneralRe: Quite Impressive PinmemberH.Riazi7-Feb-06 18:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 8 Feb 2006
Article Copyright 2006 by HRiazi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid