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

  • The proliferation of cloud computing options has begun to change the way storage is thought about, procured, and used. IT managers and departments need to think through how cloud options might fit into and complement their onsite data infrastructures. This white paper explains cloud storage and backup, providing advice about the tools and best practices for its implementation and use. Read this white paper for some useful takeaways about how to take advantage of cloud storage for high availability, backup and …

  • IT decision support impacts all aspects of technology management, from governance and strategy to budgets and resource planning. IT decision support effectiveness often falls prey to data-driven challenges that make it difficult to understand the data in context. These challenges: overwhelming data volumes, heterogeneous data types, and growing data complexity. This Forrester Consulting Paper reports the three key findings from their study conducted, on behalf of BDNA, to test the hypothesis that data …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds