Pages

Advertisement

Wednesday, July 11, 2007

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

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;
}
}

No comments: