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

  • Live Event Date: May 11, 2015 @ 1:00 p.m. ET / 10:00 a.m. PT One of the languages that have always been supported with the Intel® RealSense™ SDK (Software Developer Kit) is JavaScript, specifically so that web-enabled apps could be created. Come hear from Intel Expert Bob Duffy as he reviews his own little "space shooting" game where the orientation of your face controls the aiming reticle to help teach developers how to write apps and games in JavaScript that can use facial and gesture …

  • Employees must exchange sensitive emails with customers and partners. These emails might contain protected health information, protected financial information, or corporate information that should not be made public. Globalscape® Mail Express® allows you to encrypt the emails that it manages so that no one but the sender and recipient--not even the administrator--can view the contents of the email. "Secure the Transfer of Sensitive Emails" is the property of GlobalSCAPE

Most Popular Programming Stories

More for Developers

RSS Feeds

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