Generic Code to Execute Stored Procedures

You can execute a single stored procedure or batch of stored procedures using collections. You can execute any stored procedure with a different number of parameters and data types. Whenever there is any change in stored procedure, you just need to add or delete parameters in the calling code.

Code snippet:

namespace StoredProcExecution
{
      using System;
      using System.Collections;
      using System.Data;
      using System.Data.SqlClient;
      public struct ParamData
      {
            public string pName,pValue;
            public SqlDbType pDataType;
            public ParamData(string pName,SqlDbType pDataType,string pValue)
            {
                  this.pName=pName;
                  this.pDataType=pDataType;
                  this.pValue=pValue;
            }
      }           
      public class StoredProcedure
      {
            private string sProcName;
            private ArrayList sParams=new ArrayList();
			public  void SetParam(string pName,SqlDbType pDataType,string pValue)
            {              
                 ParamData pData=new  ParamData(pName,pDataType,pValue);
                 sParams.Add(pData);
            }
            
            public ArrayList GetParams()
            {
                  if (!(sParams==null))
                  {
                        return sParams;
                  }
                  else
                  {
                        return null;
                  }
            }
            public string ProcName
            {
                  get 
                  { 
                        return sProcName; 
                  }
                  set 
                  {
                        sProcName = value; 
                  }
            }
      }
	  
      public class StoredProcedureCollection:System.Collections.CollectionBase
      {
            public void add(StoredProcedure value)
            {
                  List.Add(value);
            }
            public void Remove(int index)
            {
                  if (index > Count - 1 || index < 0)
                  {
                        //ignore
                        Console.WriteLine("No data to remove");
                  }
                  else
                  {
                        List.RemoveAt(index); 
                  }
            }
            
            public StoredProcedure Item(int Index)
            {
                  return (StoredProcedure) List[Index];
            }
      }
}

Setting Data in Collection:

You can set up the data as given in following example.

StoredProcedureCollection spCollection=new StoredProcedureCollection();
StoredProcedure spData=new StoredProcedure();
spData.ProcName="TestMe";
spData.SetParam("@CountryCode",SqlDbType.Int,1);
spData.SetParam("@City",SqlDbType.VarChar,Hyderabad);
spCollection.add(spProcedure);

Similarly you can add n number of stored procedures to this collection.

Execution part:

You need to parse collection of stored procedures and parameter collection and execute the stored procedure.

 
public static bool ExecuteSps( StoredProcedureCollection spCollection,
                               SqlConnection Connection )
{
      try
      {
            foreach(StoredProcedure spData in spCollection)
            {
                  SqlCommand cmd=new SqlCommand();
                  int i=0;
                  if (Connection.State!=  ConnectionState.Open)
                     Connection.Open();
                  cmd.Connection=Connection;
                  cmd.CommandType=CommandType.StoredProcedure;
                  cmd.CommandText=spData.ProcName;
                  IEnumerator myEnumerator = spData.GetParams().GetEnumerator();
                  while (myEnumerator.MoveNext())
                  {
                      ParamData Data=(ParamData)myEnumerator.Current;
                      cmd.Parameters.Add(pData.pName,pData.pDataType);
                      cmd.Parameters[i].Value=pData.pValue;
                      i=i+1;
                  }
                  cmd.ExecuteNonQuery();
            }
            return true;
                    
      }
      catch(Exception exc)
      {
            return false;
      }
}


Comments

  • Need More

    Posted by sridher on 12/20/2007 03:36am

    i need information about presentation,Business,Data Layer and how its work and coding examples for this.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Lenovo recommends Windows 8 Pro. "I dropped my laptop getting out of the taxi." This probably sounds familiar to most IT professionals. If your employees are traveling, you know their devices are in for a rough go. Whether it's a trip to the conference room or a convention out of town, any time equipment leaves a user's desk it is at risk of being put into harm's way. Stay connected at all times, whether at the office or on the go, with agile, durable, and flexible devices like the Lenovo® …

  • By providing developers with the right tools to detect, understand, and fix problems early, your business can simplify software development, shorten development lifecycles, and improve the quality of software code. The end result is increased innovation, secure applications, and a faster time to market — all at a lower cost.

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date