Most recommended way to create web or windows database applications is based on "allocation" of the data access layer (DAL) in a separate layer. There are many ways to create this layer, of course, including the new feature of the .NET 2.0 - the ObjectDataSource. In this article I will describe one of the ways to build data access layer component, using such feature of the VS 2005 as Generic.
In the project , named DA (this is "DataAccess") and having type of the "Class Library", we create the class "DataAccess". This class recieves SQL connection as string and has the methods, which always returns some value (some messages as string or some DataSets).
In the case we "use" the method for delete, insert, update operations (this is "Non Query"), the method returns string. If the process is completed successfully, the returned string looks like that: "OK;6 rows affected". Now with the Substring method you can retrieve a substring from this instance and to process it according your needed logic. If the process is completed non successfully, the returned string is just the error message. In the case we use stored procedure (as CommandType for the SqlConnection object, the method has at least two parameters: name of the stored procedure as string and SqlParameter as List< SqlParameter> (here Generic come in). The example of the code for the method is the following :
public string execNonQuery(List<SqlParameter> ListSqlParams, string NameSP)
{
SqlCommand sqlCommand = new SqlCommand();
SqlParameter sqlParameter;
SqlConnection sqlConnection = new SqlConnection();
int iListCount = ListSqlParams.Count;
int iCount;
string sMessageOut = "OK";
int iRowsAffected = 0;
if (iListCount > 0)
{
for (iCount = 0; iCount < iListCount; iCount++)
{
sqlParameter = new SqlParameter();
sqlParameter = ListSqlParams[iCount];
sqlCommand.Parameters.Add(sqlParameter);
}
try
{
sqlConnection.ConnectionString = s_SQLConnection;
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
sqlCommand.CommandText = NameSP;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlConnection;
iRowsAffected = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
sMessageOut += ";" + iRowsAffected.ToString() + " rows affected.";
}
catch (Exception E)
{
sMessageOut = E.Message;
}
finally
{
if (sqlConnection.State != ConnectionState.Closed)
{
sqlConnection.Close();
}
}
}
return (sMessageOut);
}
Here s_SQLConnection is the string, that we "set" in the constructor:
namespace DA
{
public class DataAccess
{
string s_SQLConnection;
public DataAccess(string SQLConnection)
{
s_SQLConnection = SQLConnection;
}
//code of the class
................................
................................
Now , let's pass to the case with some "select" query. In this case our method returns DataSet, but also has out parameter, which informs us on success of inquiry: "OK" or just error message. As we have decided, that all our methods always returns the value, we will use special method, that returns DataSet in "non successful" processes:
private DataSet getDataSetMessage(string sMessage)
{
DataTable dt = new DataTable("DataTableMessage");
DataColumn dCol;
DataRow dRow;
DataSet ds = new DataSet();
ds.Clear();
dCol = new DataColumn("ErrorMessage", Type.GetType("System.String"));
dt.Columns.Add(dCol);
dRow = dt.NewRow();
dRow["ErrorMessage"] = sMessage;
dt.Rows.Add(dRow);
ds.Tables.Add(dt);
return (ds);
}
One of the methods, that returns DataSet, is the following:
public DataSet getDataSetSP(List<SqlParameter> ListSqlParams, string NameSP,out string sMessageOut)
{
SqlDataAdapter sqlDataAdapter;
SqlCommand sqlCommand = new SqlCommand();
SqlParameter sqlParameter;
DataSet ds = new DataSet();
SqlConnection sqlConnection = new SqlConnection();
int iListCount = ListSqlParams.Count;
int iCount;
sMessageOut = "OK";
if (iListCount > 0)
{
for (iCount = 0; iCount < iListCount; iCount++)
{
sqlParameter = new SqlParameter();
sqlParameter = ListSqlParams[iCount];
sqlCommand.Parameters.Add(sqlParameter);
}
try
{
sqlConnection.ConnectionString = s_SQLConnection;
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}
sqlCommand.CommandText = NameSP;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlConnection;
sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(ds);
sqlConnection.Close();
}
catch (Exception E)
{
sMessageOut = E.Message;
ds = getDataSetMessage(E.Message);
}
finally
{
if (sqlConnection.State != ConnectionState.Closed)
{
sqlConnection.Close();
}
}
}
return (ds);
}
After creating all methods, that we need (as for me, I have in the class six methods), we can use our DA.dll in every project what we want.
Let's suppose, that we have the table " TABLE_T " with two columns "Num" and "Descr". For "insert" and "select" we use two stored procedures: usp_TABLE_T_Insert (with the parameters: "@Num" and "@Descr") and usp_TABLE_T_Select (with the parameter "@Descr"). The class, named "BL_T" , we place into folder "BL"(business logic), which in its turn is located in the App_Code folder. Of course, we have to have reference to the DA.dll. To the class "BL_T" we add two methods, that "get" answer from the DataBase (as DataSet, some message, etc.) according to received data and SQLConnection :
public class BL_T
{
#region "forClass"
DA.DataAccess data_Acc;
#endregion
public BL_T()
{
//
// TODO: Add constructor logic here
//
data_Acc = new DA.DataAccess("Data Source=;Initial Catalog=;Persist Security
Info=;User ID=;Password=");
}
public string usp_TABLE_T_Insert(int Num, string Descr)
{
string sNameSP = "usp_TABLE_T_Insert";
List<SqlParameter> ListSqlParam = new List<SqlParameter>();
SqlParameter sqlParam1 = new SqlParameter("@Num",Num);
ListSqlParam.Add(sqlParam1);
SqlParameter sqlParam2 = new SqlParameter("@Descr",Descr);
ListSqlParam.Add(sqlParam2);
return (data_Acc.execNonQuery(ListSqlParam, sNameSP));
}
public DataSet usp_TABLE_T_Select(string Descr,
out string sMessageOut)
{
string sNameSP = "usp_TABLE_T_Select";
List<SqlParameter> ListSqlParam = new List<SqlParameter>();
SqlParameter sqlParam = new SqlParameter("@Descr", Descr);
ListSqlParam.Add(sqlParam);
return data_Acc.getDataSetSP(ListSqlParam ,sNameSP ,out sMessageOut);
}
}
Of course, you can follow your logic here: to add to the Descr parameter string "My name is ..." ; to the out sMessageOut string like "I am from usp_TABLE_T_Select now" , etc.
And now, on some page (for example, WF_DA_Test.aspx) , where there are such controls as ButtonInsert, ButtonSelect, GridViewSelect, LabelMessage, we can use the follow code (that allow to get DataSource for the GridView according to our parameter, Insert to Table_T values and get messages for all our interaction with the DataBase ):
public partial class WebForms_Test_WF_DA_Test : System.Web.UI.Page
{
#region "forClass"
BL_T bl_t = new BL_T();
#endregion
protected void ButtonInsert_Click(object sender, EventArgs e)
{
int Num = int.Parse(TextBoxNum.Text.Trim());
string Descr = TextBoxDescr.Text.Trim();
LabelMessage.Text = bl_t.usp_TABLE_T_Insert(Num, Descr);
}
protected void ButtonSelect_Click(object sender, EventArgs e)
{
string sMessage = "";
string Descr = TextBoxDescr.Text.Trim();
GridViewSelect.DataSource =
bl_t.usp_TABLE_T_Select(Descr, out sMessage).Tables[0];
GridViewSelect.DataBind();
LabelMessage.Text = sMessage;
}
}
Conclusion
I hope that this article will help you to create your own DA.dll component, that can be very useful in some cases of building web or windows database applications.
Good luck in programming !