ASP. NET generic database access class for SQLServer

This article imitates the realization of database access to the general class, the code is clear, and very practical, including all the commonly used operations of the database.

  /// <summary>
  ///  Database access general 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;
  }
  /// <summary>
  ///  Execute 1 Queries and return query results 
  /// </summary>
  /// <param name="sql"> To be executed sql Statement </param>
  /// <param name="commandType"> The type of query statement to execute, such as a stored procedure or sql Text command </param>
  /// <returns> Returns the query result set </returns>
  public DataTable ExecuteDataTable(string sql,CommandType commandType)
  {
  return ExecuteDataTable(sql, commandType, null);
  }
  /// <summary>
  ///  Execute 1 Queries and return a result set 
  /// </summary>
  /// <param name="sql"> To be executed sql Text command </param>
  /// <returns> Returns the result set of a query </returns>
  public DataTable ExecuteDataTable(string sql)
  {
  return ExecuteDataTable(sql, CommandType.Text, null);
  }
  /// <summary>
  ///  Execute 1 Queries and return query results 
  /// </summary>
  /// <param name="sql"> To be executed sql Statement </param>
  /// <param name="commandtype"> The type of query statement to execute, such as a stored procedure or sql Text command </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(); // Instantiation datatable Which is used to load the query result set 
  using (SqlConnection con = new SqlConnection(connectionString))
  {
  using (SqlCommand cmd = new SqlCommand(sql, con))
  {
  cmd.CommandType = commandtype;// Settings command Adj. commandType For the specified Commandtype
  // If parameters are passed in at the same time, these parameters are added 
  if (parameters != null)
  {
  foreach (SqlParameter parameter in parameters)
  {
  cmd.Parameters.Add(parameter);
  }
  }
  // By including queries sql Adj. sqlcommand Instance to instantiate sqldataadapter
  SqlDataAdapter adapter = new SqlDataAdapter(cmd);
  adapter.Fill(data);// Padding datatable
  }
  }
  return data;
  }
  /// <summary>
  ///  Return 1 A SqlDataReader Object 
  /// </summary>
  /// <param name="sql"> To be executed SQl Query command </param>
  /// <returns></returns>
  public SqlDataReader ExecuteReader(string sql)
  {
  return ExecuteReader(sql, CommandType.Text, null);
  }
  /// <summary>
  ///
  /// </summary>
  /// <param name="sql"> To be executed sql Statement </param>
  /// <param name="commandType"> The type of query statement to execute, such as a stored procedure or SQl Text command </param>
  /// <returns></returns>
  public SqlDataReader ExecuteReader(string sql,CommandType commandType)
  {
  return ExecuteReader(sql, commandType, null);
  }
  /// <summary>
  ///  Return 1 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.CloseConnection Parameter indicates that the reader Object that is associated with it when the Connection Object 
  return cmd.ExecuteReader(CommandBehavior.CloseConnection);
  }
  /// <summary>
  ///  Execute 1 Queries that return the first row and column of the result set. Ignore other rows, other columns 
  /// </summary>
  /// <param name="sql"> To be executed SQl Command </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(sql, 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>
  ///  Adding, deleting and modifying the database 
  /// </summary>
  /// <param name="sql"> To be executed sql Command </param>
  /// <returns></returns>
  public int ExecuteNonQuery(string sql)
  {
  return ExecuteNonQuery(sql, CommandType.Text, null);
  }
  /// <summary>
  ///  Adding, deleting and modifying the database 
  /// </summary>
  /// <param name="sql"> Object that operates on the database sql Command </param>
  /// <param name="commandType"> The type of query statement to execute, such as a stored procedure or sql Text command </param>
  /// <returns></returns>
  public int ExecuteNonQuery(string sql, CommandType commandType)
  {
  return ExecuteNonQuery(sql, commandType, null);
  }
  /// <summary>
  ///  Adding, deleting and modifying the database 
  /// </summary>
  /// <param name="sql"> To be executed sql Statement </param>
  /// <param name="commandType"> The type of query statement to execute, such as a stored procedure or sql Text command </param>
  /// <param name="parameters">Transact-SQL Array of arguments for a statement or stored procedure </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>
  ///  Returns the 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 general class to access the database, when we operate with the database, we only need to instantiate the object first, and then call the corresponding methods according to our own needs to complete all the operations on the database. This is the advantage of separating the database access layer from the business logic layer.
The code written in this way can greatly reduce the complexity of our code, and the tedious degree is also greatly reduced.

The above is the whole content of this paper, hoping to help everyone's study.