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里面就有值了。

//这是我通常的用法，一般都是用三层架构的，不过在这里简化了