Asp. NET common database access classes for SQL Server _ practical Tips

This article imitates the universal class that implements database access, the code is clear and practical, including all the common operations on the database.

  <summary>///Database Access Universal Class///</summary> public class SqlHelper {private String Connectionstrin
  G <summary>///Set Database access string///</summary> public string ConnectionString {set {ConnectionString = value; }///<summary>///constructor///</summary>///<param name= "connectionString" > Database access String </para
  M> public SqlHelper (string connectionString) {this.connectionstring = connectionString;
  ///<summary>///executes a query and returns query results///</summary>///<param name= "SQL" > SQL statements to execute </param> <param name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>///<returns> return query result set </ Returns> Public DataTable executedatatable (string Sql,commandtype commandtype) {return executedatatable (SQL, COM
  Mandtype, NULL); 
  ///<summary>///executes a query and returns the result set///</summary>///<param name= "SQL" > SQL text command to execute </param> <returns> returnResult set of query back </returns> public DataTable executedatatable (String sql) {return executedatatable sql, Commandtype.tex
  T, NULL);
  ///<summary>///executes a query and returns query results///</summary>///<param name= "SQL" > SQL statements to execute </param> <param name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>///<param name= "Parameters" > Transact-SQL statement or stored procedure parameter array </param>///<returns></returns> public DataTable executedatatable (string
  SQL, CommandType commandtype, sqlparameter[] parameters) {DataTable data = new DataTable ();//materialized DataTable for loading query result set
  using (SqlConnection con = new SqlConnection (connectionString)) {using (SqlCommand cmd = new SqlCommand (sql, con)) {cmd. CommandType = commandtype;//SET command commandtype for specified commandtype//If parameters are passed in at the same time, add these arguments if (parameters!= null) {for Each (sqlparameter parameter in parameters) {cmd.
  Parameters.Add (parameter); Is instantiated by a SqlCommand instance that contains query SQL SqlDataAdapter
  SqlDataAdapter adapter = new SqlDataAdapter (cmd); Adapter.
  Fill (data)//Filled DataTable}} return data; ///<summary>///Returns an instance of a SqlDataReader object///</summary>///<param name= "SQL" > SQL query command to execute </ param>///<returns></returns> Public SqlDataReader ExecuteReader (String sql) {return executereade
  R (SQL, CommandType.Text, NULL);  }///<summary>//////</summary>///<param name= "SQL" > SQL statements to execute </param>///<param Name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>///<returns></returns> public
  SqlDataReader ExecuteReader (String Sql,commandtype commandtype) {return ExecuteReader (SQL, CommandType, NULL);
  ///<summary>///Returns an instance of a SqlDataReader object///</summary>///<param name= "SQL" ></param> <param name= "CommandType" ></param>///<param name= "parameters" ></param>///<returns& Gt;</returns> Public SqlDataReader ExecuteReader (String sql, CommandType commandtype, sqlparameter[] parameters) {SqlConnect
  Ion con = new SqlConnection (connectionString);
  SqlCommand cmd = new SqlCommand (sql, con); if (parameters!= null) {foreach (SqlParameter parameter in parameters) {cmd.
  Parameters.Add (Parameters); } con.
  Open (); The CommandBehavior.CloseConnection parameter indicates that the connection object associated with the reader object is closed with return cmd when it closes.
  ExecuteReader (commandbehavior.closeconnection); ///<summary>///Executes a query that returns the first header column of the result set. Ignore other rows, other columns///</summary>///<param name= "sql" > SQL command to execute </param>///&LT;RETURNS&GT;&LT;/RETURNS&G
  T
  Public Object ExecuteScalar (String sql) {return executescalar (SQL, CommandType.Text, NULL); ///<summary>//////</summary>///<param name= "sql" ></param>///<param name= "Co Mmandtype "></param>///<returns></returns> public Object executescalar (String sql, CommandtyPE commandtype) {return executescalar (SQL, CommandType, NULL); ///<summary>//////</summary>///<param name= "sql" ></param>///<param name= "Co Mmandtype "> Parameter type </param>///<param name=" parameters "></param>///<returns></returns&
  Gt
  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>///Operations for additions and deletions to the database///</summary>///<param name= "sql" > SQL command to execute </param> <returns></returns> public int ExecuteNonQuery (String sql) {return executenonquery (SQL, COmmandtype.text, NULL); ///<summary>///Database operations///</summary>///<param name= "sql" > SQL command to operate the database </param&gt
  ;
  <param name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>///<returns></returns>
  public int ExecuteNonQuery (String sql, CommandType commandtype) {return ExecuteNonQuery (SQL, CommandType, NULL);
  ///<summary>///Operations for additions and deletions to the database///</summary>///<param name= "SQL" > SQL statements to execute </param> <param name= "CommandType" > type of query statement to execute, such as stored procedures or SQL text commands </param>///<param name= "Parameters" > An array of parameters for Transact-SQL statements or stored procedures </param>///<returns></returns> public int ExecuteNonQuery (String sql, Co
  Mmandtype 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>///Returns database created by all users in the currently connected database///</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 set up a common class that accesses the database, and when we do it with the database, we just need to instantiate the object and then call the appropriate method to do all the operations on the database, depending on our needs. This is the benefit of separating the database access layer from the business logic layer.
This writing code, can greatly reduce the complexity of our code, but also greatly reduced the complexity.

The above is the entire content of this article, I hope to help you learn.