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

Run any stored procedure using that C# code

By , 22 Apr 2009
Rate this:
Please Sign up or sign in to vote.
Download SP_v1.zip - 2.12 KB
Download and Read details latest (v2) code from Here

Introduction  

That code will help to developer to develop code to run Stored Procedure easily from .Net (if you built it .dll file). Any one can write just few lines code to use it. If any one get any bug then please inform me at sumanbiswas@aol.in. To know more about such programming please visit to my blog http://socketprogramming.blogspot.com.

Background

When I try to run stored procedure from C# then I see that to run a single stored procedure need to write few line of code also to pass parameter need to write more line of code. So i decide to write some code by using stored procedure can run very fast. And now my code can run a Stored procedure by invoke just one function, and per parameter just one line code.

Use this code to run stored procedure 

You can run any stored procedure by invoking two static methods. These are -

1.      spArgumentsCollection - this is a static method of SP class, used to pass arguments to stored procedure. It has four arguments, for example, SP.spArgumentsCollection(arLst, "@nextName", "Suman Biswas", "varchar"); here ‘SP’ is the class name, ‘arLst’ is an ArrayList, “@nextName” is arguments name in Stored Procedure, “Suman Biswas” is the value of argument and “varchar” is argument type (in version 2, this has updated to enum type).

2.      RunStoredProcedure- this is used to run stored procedure. If stored procedure has argument then need to set argument(s) in ArrayList then have to call that method. For e.g. SP.RunStoredProcedure(cnnStr, "UpdateName", arLst);  Here ‘cnnStr’ is connection string of BD, “UpdateName” is stored procedure name and arLst is ArrayList of arguments. By this array list arguments’ value passed to stored procedure. This has one more overload method, with extra argument DataSet, to use it’s DataTable to store retrieved value.

  How this code actually works  

            SP class has one subclass SPArgBuild  which helps to pass argument to Stored Procedure. When spArgumentsCollection  method called then it internally creates an object of SPArgBuild, and set passed value to new objects’ attribute then return that object, which holds as an ArrayList element (here in arLst), and return ArrayList. In this way ArrayList holds Stored Procedure’s all argument.

            Next when RunStoredProcedure called then data are retrieve from ArrayList and these set to command object’s parameter. Then send commend to execute Stored Procedure.

  An example by executing a sample Stored Procedure

            To run that code we will follow these steps –

(1)   Create a table

 CREATE TABLE client(
id int IDENTITY(1,1) NOT NULL,
Name varchar(64) NULL,
jdate datetime NULL
)  

(2)   Insert some rows in there

INSERT INTO client(Name,jdate)VALUES ('Taniya',getdate())
INSERT INTO client(Name,jdate)VALUES ('Jhuma',getdate())
INSERT INTO client(Name,jdate)VALUES ('Ganesh',getdate()) 

(3)   Create a stored procedure

create procedure UpdateName ( @nextName varchar(256), @id int )
as
update client set name=@nextName where id=@id 

(4)   Run stored procedure by my code.

 ArrayList arLst = new ArrayList();//Create an Array List            
//Set argument data for Stored Procedure 
SP.spArgumentsCollection(arLst, "@nextName", "Suman Biswas", "varchar");
SP.spArgumentsCollection(arLst, "@id", "2", "int");
//Now run stored procedure.
SP.RunStoredProcedure(cnnStr, "UpdateName", arLst); 

 Now code is completed try it now.  



						

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)

About the Author

SumanBiswas
Web Developer
India India
No Biography provided
Follow on   Google+

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberkishore doni2-Sep-12 20:23 
General[My vote of 1] EntLib PinmemberITMaiO23-Apr-09 3:29 
GeneralMy vote of 1 Pinmemberahmed zahmed2-Mar-09 12:01 
GeneralMy vote of 1 PinmemberDmitri Nesteruk2-Mar-09 5:02 
GeneralGenerics PinmemberBryH1-Mar-09 13:44 
GeneralMy vote of 2 PinmemberMember 162330418-Jan-09 17:19 
General[Message Removed] PinmemberMojtaba Vali21-May-08 20:26 

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
Web01 | 2.8.140415.2 | Last Updated 23 Apr 2009
Article Copyright 2008 by SumanBiswas
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid