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

  • Learn How A Global Entertainment Company Saw a 448% ROI Every business today uses software to manage systems, deliver products, and empower employees to do their jobs. But software inevitably breaks, and when it does, businesses lose money -- in the form of dissatisfied customers, missed SLAs or lost productivity. PagerDuty, an operations performance platform, solves this problem by helping operations engineers and developers more effectively manage and resolve incidents across a company's global operations. …

  • Live Event Date: December 18, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this upcoming webcast …

Most Popular Programming Stories

More for Developers

RSS Feeds