Access | Data in writing data access layer code, always have to write on one side of the writing: read the database connection string, establish the database connection object, open the connection, create a Command object, create a data adapter, create a dataset, fill the dataset, close the connection. This kind of duplicate code writes one or two times to be OK, writes many to be unavoidable to have some annoyance.
After summarizing the previous code, decide to refactor the code for the data method layer. The data access layer does nothing more than two things: a query returns a DataTable, inserts, updates, deletes, and so on, without return values. As long as adding a data access layer base class contains these cumbersome code, the rest of the data Access layer code inherits the data access layer base class, giving the stored procedure name and stored procedure parameters when the base class function is invoked.
Data access layer base class code:
Using System;
Using System.Data;
Using System.Collections;
Using System.Data.SqlClient;
Namespace DAL
{
<summary>
Summary description of the dalbase.
The data layer accesses the base class, defines the data-tier access to the public variables, and methods
</summary>
public class Dalbase
{
Define the class shared variables
Private SqlConnection Conn; //
Private SqlCommand mycm; //
Private DataSet myds; //
Private SqlDataAdapter Myda; //
<summary>
Reading the database connection string from Web.config
</summary>
private string connstr = system.configuration.configurationsettings.appsettings["ConnectionString"];
Public Dalbase ()
{
constructor, creating an object instance
conn = new SqlConnection (CONNSTR);
MYCM = conn. CreateCommand ();
myDS = new DataSet ();
Myda = new SqlDataAdapter ();
}
<summary>
Returning information from a query table through a stored procedure
</summary>
<param name= "sprocname" > Stored procedure name </param>
<returns>DataTable</returns>
Protected DataTable gettable (string sprocname)
{
Conn. Open ();
Try
{
Mycm.commandtext = sProcName;
Mycm.commandtype = CommandType.StoredProcedure;
Myda. SelectCommand = mycm;
Myda. Fill (myds);
}
Finally
{
Closes the connection release resource, whether the statement is executing correctly or not
Conn. Close ();
}
Return myds. Tables[0];
}
<summary>
Returns information about a query table through stored procedures and parameters
</summary>
<param name= "sProcName" ></param>
<param name= "Parameters" ></param>
<returns></returns>
Protected DataTable gettable (String sprocname, sqlparameter[] parameters)
{
Conn. Open ();
Try
{
Mycm.commandtext = sProcName;
Mycm.commandtype = CommandType.StoredProcedure;
SqlParameterCollection sqlparams = mycm. Parameters;
Empty the original argument first.
mycm. Parameters.clear ();
Add parameters to command
foreach (SqlParameter parameter in parameters)
{
mycm. Parameters.Add (parameter);
}
Myda. SelectCommand = mycm;
Myda. Fill (myds);
}
Finally
{
Closes the connection release resource, whether the statement is executing correctly or not
Conn. Close ();
}
Return myds. Tables[0];
}
<summary>
Perform operations on the database with no return value through stored procedures and stored procedure parameters (e.g. Add, update, delete, etc.)
</summary>
<param name= "sprocname" > Stored procedure name </param>
<param name= "Parameters" > Stored procedure parameters </param>
protected void Savetale (String sprocname, sqlparameter[] parameters)
{
Mycm.commandtext = sProcName;
Mycm.commandtype = CommandType.StoredProcedure;
SqlParameterCollection sqlparams = mycm. Parameters;
Empty the original argument first.
mycm. Parameters.clear ();
Add parameters to command
foreach (SqlParameter parameter in parameters)
{
mycm. Parameters.Add (parameter);
}
Open connection
Conn. Open ();
Try
{
Perform
mycm. ExecuteNonQuery ();
}
Finally
{
Close connection
Conn. Close ();
}
}
}
}
Data Access Layer Code:
Using System;
Using System.Data;
Using System.Collections;
Using System.Data.SqlClient;
Namespace DAL
{
public class Test:dalbase
{
Public Test ()
{
}
Public DataTable gettesttable ()
{
Return base. GetTable ("Stored procedure name");
}
Public DataTable Gettesttablebyxname (string xname)
{
sqlparameter[] Parameters = {New SqlParameter ("@XName", sqldbtype.nvarchar,10)};
Return base. GetTable ("Stored procedure name", parameters);
}
public void Addtesttable (string xname, String Description)
{
sqlparameter[] Parameters =
{
New SqlParameter ("@XName", sqldbtype.nvarchar,10),
New SqlParameter ("@Description", sqldbtype.nvarchar,100)
};
Set parameter values
Parameters[0]. Value = XName;
PARAMETERS[1]. Value = Description;
Base. Savetale ("Stored procedure name", parameters);
}
}
}
You can also help expand this base class, such as increasing the return of a DataTable through a SQL statement, and returning a single value result (e.g., query totals) ...
