1、新建一个 数据库访问基类DBHandler
- /// <summary>
- /// 数据库访问基类
- /// </summary>
- public abstract class DBHandler
- {
- public DBHandler() { }
- #region 需要在子类中初始化的与数据库相关的特征类
- protected DbConnection dbConnection = null; //连接对象
- protected DbTransaction dbTransaction = null; //事务对象
- protected abstract DbCommand CreateCommand(); //从子类中构建DbCommand对象
- protected abstract DbDataAdapter CreateAdapter(); //从子类中构建DbDataAdapter对象
- protected abstract void BuilderCommand(DbDataAdapter adapter); //用于Update方法,构建DbDataAdapter中的UpdateCommand/InsertCommand/DeleteCommand
- protected abstract int GetTotalCount(); //用于分页查询,获取总记录数
- #endregion
- #region 子类中要用的数据或方法
- protected List<Parameter> parameters = new List<Parameter>();
- protected bool IsInTransaction = false; //是否处于事务当中
- //用于分页查询,检查当前SQL是否符合基本查询要求
- protected void CheckPageSQL()
- {
- this.CommandType = CommandType.Text;
- if (!this.CommandText.StartsWith("select", true, null))
- {
- throw new Exception("sql语句必须是select开头");
- }
- if (IsInTransaction)
- {
- throw new Exception("分页查询不能在事务中");
- }
- }
- #endregion
- #region 用于输入初始条件的属性和方法
- /// <summary>
- /// sql语句或存储过程名称
- /// </summary>
- public string CommandText { get; set; }
- /// <summary>
- /// 执行类型是SQL还是存储过程
- /// </summary>
- public CommandType CommandType { get; set; }
- /// <summary>
- /// 添加参数,将参数添加到List<T>列表中保存起来
- /// </summary>
- /// <param name="paraName">参数名</param>
- /// <param name="paraValue">参数值</param>
- public void AddParameter(string paraName, string paraValue)
- {
- this.parameters.Add(new Parameter(paraName,paraValue));
- }
- /// <summary>
- /// 清空当前参数列表
- /// </summary>
- public void ClearParameter()
- {
- this.parameters.Clear();
- }
- /// <summary>
- /// 用于分页查询,获取查询到的总记录数
- /// </summary>
- public int TotalCount
- {
- get
- {
- return this.GetTotalCount();
- }
- }
- #endregion
- #region 获取数据库的返回值
- /// <summary>
- /// 获取执行结果的第一行第一列的值
- /// </summary>
- /// <returns></returns>
- public object ExecuteScalar()
- {
- try
- {
- if (dbConnection.State != ConnectionState.Open)
- {
- dbConnection.Open();
- }
- DbCommand cmd = this.CreateCommand();
- object r = cmd.ExecuteScalar();
- if (!this.IsInTransaction)
- {
- dbConnection.Close();
- }
- return r;
- }