Data Access Layer to execute Stored Procedures

This article explains about executing a single stored procedure or batch of stored procedures from the application layer. As Data access layer completely decoupled from Application layer we just need to change the Application layer in case of any change in underlying database schema.

This article explains primarily about calling stored procedures which does not return any result set. This can be extended to handle those stored procedures also.

Data access layer

This data access layer contains the following key elements.

• ParamData Structure
• StoredProcedure class
• StoredProcedureCollection class
• Execute class

ParamData structure will contain parameter name, parameter value and data type.

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

Stored Procedure class which will have methods to SetParam and Getparam to set and get the parameter list. This will be added to array list which will contain ParamData structures.

public void SetParam(string pName,SqlDbType pDataType,string pValue)
{
ParamData pData=new ParamData(pName,pDataType,pValue);
// adding to array list sParams.
sParams.Add(pData);
}

Similarly to get the parameter list GetParams method was defined which will return array list of ParamData structures.

public ArrayList GetParams()
{
if (!(sParams==null))
{
return sParams;
}
else
{
return null;
}
}

StoredProcedureCollection class contains the collection of stored procedure classes. This class has ADD and Remove methods to add the StroredProcedure class and remove when it is not needed.

public void add(StoredProcedure value)
{
List.Add(value);
}
public void Remove(int index)
{
if (index > Count - 1 || index < 0)
{
Console.WriteLine("No data to remove");
}
else
{
List.RemoveAt(index);
}
}

This will also have ITEM method to get the StoredProcedure from the List.

public StoredProcedure Item(int Index)
{
return (StoredProcedure) List[Index];
}

Execute class has a static method ExecuteSps to execute the stored procedures from the collection.

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 pData=(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;
}
}

Please find the code snippet in <<DataAccessLayer.ZIP>> file.

Application Layer

In the application layer we have to add the reference of DataAccessLayer.dll. After adding this reference we can call the data access layer functions as per our need. The advantage here is we can dynamically add/remove the parameters as and whenever there is change stored procedure definition.

private void button1_Click(object sender, System.EventArgs e)
{
SqlConnection connection=new SqlConnection();
//change this connect string as per your environment
string connectString="Persist Security Info=False;Integrated Security=SSPI;database=DB1;server=Server2;Connect Timeout=60";
connection.ConnectionString=connectString;
if (connection.State!=ConnectionState.Open)
connection.Open();
DataAccessLayer.StoredProcedureCollection spCollection=new DataAccessLayer.StoredProcedureCollection();
DataAccessLayer.StoredProcedure spData=new DataAccessLayer.StoredProcedure();
spData.ProcName=txtSpName.Text;
spData.SetParam(txtParam1.Text,SqlDbType.VarChar,txtParamValue1.Text);
spData.SetParam(txtParam2.Text,SqlDbType.VarChar,txtParamValue2.Text);
spCollection.add(spData);
if (DataAccessLayer.Execute.ExecuteSps(spCollection,connection))
MessageBox.Show("Successfully executed");
}
}
catch(Exception exc)
{
return false;
}
}

Please find the code snippet for using data access layer from the <<CallingAPP.zip>> file.