using System;  using System.Collections.Generic;  using System.Linq;  using System.Text;  using System.Data;  using System.Data.SqlClient;    namespace Book.DAL  {      public class DBHelper      {          private SqlConnection conn = null;          private SqlCommand cmd = null;          private SqlDataReader sdr = null;          public DBHelper()          {              string connStr = "data Source=.;database=MYBOOKSHOP;uid=sa;pwd=123";              conn = new SqlConnection(connStr);          }            private SqlConnection GetConn()          {              if (conn.State == ConnectionState.Closed)              {                  conn.Open();              }              return conn;          }            /// 
          ///  执行不带参数的增删改SQL语句或存储过程          /// 
          /// 增删改SQL语句或存储过程          /// 命令类型          /// 
               public int ExecuteNonQuery(string cmdText, CommandType ct)          {              int res;              try              {                  cmd = new SqlCommand(cmdText, GetConn());                  cmd.CommandType = ct;                  res = cmd.ExecuteNonQuery();              }              catch (Exception ex)              {                  throw ex;              }              finally              {                  if (conn.State == ConnectionState.Open)                  {                      conn.Close();                  }              }              return res;          }            /// 
          ///  执行带参数的增删改SQL语句或存储过程          /// 
          /// 增删改SQL语句或存储过程          /// 命令类型          /// 
     
      int值
               public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)          {              int res;              using (cmd = new SqlCommand(cmdText, GetConn()))              {                  cmd.CommandType = ct;                  cmd.Parameters.AddRange(paras);                  res = cmd.ExecuteNonQuery();              }              return res;          }            /// 
          ///  执行查询SQL语句或存储过程          /// 
          /// 查询SQL语句或存储过程          /// 命令类型          /// 
     
      Table值
               public DataTable ExecuteQuery(string cmdText, CommandType ct)          {              DataTable dt = new DataTable();              cmd = new SqlCommand(cmdText, GetConn());              cmd.CommandType = ct;              using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))              {                  dt.Load(sdr);              }              return dt;          }            /// 
          ///  执行带参数的查询SQL语句或存储过程          /// 
          /// 查询SQL语句或存储过程          /// 参数集合          /// 命令类型          /// 
     
      Table值
               public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)          {              DataTable dt = new DataTable();              cmd = new SqlCommand(cmdText, GetConn());              cmd.CommandType = ct;              cmd.Parameters.AddRange(paras);              using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))              {                  dt.Load(sdr);              }              return dt;          }              /// 
          /// 执行带参数的Scalar查询          /// 
          /// 查询SQL语句或存储过程          /// 参数集合          /// 命令类型          /// 
     
      一个int型值
               public int ExecuteCheck(string cmdText, SqlParameter[] paras, CommandType ct)          {              int result;              using (cmd = new SqlCommand(cmdText, GetConn()))              {                  cmd.CommandType = ct;                  cmd.Parameters.AddRange(paras);                  result = Convert.ToInt32(cmd.ExecuteScalar());              }              return result;          }      }  }

 

用法

// 判断用户名密码是否正确              public bool CheckUser(string LoginId, string LoginPwd)              {                  bool check = false;                  string cmdText = "procCheckUser";                  SqlParameter[] parm = new SqlParameter[]                  {                      new SqlParameter("@LoginId",LoginId),                      new SqlParameter("@LoginPwd",LoginPwd)                  };                  int one = dh.ExecuteCheck(cmdText, parm, CommandType.StoredProcedure);                  if (one > 0)                  {                      return true;                  }                  return check;              }                                             //根据图书类型显示该类型所有图书              public DataTable SelectAllByType(int TypeId)              {                  string cmdText = "procSelectAllByType";                  SqlParameter[] parm = new SqlParameter[]{                      new SqlParameter("@TypeId",TypeId)                      };                  dt = dh.ExecuteQuery(cmdText, parm, CommandType.StoredProcedure);                  return dt;              }