Run any stored procedure using that C# code

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.