Introduction to ASP. NET data access layer

If you do not use the ASP. NET data access layer, there will be a lot of SqlConnection, SqlCommand, SqlDataReader, Open, Close ...... These classes and methods, and the large amount of code, leave you bored, and the code writing is actually all physical, no technical content.

ASP is required for each project. NET data access layer, I do not do anything, but I made the data access layer into an independent project, there is no big purpose, the data access layer, only for data access, does not contain any logic.

The following is my improved ASP. NET data access layer. I hope more people can make better suggestions. Thank you.

 
 

  
  
UsingSystem;
  
  
UsingSystem. Data;
  
  
UsingSystem. Data. SqlClient;
  
  
UsingSystem. Configuration;
  
  
UsingSystem. Web;
  
  
UsingSystem. Web. Security;
  
  
UsingSystem. Web. UI;
  
  
UsingSystem. Web. UI. WebControls;
  
  
UsingSystem. Web. UI. WebControls. WebParts;
  
  
UsingSystem. Web. UI. HtmlControls;
  
  
UsingSystem. Text;
  
  
///<Summary> 
  
  
/// Data access layer, database operation class
  
  
/// Summary> 
  
  
PublicclassDatabase
  
  
{
  
  
PublicstaticstringConnectionStrings=ConfigurationManager.
ConnectionStrings ["ConnectionStrings"]. ConnectionString;
  
  
///<Summary> 
  
  
/// Execute an SQL statement with SqlParameter. If the SQL statement is successful, true is returned. If the SQL statement fails, false is returned.
  
  
/// Summary> 
  
  
///<ParamnameParamname="SQL">SQL statement Param> 
  
  
///<ParamnameParamname="Param">SqlParameter [] parameter list Param> 
  
  
///<Returns> Returns> 
  
  
PublicstaticintExecuteNonQuery (stringsql, paramsSqlParameter [] Param)
  
  
// Execute an SQL statement with SqlParameter and return the number of affected rows
  
  
{
  
  
// HttpContext. Current. Response. Write (SQL +"<Br>");
  
  
Using (SqlConnectionconn=NewSqlConnection(ConnectionStrings ))
  
  
{
  
  
If (conn. State! = ConnectionState. Open)
  
  
Conn. Open ();
  
  
SqlTransactionTran=Conn. BeginTransaction (); // start the transaction
  
  
SqlCommandcmd=NewSqlCommand(SQL, conn, Tran );
  
  
If (Param! = Null)
  
  
Cmd. Parameters. AddRange (Param );
  
  
Try
  
  
{
  
  
IntResult=Cmd. ExecuteNonQuery ();
  
  
Tran. Commit ();
  
  
Cmd. Parameters. Clear ();
  
  
ReturnResult;
  
  
}
  
  
Catch (Exceptionerr)
  
  
{
  
  
HttpContext. Current. Response. Write (err. Message +"<Br>"+ Err. StackTrace );
  
  
Tran. Rollback (); // roll back the transaction
  
  
Return0;
  
  
}
  
  
}
  
  
}
  
  
 
  
  
///<Summary> 
  
  
/// Execute the SQL statement with SqlParameter and return the DataReader
  
  
/// Summary> 
  
  
///<ParamnameParamname="SQL">SQL statement Param> 
  
  
///<ParamnameParamname="Param">SqlParameter [] parameter list Param> 
  
  
///<Returns>Return SqlDataReader Returns> 
  
  
PublicstaticSqlDataReaderExecuteReader (stringsql, paramsSqlParameter [] Param)
  
  
// Execute the SQL statement with SqlParameter and return the DataReader
  
  
{
  
  
SqlConnectionconn=NewSqlConnection(ConnectionStrings );
  
  
SqlCommandcmd=NewSqlCommand(SQL, conn );
  
  
If (Param! = Null)
  
  
Cmd. Parameters. AddRange (Param );
  
  
If (conn. State! = ConnectionState. Open)
  
  
Conn. Open ();
  
  
 
  
  
Try
  
  
{
  
  
SqlDataReaderdr=Cmd. ExecuteReader (CommandBehavior. CloseConnection );
  
  
Cmd. Parameters. Clear ();
  
  
Returndr;
  
  
}
  
  
Catch (Exceptionerr)
  
  
{
  
  
Conn. Close ();
  
  
HttpContext. Current. Response. Write (err. Message +"<Br>"+ Err. StackTrace );
  
  
Returnnull;
  
  
}
  
  
}
  
  
 
  
  
///<Summary> 
  
  
/// Execute the SQL statement with SqlParameter and return the DataTable
  
  
/// Summary> 
  
  
///<ParamnameParamname="SQL">SQL statement Param> 
  
  
///<ParamnameParamname="Param">SqlParameter [] parameter list Param> 
  
  
PublicstaticDataTableGetDataTable (stringsql, paramsSqlParameter [] Param)
  
  
// Execute the SQL statement with SqlParameter and return the DataTable
  
  
{
  
  
SqlConnectionconn=NewSqlConnection(ConnectionStrings );
  
  
DataTabledt=NewDataTable();
  
  
SqlDataAdapterda=NewSqlDataAdapter(SQL, conn );
  
  
If (Param! = Null)
  
  
Da. SelectCommand. Parameters. AddRange (Param );
  
  
SqlCommandBuildercb=NewSqlCommandBuilder(Da );
  
  
Da. Fill (dt );
  
  
Conn. Close ();
  
  
// HttpContext. Current. Response. Write (SQL +"<Br>");
  
  
Returndt;
  
  
}
  
  
 
  
  
///<Summary> 
  
  
/// Execute an SQL statement with SqlParameter and return the data in the first column of the first row of the query result.
  
  
/// Summary> 
  
  
///<ParamnameParamname="SQL">SQL statement Param> 
  
  
///<ParamnameParamname="Param">SqlParameter [] parameter list Param> 
  
  
///<Returns> Returns> 
  
  
PublicstaticObjectExecuteScalar (stringsql, paramsSqlParameter [] Param)
  
  
// Execute an SQL statement with SqlParameter and return the data in the first column of the first row of the query result.
  
  
{
  
  
// HttpContext. Current. Response. Write (SQL +"<Br>");
  
  
Using (SqlConnectionconn=NewSqlConnection(ConnectionStrings ))
  
  
{
  
  
If (conn. State! = ConnectionState. Open)
  
  
Conn. Open ();
  
  
SqlTransactionTran=Conn. BeginTransaction ();
  
  
SqlCommandcmd=NewSqlCommand(SQL, conn, Tran );
  
  
If (Param! = Null)
  
  
Cmd. Parameters. AddRange (Param );
  
  
Try
  
  
{
  
  
ObjectResult=Cmd. ExecuteScalar ();
  
  
Cmd. Parameters. Clear ();
  
  
Tran. Commit ();
  
  
ReturnResult;
  
  
}
  
  
Catch (Exceptionerr)
  
  
{
  
  
HttpContext. Current. Response. Write (err. Message +"<Br>"+ Err. StackTrace );
  
  
Tran. Rollback ();
  
  
Returnnull;
  
  
}
  
  
}
  
  
}
  
  
 
  
  
///<Summary> 
  
  
/// Generate the SqlParameter Parameter
  
  
/// Summary> 
  
  
///<ParamnameParamname="ParamName">Parameter Name Param> 
  
  
///<ParamnameParamname="ParamType">Parameter type Param> 
  
  
///<ParamnameParamname="ParamValue">Parameter Value Param> 
  
  
PublicstaticSqlParameterGetParameter
(StringParamName, SqlDbTypeparamType, ObjectParamValue)
  
  
// Generate the SqlParameter Parameter
  
  
{
  
  
SqlParameterparam=NewSqlParameter(ParamName, paramType );
  
  
Param. Value=ParamValue;
  
  
Returnparam;
  
  
}
  
  
}
 
 

1. C # Introduction to local types
2. C # fixed pointer
3. Analysis of C # FTP WebRequest objects
4. C # application scenarios of the Division Method
5. Brief Introduction to VB. NET and C #