Data access layer (DAL) for executing personalized stored procedures (C # implementation)

This article explains how to execute a single or batch stored procedure from the application layer (mainly calling the stored procedure that does not return any result set, but also extending it to calling the stored procedure that has returned results set ).
Data access layer:
This layer includes the following members:

• Paramdata Structure
• Storedprocedure class
• Storedprocedurecollection class
• Execute class

Paramdata contains the name, value, and data type of stored procedure parameters.

1 struct paramdata
2 {
3 Public String pname, pvalue;
4 Public sqldbtype pdatatype;
5 Public paramdata (string pname, sqldbtype pdatatype, string pvalue)
6 {
7this. pname = pname;
8this. pdatatype = pdatatype;
9this. pvalue = pvalue;
10}
11}
12

Storedprocedure contains two methods: setparam and getparam, which are used to set and obtain the parameter list of the stored procedure respectively.

Public void setparam (string pname, sqldbtype pdatatype, string pvalue)
{
Paramdata pdata = new paramdata (pname, pdatatype, pvalue );
// Adding to array list sparams.
Sparams. Add (pdata );
}


Public arraylist getparams ()
{
If (! (Sparams = NULL ))
{
Return sparams;
}
Else
{
Return NULL;
}
}

The storedprocedurecollection class is a collection class that contains multipleStored Procedure, add, remove, and item methods.

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


Public storedprocedure item (INT index)
{
Return (storedprocedure) list [Index];
}

The execute class includesExecutespsStatic method, which mainly runs fromStoredprocedurecollection

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

For more implementation details, seeDataaccesslayer. Zip
Application Layer:
In the application layer, we add the dataaccesslayer. after the DLL is referenced, we can call the functions of the data layer as needed. one advantage of this is that we can add and delete any number of parameters as the storage process needs change.

1 private void button#click (Object sender, system. eventargs E)
2 {
3 sqlconnection connection = new sqlconnection ();
4 // change this connect string as per your environment
5 string connectstring = "Persist Security info = false; Integrated Security = sspi; database = db1; server = server2; Connect timeout = 60 ";
6connection. connectionstring = connectstring;
7if (connection. State! = Connectionstate. open)
8connection. open ();
9dataaccesslayer. storedprocedurecollection spcollection = new dataaccesslayer. storedprocedurecollection ();
10dataaccesslayer. storedprocedure spdata = new dataaccesslayer. storedprocedure ();
11spdata. procname = txtspname. text;
12spdata. setparam (txtparam1.text, sqldbtype. varchar, txtparamvalue1.text );
13spdata. setparam (txtparam2.text, sqldbtype. varchar, txtparamvalue2.text );
14spcollection. Add (spdata );
15if (dataaccesslayer. Execute. executesps (spcollection, connection ))
16messagebox. Show ("successfully executed ");
17}
18}
19 catch (exception exc)
20 {
21 return false;
22}
23}

For more implementation details, seeCallingapp.zip