When writing a data access layer code, you always have to write again and write: read the database connection string, create a database connection object, open the connection, create a Command object, create a data adapter, create a dataset, populate the data set, and turn it off. This duplicate code is written once or two, and there is no more annoying.
After summarizing the previous code, the code of reconstructing the data method layer is determined. Data Access Layers are nothing more than two operations: query returns to DataTable, insert, update, delete, etc. without return values. Just adding a data access layer base class contains these cumbersome code, the remaining data access layer code inherits the data access layer base class, gives the stored procedure name and stored procedure parameters when the base class function is called.
Data Access Layer Base Code:
Using
System;
Using
System.data;
Using
System.collections;
Using
System.data.sqlclient;
Namespace
Dal
{/ ** ////
/// Data layer Access the base class, define the data layer to access the public variable, method /// summary> public class dalbase {// Defines the class shared variable private sqlconnection conn; // private sqlcommand mycm; // private dataset MYDS; // private sqldataadapter myda; // / ** ////
returns> returns> protected DataTable GetTable (string sprocName, SqlParameter [] parameters) {conn.Open (); try {mycm.CommandText = sprocName; mycm.CommandType = CommandType.StoredProcedure; SqlParameterCollection sqlParams = mycm.Parameters; // First empty the original parameters mycm.parameters.clear (); // add parameters to Command for Foreach (SQLParameter Parameter in Parameters) {MyCm.Parameters.Add (Parameter);} myda.selectcommand = mycm; myda.fill (MyDS) } Finally {// Regardless of the statement to perform the correct or not, close the connection release resource conn.close ();} return myds.tables [0];} / ** ////
Try {// execute mycm.executenonury ();} 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 names");} public DataTable GetTestTableByXName (string XName) {SqlParameter [] parameters = {new SqlParameter ( " @XName ", SqlDbType.NVarChar, 10)}; return base.GetTable (" stored procedure names ", parameters);} public void AddTestTable (string XName, string Description) {SqlParameter [] parameters = {new SqlParameter (" @XName "Sqldbtype.nvarchar, 10), new sqlparameter (" @Description ", sqldbtype.nvarchar, 100)}; // set parameter value parameters [0] .Value = xname; parameters [1] .value = description; base. Savetale ("Stored Procedure Name", Parameters);}}}
Everyone is interested in helping to expand this base class, such as increasing to return to DataTable through the SQL statement, return a single value result (such as: query) ... Note: A bad place to use this method is for a long time, I will forget how to do it. The specific method of data access only calls the base class method, if the interview is asked to access the code ... ^ - ^.
Http://esshs.cnblogs.com/archive/2005/04/12/135883.html