When writing a data access layer code, you always want to write again and again: read the database connection string, create a database connection object, open the connection, create a Command object, create a data adapter, create a data set, 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, determine the code of the reconstructed data method layer. 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.data; using system.data.sqlclient;
A summary description of Namespace Dal {/// /// dalbase. /// Data layer Access the base class, define the data layer to access the public variable, method /// public class dalbase {// Defines the class shared variable private sqlconnection conn; // private sqlcommand mycm; // private dataset Myds; // private sqldataadapter myda; /// ////////////////////////////// private string connStr = system.configuration.configurationSettings.Appsettings [" Connectionstring "];
Public Dalbase () {// Constructor creates an object instance conn = new sqlconnection (connStr); mycm = conn.createCommand (); myds = new dataset (); myda = new sqldataadapter ();
/// /// The information returned by the stored procedure /// /// Stored Procedure Name /// DataTable < / returns> protected DataTable GetTable (string sprocName) {conn.Open (); try {mycm.CommandText = sprocName; mycm.CommandType = CommandType.StoredProcedure; myda.SelectCommand = mycm; myda.Fill (myds);} finally {/ / No matter whether the statement is correct or not, close the connection release resource conn.close ();} return myds.tables [0];
/// /// Returns the information of the query table by stored procedures and parameters /// /// /// /// protected DataTable GetTable (string sprocName, SqlParameter [] parameters) {conn.Open (); try {mycm.CommandText = sprocName; mycm.CommandType = CommandType.StoredProcedure SQLParameterCollection Sqlparams = mycm.parameters; // Clear the original parameter mycm.parameters.clear (); // add parameters to Command for the 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];} /// /// Execute an operation on the database without return value (such as new, updated, delete, etc.) /// /// stored procedure name /// Stored procedure parameters protected void savetale (string sprocname, sqlparameter [] parameters) {mycm.commandtext = sprocname; mycm.commandtype = CommandTyp e.StoredProcedure; SqlParameterCollection sqlParams = mycm.Parameters; // clear the original first parameter mycm.Parameters.Clear (); // add to the Command parameter foreach (SqlParameter parameter in parameters) {mycm.Parameters.Add (parameter); } // Open connection conn.open (); try {// execute mycm.executenon query ();} finally {// close connection conn.close ();}}}}
Data Access Layer Code:
Using system.data; 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 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 value parameters [0] .value = xname; parameters [1] .value = description; base.savetale ("Stored Procedure Name", Parameters);}}}
If you are interested, you can also help expand this base class, such as increasing to return to DataTable through the SQL statement, returning a single value result (such as: Query) ......
