这个 SqlDbHelper 是我参考网上的和以前用过的 SqlDbHelper 自己写的一个非常简单的东西，主要是记录自己的学习情况
首先在Web.config中配置数据库连接字符串:
然后新建DbHelper文件夹，创建SqlDbHelper类：
public class DbHelper { private string connectionString = ""; protected SqlConnection conn = null; #region //构造函数 public DbHelper() { //this.connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DBConnectionString"].ToString(); this.connectionString = ConfigurationManager.ConnectionStrings["TestConnectionStrings"].ConnectionString; this.conn = new SqlConnection(connectionString); } #endregion #region //打开连接 public void OpenDb() { if (conn.State != ConnectionState.Open) { try { conn.Open(); } catch (SqlException ex) { throw ex; } } } #endregion #region //关闭连接 public void CloseDb() { if (!object.Equals(conn, null) && (conn.State != ConnectionState.Closed)) { conn.Close(); } } #endregion #region //释放连接 public void Dispose() { if (conn != null) { conn.Dispose(); conn = null; } } #endregion #region //执行单条SQL(插入、更新、删除) /// /// 执行单条SQL(插入、更新、删除) /// /// public void ExecuteNonQuery(string sql_) { try { OpenDb(); SqlCommand cm = new SqlCommand(sql_, conn); cm.ExecuteNonQuery(); cm.Dispose(); cm = null; CloseDb(); } catch (Exception ex) { throw new Exception(ex.ToString() + " " + sql_); } } #endregion #region //用于执行带参的SQL(插入、更新、删除) /// /// 用于执行带参的SQL(插入、更新、删除) /// /// sql语句 /// 参数化查询 /// 有多少语句执行成功 public int ExecuteNonQuery(string sql, params SqlParameter[] parameter) { try { OpenDb(); SqlCommand cmd = conn.CreateCommand(); //SqlCommand cm = new SqlCommand(sql_, conn); cmd.CommandText = sql; cmd.Parameters.AddRange(parameter); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (Exception ex) { throw new Exception(ex.ToString() + " " + sql); } } #endregion #region //执行查询语句，返回SqlDataReader /// /// 执行查询语句，返回SqlDataReader /// /// /// /// /// 执行查询语句，返回DataTable /// /// 查询语句 /// /// DataSet public DataTable GetDataTable(string sql, params SqlParameter[] parameter) { try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.AddRange(parameter); using (var da = new SqlDataAdapter(cmd)) { var dt = new DataTable(); var ds = new DataSet(); try { da.Fill(ds, "ds"); if (ds.Tables.Count <= 0) return null; dt = ds.Tables[0]; cmd.Parameters.Clear(); } catch (SqlException ex) { throw new Exception(ex.Message); } return dt; } } catch (Exception ex) { throw new Exception(ex.ToString() + " " + sql); } } #endregion #region //执行查询语句，返回DataSet /// /// 执行查询语句，返回DataSet /// /// 查询语句 /// /// DataSet public DataSet GetDataSet(string sql, params SqlParameter[] parameter) { using (var connection = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.AddRange(parameter); using (var da = new SqlDataAdapter(cmd)) { var ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (SqlException ex) { throw new Exception(ex.Message); } return ds; } } } #endregion } 在这其中，先获得Web.config中的连接数据库的字符串：
private string connectionString = ""; protected SqlConnection conn = null; #region //构造函数 public DbHelper() { //this.connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DBConnectionString"].ToString(); this.connectionString = ConfigurationManager.ConnectionStrings["TestConnectionStrings"].ConnectionString; this.conn = new SqlConnection(connectionString); } #endregion 然后写连接打开/关闭/释放的方法
#region //打开连接 public void OpenDb() { if (conn.State != ConnectionState.Open) { try { conn.Open(); } catch (SqlException ex) { throw ex; } } } #endregion #region //关闭连接 public void CloseDb() { if (!object.Equals(conn, null) && (conn.State != ConnectionState.Closed)) { conn.Close(); } } #endregion #region //释放连接 public void Dispose() { if (conn != null) { conn.Dispose(); conn = null; } } #endregion 下面写增删改的方法，我写了两种，带参的和不带参的
#region //执行单条SQL(插入、更新、删除) ////// 执行单条SQL(插入、更新、删除) /// /// public void ExecuteNonQuery(string sql_) { try { OpenDb(); SqlCommand cm = new SqlCommand(sql_, conn); cm.ExecuteNonQuery(); cm.Dispose(); cm = null; CloseDb(); } catch (Exception ex) { throw new Exception(ex.ToString() + " " + sql_); } } #endregion #region //用于执行带参的SQL(插入、更新、删除) ////// 用于执行带参的SQL(插入、更新、删除) /// /// sql语句 /// 参数化查询 ///有多少语句执行成功 public int ExecuteNonQuery(string sql, params SqlParameter[] parameter) { try { OpenDb(); SqlCommand cmd = conn.CreateCommand(); //SqlCommand cm = new SqlCommand(sql_, conn); cmd.CommandText = sql; cmd.Parameters.AddRange(parameter); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (Exception ex) { throw new Exception(ex.ToString() + " " + sql); } } #endregion
然后是查询，查询我写了三种，第一中返回的 SqlDataReader :
#region //执行查询语句，返回SqlDataReader ////// 执行查询语句，返回SqlDataReader /// /// /// ///public SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameter) { try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.AddRange(parameter); //return cmd.ExecuteReader(CommandBehavior.CloseConnection); SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return myReader; } catch (Exception ex) { throw new Exception(ex.ToString() + " " + sql); } } #endregion
这里用完一定要对SqlDataReader进行Close
第二种返回的DateTable：
#region //执行查询语句，返回DataTable ////// 执行查询语句，返回DataTable /// /// 查询语句 /// ///DataSet public DataTable GetDataTable(string sql, params SqlParameter[] parameter) { try { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.AddRange(parameter); using (var da = new SqlDataAdapter(cmd)) { var dt = new DataTable(); var ds = new DataSet(); try { da.Fill(ds, "ds"); if (ds.Tables.Count <= 0) return null; dt = ds.Tables[0]; cmd.Parameters.Clear(); } catch (SqlException ex) { throw new Exception(ex.Message); } return dt; } } catch (Exception ex) { throw new Exception(ex.ToString() + " " + sql); } } #endregion
第二种返回的DateSet：
#region //执行查询语句，返回DataSet ////// 执行查询语句，返回DataSet /// /// 查询语句 /// ///DataSet public DataSet GetDataSet(string sql, params SqlParameter[] parameter) { using (var connection = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.Parameters.AddRange(parameter); using (var da = new SqlDataAdapter(cmd)) { var ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (SqlException ex) { throw new Exception(ex.Message); } return ds; } } } #endregion