Generic Code to Execute Stored Procedures

Share |

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 :::

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,14Hyderabad14);
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;
      }
}