Write a base class for the data access layer

xiaoxiao2021-03-17 200

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

{/ ** ////

/// Dalbase's summary description.

/// 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; // / ** ////

/// read database connection string from Web.config /// prince string connStr = system.configuration.configurationSettings .Appsettings ["connectionstring"]; public dalbase () {// constructor, create an object instance conn = new sqlconnection (connStr); mycm = conn.createCommand (); myds = new dataset (); myda = new sqldataadapter () } / ** ////

/// Returns the query table by stored procedure /// /// Stored Procedure Name // / DataTable protected diatable gettable (string sprocname) {conn.open (); try { Mycm.commandtext = sprocname; mycm.commandtype = commandType.StoredProcedure; myda.selectcommand = mycm; myda.fill (myds);} finally {// Whether the connection release resource conn.close () is closed regardless of the statement. } Return myds.tables [0];} / ** ////

/// Return the query table by stored procedures and parameters /// /// /// /// <

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];} / ** ////

/// through the stored procedure And store procedure parameters to perform operations for non-return values for databases (eg, new, update, delete, etc.) /// /// stored procedure name /// stored procedure parameter protected void SaveTale (string sprocName, SqlParameter [] parameters) {mycm.CommandText = sprocName; mycm.CommandType = CommandType.StoredProcedure; SqlParameterCollection sqlParams = MYCM.Parameters; // Clear the original parameters mycm.parameters.clear (); // Add parameters to Command for Foreach (SQLParameter Parameter In Parameters) {MyCM.Parameters.Add (Parameter);} // Open the connection CONN. Open ();

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

转载请注明原文地址:https://www.9cbs.com/read-129649.html

9cbs

New Post(0)