[ASP. NET development] ASP. NET generic database category for SQLServer

As a programmer, we should learn new knowledge tomorrow.

Previously, I performed database operations on the same page. In this case, the operation is cumbersome and the Code for database operations needs to be repeatedly written. This not only wastes a lot of time, but also makes the Code look messy. To the three-tier architecture, I understand the benefits of layering and the benefits of object-oriented. Today, when I was reading a book, I saw Mr. Zhou Jinqiao's generic class for database access. I tried to write a similar one, and the code was clear and practical, includes all common operations on the database.

/// <Summary> /// Common Database Access class /// </summary> public class SqlHelper {private string connectionString; /// <summary> /// set the database access string /// </summary> public string ConnectionString {set {connectionString = value ;}} /// <summary> /// constructor /// </summary> /// <param name = "connectionString"> database access string </param> public SqlHelper (string connectionString) {this. connectionString = connectionString;} // <summa Ry> // execute a query, return the query result /// </summary> /// <param name = "SQL"> SQL statement to be executed </param> /// <param name = "commandType "> type of the query statement to be executed, for example, stored procedures or SQL text commands </param> // <returns> return the query result set </returns> public DataTable ExecuteDataTable (string SQL, CommandType commandType) {return ExecuteDataTable (SQL, commandType, null) ;}/// <summary> /// execute a query, and return the result set /// </summary> /// <param name = "SQL"> SQL text command to be executed </param> /// <returns> Returned query result set </returns> public DataTable ExecuteDataTable (string SQL) {return ExecuteDataTable (SQL, CommandType. text, null) ;}/// <summary> /// execute a query, return the query result /// </summary> /// <param name = "SQL"> SQL statement to be executed </param> /// <param name = "commandtype "> type of the query statement to be executed, for example, stored procedures or SQL text commands </param> /// <param name = "parameters"> Transact-SQL statements or stored procedure parameter arrays </param> /// <returns> </returns> public DataTable ExecuteDataTable (string SQL, CommandType commandtype, SqlParameter [] parameters) {DataTable data = new DataTable (); // instantiate the datatable, used to load the query result set using (SqlConnection con = new SqlConnection (connectionString )) {using (SqlCommand cmd = new SqlCommand (SQL, con) {cmd. commandType = commandtype; // set the commandType of command to the specified Commandtype. // if parameters are input at the same time, add these parameters if (parameters! = Null) {foreach (SqlParameter parameter in parameters) {cmd. parameters. add (parameter) ;}}// instantiate sqldataadapter SqlDataAdapter adapter = new SqlDataAdapter (cmd) by using the sqlcommand instance that contains the query SQL statement; adapter. fill (data); // Fill the datatable} return data ;} /// <summary> /// return the instance of a SqlDataReader object // </summary> /// <param name = "SQL"> SQL query command to be executed </param> // <returns> </returns> public SqlDataReader ExecuteReader (strin G SQL) {return ExecuteReader (SQL, CommandType. text, null );} /// <summary> ///// </summary> /// <param name = "SQL"> SQL statement to be executed </param> /// <param name = "commandType"> type of the query statement to be executed, for example, stored procedures or SQL text commands </param> /// <returns> </returns> public SqlDataReader ExecuteReader (string SQL, CommandType commandType) {return ExecuteReader (SQL, commandType, null) ;}/// <summary> /// return the instance of a sqldatareader object /// </summary> /// <Param name = "SQL"> </param> /// <param name = "commandType"> </param> /// <param name = "parameters"> </param> /// <returns> </returns> public SqlDataReader ExecuteReader (string SQL, commandType commandType, SqlParameter [] parameters) {SqlConnection con = new SqlConnection (connectionString); SqlCommand cmd = new SqlCommand (SQL, con); if (parameters! = Null) {foreach (SqlParameter parameter in parameters) {cmd. parameters. add (parameters) ;}} con. open (); // CommandBehavior. the CloseConnection parameter indicates to close the Connection object return cmd associated with the reader object when the reader object is closed. executeReader (CommandBehavior. closeConnection) ;}/// <summary> /// executes a query and returns the first row and column of the result set. Ignore other rows, other columns /// </summary> /// <param name = "SQL"> SQL command to be executed </param> /// <returns> </returns> public object ExecuteScalar (string SQL) {return ExecuteScalar (SQL, CommandType. text, null );} /// <summary> ///// </summary> /// <param name = "SQL"> </param> /// <param name = "commandType "> </param> /// <returns> </returns> public Object ExecuteScalar (string SQL, commandType commandType) {return ExecuteScalar (sq L, commandType, null );} /// <summary> ///// </summary> /// <param name = "SQL"> </param> /// <param name = "commandType "> parameter type </param> /// <param name =" parameters "> </param> /// <returns> </returns> public Object ExecuteScalar (string SQL, commandType commandType, SqlParameter [] parameters) {Object result = null; SqlConnection con = new SqlConnection (connectionString); SqlCommand cmd = new SqlCommand (SQL, con); Cmd. CommandType = commandType; if (parameters! = Null) {foreach (SqlParameter parapmeter in parameters) {cmd. parameters. add (parapmeter) ;}} con. open (); result = cmd. executeScalar (); con. close (); return result ;} /// <summary> /// add, delete, modify, and delete a database /// </summary> /// <param name = "SQL"> SQL command to be executed </param> // <returns> </returns> public int ExecuteNonQuery (string SQL) {return ExecuteNonQuery (SQL, CommandType. text, null) ;}/// <summary> // add, delete, and modify the database /// </Summary> /// <param name = "SQL"> SQL command for Database Operations </param> /// <param name = "commandType"> type of the query statement to be executed, for example, stored procedures or SQL text commands </param> // <returns> </returns> public int ExecuteNonQuery (string SQL, CommandType commandType) {return ExecuteNonQuery (SQL, commandType, null );} /// <summary> /// add, delete, modify, and delete a database /// </summary> /// <param name = "SQL"> SQL statement to be executed </param> // <param name = "commandType"> type of the query statement to be executed, such as stored procedures or sq L text command </param> /// <param name = "parameters"> parameter array of Transact-SQL statements or stored procedures </param> /// <returns> </ returns> public int ExecuteNonQuery (string SQL, commandType commandType, SqlParameter [] parameters) {int count = 0; SqlConnection con = new SqlConnection (connectionString); SqlCommand cmd = new SqlCommand (SQL, con); cmd. commandType = commandType; if (parameters! = Null) {foreach (SqlParameter parameter in parameters) {cmd. parameters. add (parameter) ;}} con. open (); count = cmd. executeNonQuery (); con. close (); return count ;} /// <summary> /// return the database created by all users in the database currently connected /// </summary> /// <returns> </returns> public DataTable GetTables () {DataTable table = null; using (SqlConnection con = new SqlConnection (connectionString) {con. open (); table = con. getSchema ("Tables") ;}return table ;}}

　　

If we create a general class for database access, when we operate with the database, we only need to instantiate the object first, and then according to our own needs, you can call the corresponding method to complete all operations on the database. This is the benefit of separating the database access layer from the business logic layer.

This code can greatly reduce the complexity of our code. In addition, the complexity is greatly reduced.