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

  • Download the Information Governance Survey Benchmark Report to gain insights that can help you further establish business value in your Records and Information Management (RIM) program and across your entire organization. Discover how your peers in the industry are dealing with this evolving information lifecycle management environment and uncover key insights such as: 87% of organizations surveyed have a RIM program in place 8% measure compliance 64% cannot get employees to "let go" of information for …

  • With JRebel, developers get to see their code changes immediately, fine-tune their code with incremental changes, debug, explore and deploy their code with ease (both locally and remotely), and ultimately spend more time coding instead of waiting for the dreaded application redeploy to finish. Every time a developer tests a code change it takes minutes to build and deploy the application. JRebel keeps the app server running at all times, so testing is instantaneous and interactive.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds