ज्योतिष की सार्थकता

आपकी रचना बहुत अच्छी और ज्ञानवर्धक है

Use 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.


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 <>
{
//ignore
Console.WriteLine("No data to delete");
}
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;
}
}

www.vmotechnologies.com