C#，sql数据库的增删改查

 

数据库的增删改查，我通常的做法是： 通常新建一个config配置文件用来保存数据库连接字符串 <?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="DB" connectionString="server=.;database=ATMDB;uid=sa;pwd=123456"/> </connectionStrings> </configuration> 然后再建一个专门用来连接数据库的类DBHelper using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Configuration; using System.Data; namespace Dals {   public class DBHelper   {   public string connstr = ConfigurationManager.ConnectionStrings["db1"].ConnectionString;   private SqlConnection _conn;   public SqlConnection Conn   {   get   {   if (_conn == null)   _conn = new SqlConnection(connstr);   return _conn;   }   }   /// <summary>   /// 封装增删改操作代码   /// </summary>   /// <param name="sql">要执行的SQL语句或者存储过程名称</param>   /// <param name="type">命令类型</param>   /// <param name="paras">参数数组</param>   /// <returns></returns>   public bool ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] paras)   {   int result = 0;   try   {   SqlCommand cmd = new SqlCommand(sql, Conn);   cmd.CommandType = type;   //判断是否存在参数   if (paras != null && paras.Length > 0)   cmd.Parameters.AddRange(paras);   Conn.Open();   result = cmd.ExecuteNonQuery();   }   catch   { }   finally   {   Conn.Close();   }   return result > 0 ? true : false;   }   /// <summary>   /// 返回单行单列   /// </summary>   /// <param name="sql">要执行的SQL语句或者存储过程名称</param>   /// <param name="type">命令类型</param>   /// <param name="paras">参数数组</param>   /// <returns></returns>   public object ExecuteScale(string sql, CommandType type, params SqlParameter[] paras)   {   object result = null;   try   {   SqlCommand cmd = new SqlCommand(sql, Conn);   cmd.CommandType = type;   //判断是否存在参数   if (paras != null && paras.Length > 0)   cmd.Parameters.AddRange(paras);   Conn.Open();   result = cmd.ExecuteScalar();   }   catch { }   finally   {   Conn.Close();   }   return result;   }   /// <summary>   /// 返回DataReader，需要注意，获取完数据后，必须关闭DataReader对象   /// </summary>   /// <param name="sql">要执行的SQL语句或者存储过程名称</param>   /// <param name="type">命令类型</param>   /// <param name="paras">参数数组</param>   /// <returns></returns>   public SqlDataReader ExecuteDataReader(string sql, CommandType type, params SqlParameter[] paras)   {   SqlDataReader read = null;   try   {   SqlCommand cmd = new SqlCommand(sql, Conn);   cmd.CommandType = type;   //判断是否存在参数   if (paras != null && paras.Length > 0)   cmd.Parameters.AddRange(paras);   Conn.Open();   //CommandBehavior.CloseConnection:关闭DataReader对象时，自动关闭相应的连接池对象   read = cmd.ExecuteReader(CommandBehavior.CloseConnection);   }   catch   { }   return read;   }   /// <summary>   /// 返回DataSet   /// </summary>   /// <param name="sql">要执行的SQL语句或者存储过程名称</param>   /// <param name="type">命令类型</param>   /// <param name="paras">参数数组</param>   /// <returns></returns>   public DataSet ExecuteDataSet(string sql, CommandType type, params SqlParameter[] paras)   {   DataSet ds = new DataSet();   try   {   SqlCommand cmd = new SqlCommand(sql, Conn);   cmd.CommandType = type;   //判断是否存在参数   if (paras != null && paras.Length > 0)   cmd.Parameters.AddRange(paras);   SqlDataAdapter da = new SqlDataAdapter(cmd);   da.Fill(ds);   }   catch { }   return ds;   }   /// <summary>   /// 封装带事务的增删改操作代码   /// </summary>   /// <returns></returns>   public bool ExecuteNonQuery(string sql, SqlConnection tranconn, SqlTransaction tran, CommandType type, params SqlParameter[] paras)   {   int result = 0;   try   {   SqlCommand cmd = new SqlCommand(sql, tranconn);   cmd.Transaction = tran;   cmd.CommandType = type;   //判断是否存在参数   if (paras != null && paras.Length > 0)   cmd.Parameters.AddRange(paras);   result = cmd.ExecuteNonQuery();   }   catch   { }   return result > 0 ? true : false;   }   } }   /// <summary>   /// 比如说是根据传过来的员工编号查员工   /// </summary>   /// <param name="empid">员工编号</param>   /// <returns>查询到的datatable数据集</returns>   public DataTable SearchEmp(string empid)   {   string sql;   if (empid.Equals(""))   {   sql = "select * from employee";   }   else   {   sql = "select * from employee where empNo='" + empid + "'";   }   return db.ExecuteDataSet(sql, CommandType.Text).Tables[0];   } 然后在点查询的时候：调用   DataTable dt = SearchEmp(this.txtEmpID.Text.Trim());   //查询出来了给你的DataGridView指定数据源，   dgvEmp.DataSource = dt;   //这样DataGridView里面就有值了。 //这是我通常的用法，一般都是用三层架构的，不过在这里简化了