Click here to Skip to main content
11,923,958 members (64,129 online)
Click here to Skip to main content
Add your own
alternative version


24 bookmarked

Simple Stored Procedures in .NET

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


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

DataSet resultDS = new DataSet();
resultDA.Fill(resultDS, tableName);
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


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
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.


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


About the Author

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.

You may also be interested in...

Comments and Discussions

GeneralUse Microsoft's SqlHelper or Enterprise Library instead Pin
Jeff Firestone7-Feb-06 8:35
memberJeff Firestone7-Feb-06 8:35 
GeneralRe: Use Microsoft's SqlHelper or Enterprise Library instead Pin
H.Riazi7-Feb-06 19:52
memberH.Riazi7-Feb-06 19:52 
GeneralRe: Use Microsoft's SqlHelper or Enterprise Library instead Pin
nsimeonov7-Feb-06 21:19
membernsimeonov7-Feb-06 21:19 
GeneralRe: Use Microsoft's SqlHelper or Enterprise Library instead Pin
Brian Leach8-Feb-06 7:01
memberBrian Leach8-Feb-06 7:01 
GeneralRe: Use Microsoft's SqlHelper or Enterprise Library instead Pin
computerguru9238225-Feb-06 11:50
membercomputerguru9238225-Feb-06 11:50 
GeneralClever Use of DictionaryEntry Pin
Brian Leach7-Feb-06 6:53
memberBrian Leach7-Feb-06 6:53 
GeneralRe: Clever Use of DictionaryEntry Pin
H.Riazi7-Feb-06 19:55
memberH.Riazi7-Feb-06 19:55 
GeneralRe: Clever Use of DictionaryEntry Pin
H.Riazi7-Feb-06 21:11
memberH.Riazi7-Feb-06 21:11 
GeneralQuite Impressive Pin
M_Rizwan7-Feb-06 1:27
memberM_Rizwan7-Feb-06 1:27 
GeneralRe: Quite Impressive Pin
H.Riazi7-Feb-06 19:56
memberH.Riazi7-Feb-06 19:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web03 | 2.8.151125.3 | Last Updated 8 Feb 2006
Article Copyright 2006 by HRiazi
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid