SqlHelper数据库操作辅助类

SQLhelper
/// <summary>
  /// 自定义访问通用类
  /// </summary>
  public class SqlDbHelper
  {
  string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
  /// <summary>
  /// 构造函数
  /// </summary>
  public SqlDbHelper()
  {
  }
  /// <summary>
  /// ExecuteNonQuery操作,对数据库进行 增、删、改 操作((1)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <returns> </returns>
  public int ExecuteNonQuery(string sql)
  {
  return ExecuteNonQuery(sql, CommandType.Text, null);
  }
  /// <summary>
  /// ExecuteNonQuery操作,对数据库进行 增、删、改 操作(2)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <returns> </returns>
  public int ExecuteNonQuery(string sql, CommandType commandType)
  {
  return ExecuteNonQuery(sql, commandType, null);
  }
  /// <summary>
  /// ExecuteNonQuery操作,对数据库进行 增、删、改 操作(3)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <param name="parameters">参数数组 </param>
  /// <returns> </returns>
  public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
  {
  int count = 0;
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
  using (SqlCommand command = new SqlCommand(sql, connection))
  {
  command.CommandType = commandType;
  if (parameters != null)
  {
  foreach (SqlParameter parameter in parameters)
  {
  command.Parameters.Add(parameter);
  }
  }
  connection.Open();
  count = command.ExecuteNonQuery();
  }
  }
  return count;
  }
  /// <summary>
  /// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataSet类型结果(1)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <returns> </returns>
  public DataSet ExecuteDataSet(string sql)
  {
  return ExecuteDataSet(sql, CommandType.Text, null);
  }
  /// <summary>
  /// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataSet类型结果(2)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <returns> </returns>
  public DataSet ExecuteDataSet(string sql, CommandType commandType)
  {
  return ExecuteDataSet(sql, commandType, null);
  }
  /// <summary>
  /// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataSet类型结果(3)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <param name="parameters">参数数组 </param>
  /// <returns> </returns>
  public DataSet ExecuteDataSet(string sql, CommandType commandType, SqlParameter[] parameters)
  {
  DataSet ds = new DataSet();
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
  using (SqlCommand command = new SqlCommand(sql, connection))
  {
  command.CommandType = commandType;
  if (parameters != null)
  {
  foreach (SqlParameter parameter in parameters)
  {
  command.Parameters.Add(parameter);
  }
  }
  SqlDataAdapter adapter = new SqlDataAdapter(command);
  adapter.Fill(ds);
  }
  }
  return ds;
  }
  /// <summary>
  /// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataTable类型结果(1)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <returns> </returns>
  public DataTable ExecuteDataTable(string sql)
  {
  return ExecuteDataTable(sql, CommandType.Text, null);
  }
  /// <summary>
  /// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataTable类型结果(2)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <returns> </returns>
  public DataTable ExecuteDataTable(string sql, CommandType commandType)
  {
  return ExecuteDataTable(sql, commandType, null);
  }
  /// <summary>
  /// SqlDataAdapter的Fill方法执行一个查询,并返回一个DataTable类型结果(3)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <param name="parameters">参数数组 </param>
  /// <returns> </returns>
  public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
  {
  DataTable data = new DataTable();
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
  using (SqlCommand command = new SqlCommand(sql, connection))
  {
  command.CommandType = commandType;
  if (parameters != null)
  {
  foreach (SqlParameter parameter in parameters)
  {
  command.Parameters.Add(parameter);
  }
  }
  SqlDataAdapter adapter = new SqlDataAdapter(command);
  adapter.Fill(data);
  }
  }
  return data;
  }
  /// <summary>
  /// ExecuteReader执行一查询,返回一SqlDataReader对象实例(1)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <returns> </returns>
  public SqlDataReader ExecuteReader(string sql)
  {
  return ExecuteReader(sql, CommandType.Text, null);
  }
  /// <summary>
  /// ExecuteReader执行一查询,返回一SqlDataReader对象实例(2)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <returns> </returns>
  public SqlDataReader ExecuteReader(string sql, CommandType commandType)
  {
  return ExecuteReader(sql, commandType, null);
  }
  /// <summary>
  /// ExecuteReader执行一查询,返回一SqlDataReader对象实例(3)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <param name="parameters">参数数组 </param>
  /// <returns> </returns>
  public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
  {
  SqlConnection connection = new SqlConnection(connectionString);
  SqlCommand command = new SqlCommand(sql, connection);
  command.CommandType = commandType;
  if (parameters != null)
  {
  foreach (SqlParameter parameter in parameters)
  {
  command.Parameters.Add(parameter);
  }
  }
  connection.Open();
  return command.ExecuteReader(CommandBehavior.CloseConnection);
  }
  /// <summary>
  /// ExecuteScalar执行一查询,返回查询结果的第一行第一列(1)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <returns> </returns>
  public Object ExecuteScalar(string sql)
  {
  return ExecuteScalar(sql, CommandType.Text, null);
  }
  /// <summary>
  /// ExecuteScalar执行一查询,返回查询结果的第一行第一列(2)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <returns> </returns>
  public Object ExecuteScalar(string sql, CommandType commandType)
  {
  return ExecuteScalar(sql, commandType, null);
  }
  /// <summary>
  /// ExecuteScalar执行一查询,返回查询结果的第一行第一列(3)
  /// </summary>
  /// <param name="sql">要执行的SQL语句 </param>
  /// <param name="commandType">要执行的查询类型(存储过程、SQL文本) </param>
  /// <returns> </returns>
  public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
  {
  object result = null;
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
  using (SqlCommand command = new SqlCommand(sql, connection))
  {
  command.CommandType = commandType;
  if (parameters != null)
  {
  foreach (SqlParameter parameter in parameters)
  {
  command.Parameters.Add(parameter);
  }
  }
  connection.Open();
  result = command.ExecuteScalar();
  }
  }
  return result;
  }
  /// <summary>
  /// 返回当前连接的数据库中所有由用户创建的数据库
  /// </summary>
  /// <returns> </returns>
  public DataTable GetTables()
  {
  DataTable data = null;
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
  connection.Open();
  data = connection.GetSchema("Tables");
  }
  return data;
  }
  }
posted @ 2012-06-14 23:22  ``炯``  阅读(345)  评论(2编辑  收藏  举报