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; } /// <summary> /// 执行不带参数的增删改SQL语句或存储过程 /// </summary> /// <param name="cmdText">增删改SQL语句或存储过程</param> /// <param name="ct">命令类型</param> /// <returns></returns> 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; } /// <summary> /// 执行带参数的增删改SQL语句或存储过程 /// </summary> /// <param name="cmdText">增删改SQL语句或存储过程</param> /// <param name="ct">命令类型</param> /// <returns>int值</returns> 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; } /// <summary> /// 执行查询SQL语句或存储过程 /// </summary> /// <param name="cmdText">查询SQL语句或存储过程</param> /// <param name="ct">命令类型</param> /// <returns>Table值</returns> 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; } /// <summary> /// 执行带参数的查询SQL语句或存储过程 /// </summary> /// <param name="cmdText">查询SQL语句或存储过程</param> /// <param name="paras">参数集合</param> /// <param name="ct">命令类型</param> /// <returns>Table值</returns> 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; } /// <summary> /// 执行带参数的Scalar查询 /// </summary> /// <param name="cmdText">查询SQL语句或存储过程</param> /// <param name="paras">参数集合</param> /// <param name="ct">命令类型</param> /// <returns>一个int型值</returns> 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; }