简单工厂模式构建数据访问层以实现多种数据库之间的切换

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;
            }
            catch (Exception ex)
            {
                this.dbConnection.Close();
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 执行没有数据集的命令 Update/Delete/Insert
        /// </summary>
        /// <returns></returns>
        public int ExecuteNonQuery()
        {
            try
            {
                if (this.dbConnection.State != ConnectionState.Open)
                {
                    this.dbConnection.Open();
                }
                DbCommand cmd = this.CreateCommand();
                int r = cmd.ExecuteNonQuery();
                if (!IsInTransaction)
                {
                    dbConnection.Close();
                }
                return r;
            }
            catch (Exception ex)
            {
                dbConnection.Close();
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 执行结果的DataTable集
        /// </summary>
        /// <returns></returns>
        public DataTable ExecuteDataTable()
        {
            try
            {
                if (this.dbConnection.State != ConnectionState.Open)
                {
                    this.dbConnection.Open();
                }
                DbDataAdapter adapter = this.CreateAdapter();
                DataTable dt = new DataTable();
                adapter.FillSchema(dt, SchemaType.Mapped);
                adapter.Fill(dt);
                if (!IsInTransaction)
                {
                    dbConnection.Close();
                }
                return dt;
            }
            catch (Exception ex)
            {
                dbConnection.Close();
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 用于分页查询，获取指定页码的数据集
        /// </summary>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="currentPageIndex">当前页码</param>
        /// <returns>指定页的记录集</returns>
        public abstract DataTable ExecuteDataTable(int pageSize,int currentPageIndex);

        #endregion

        #region 将DataTable更新到数据库中

        public int UpdateData(DataTable dt)
        {
            try
            {
                if (this.dbConnection.State != ConnectionState.Open)
                {
                    this.dbConnection.Open();
                }
                DbDataAdapter adapter = this.CreateAdapter();
                if (this.CommandType == CommandType.StoredProcedure)
                {
                    this.CommandType = CommandType.Text;
                }
                this.BuilderCommand(adapter);
                int r = adapter.Update(dt);
                if (!IsInTransaction)
                {
                    dbConnection.Close();
                }
                return r;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion

        #region 事务处理

        /// <summary>
        /// 开始一个事务
        /// </summary>
        public void BegionTransaction()
        {
            try
            {
                if (this.dbConnection.State != ConnectionState.Open)
                {
                    this.dbConnection.Open();
                }
                this.dbConnection.BeginTransaction();
                this.IsInTransaction = true;
            }
            catch (Exception ex)
            {
                this.dbConnection.Close();
                this.IsInTransaction = false;
                throw ex;
            }
        }

        /// <summary>
        /// 回滚一个事务
        /// </summary>
        public void RollbackTransaction()
        {
            try
            {
                this.dbTransaction.Rollback();
                this.dbConnection.Close();
                this.IsInTransaction = false;
            }
            catch (Exception ex)
            {
                this.dbConnection.Close();
                this.IsInTransaction = false;
                throw ex;
            }
        }

        /// <summary>
        /// 提交一个事务
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                this.dbTransaction.Commit();
                this.dbConnection.Close();
                this.IsInTransaction = false;
            }
            catch (Exception ex)
            {
                this.dbConnection.Close();
                this.IsInTransaction = false;
                throw ex;
            }
        }

        #endregion

        #region 建立数据行对象


        #endregion

        #region 对序列的读取

        /// <summary>
        /// 获取序列的值，对于非oracle数据库，必须建立一个名为System_Sequence的表，表的字段为（Name(nvarchar,50),Value(int))
        /// </summary>
        /// <param name="sequenceName"></param>
        /// <returns></returns>
        public abstract int GetSequenceValue(string sequenceName);

        #endregion
    }

2、新建一个参数类

   public class Parameter
    {
       public string Name = string.Empty;
       public object Value = null;
       public Parameter(string name,string value)
       {
           this.Name = name;
           this.Value = value;
       }
    }

3、新建一个Sqlser数据库访问类

   internal class DBhANDlerSQLServer:DBHandler
    {
        public DBhANDlerSQLServer(string connectionString)
            : base()
        {
            this.dbConnection = new SqlConnection(connectionString);
        }
        protected override DbCommand CreateCommand()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = (SqlConnection)this.dbConnection;
            if (this.IsInTransaction)
            {
                cmd.Transaction = (SqlTransaction)this.dbTransaction;
            }
            if (this.CommandType == CommandType.TableDirect)
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("select * from {0}", this.CommandText);
            }
            else
            {
                cmd.CommandType=CommandType;
                cmd.CommandText = CommandText;
            }
            if (this.parameters.Count > 0)
            {
                foreach (Parameter p in parameters)
                {
                    cmd.Parameters.AddWithValue(p.Name, p.Value);
                }
            }
            return cmd;
        }

        protected override DbDataAdapter CreateAdapter()
        {
            SqlCommand cmd = (SqlCommand)this.CreateCommand();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            return adapter;
        }

        protected override void BuilderCommand(DbDataAdapter adapter)
        {
            new SqlCommandBuilder((SqlDataAdapter)adapter);
        }

        protected override int GetTotalCount()
        {
            this.CheckPageSQL();
            string sql = this.CommandText; //保留原始SQL
            string sqlWithOutOrderField = string.Empty; //将原始SQL语句去掉 order by 后的部分，用于查询总记录数
            int startIndex = sql.LastIndexOf("order by");
            if (startIndex >= 0)
            {
                sqlWithOutOrderField = sql.Substring(0, startIndex);
            }
            else
            {
                sqlWithOutOrderField = sql;
            }
            this.CommandText = string.Format("select count(*）from ({0}) t1",sqlWithOutOrderField);
            int r = int.Parse(this.ExecuteScalar().ToString());
            this.CommandText = sql;
            return r;
        }

        public override DataTable ExecuteDataTable(int pageSize, int currentPageIndex)
        {
            this.CheckPageSQL();
            string sql = this.CommandText; //保留原始SQL
            string orderBy = string.Empty; //将order by字句保留下来
            string sqlWithSelectAndOrder = sql.Substring(6); //去掉select以及整个order by
            int startIndex = sqlWithSelectAndOrder.ToLower().LastIndexOf("order by");
            if (startIndex > 0)
            {
                orderBy = sqlWithSelectAndOrder.Substring(startIndex);
                sqlWithSelectAndOrder = sqlWithSelectAndOrder.Substring(0, startIndex);
            }
            else
            {
                throw new Exception("sql的分页查询必须有order by");
            }
            if (pageSize == 0) //返回所有数据
            {
                this.CommandText = sql;
                this.ExecuteDataTable();
            }
            DataTable dt = new DataTable();
            if (currentPageIndex == 1) //如果当前页为1
            {
                this.CommandText = string.Format("select top {0} {1} {2}", pageSize, sqlWithSelectAndOrder, orderBy);
                dt = this.ExecuteDataTable();
            }
            else  //适合sqlserver2005及以上的版本，但必须带 order by子句
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select * from ");
                sb.AppendFormat("(select Row_Number() over ({0}) as RowNum,{1})t1",orderBy,sqlWithSelectAndOrder);
                sb.AppendFormat(" where RowNum between {0} and {1}",pageSize*(currentPageIndex-1),pageSize*currentPageIndex-1);
                this.CommandText = sb.ToString();
                dt = this.ExecuteDataTable();
            }
            this.CommandText = sql;
            return dt;
        }

        public override int GetSequenceValue(string sequenceName)
        {
            //sqlserver 先要建立配套的系列表 System_Sequence
            /*建表语句：if not exists(select * from sysobjects where Name='System_Sequence')
            create table System_Sequence
            {
            Name varchar(50),
            Value int,
            constraint "PK_SsystemSequence" primary key(Name)
            };
            insert into System_Sequence(Name,Value) values('Sequence_<表名>',0); */

            this.CommandType = CommandType.Text;
            this.BegionTransaction();
            this.CommandText = string.Format("Update System_Sequence set Value=Value+1 where Name='{0}'", sequenceName);
            this.ExecuteNonQuery();
            this.CommandText = string.Format("select Value from System_Sequence where Name='{0}'", sequenceName);
            int r = this.ExecuteNonQuery();
            this.CommitTransaction();
            return r;
        }
    }

4、新建一个工厂类

    public enum DatabaseType
    {
        SqlServer = 1,
        Oracle = 2,
        ODBC = 3,
        OLEDB = 4
    }

   public class DBHandlerFactory
    {
       //禁止产生类的实例
       private DBHandlerFactory() { }

       /// <summary>
       /// 读取webconfig中的ConnectionString配置节点构造实例
       /// </summary>
       /// <param name="connStr"></param>
       /// <returns></returns>
       public static DBHandler GetHandler(string connStr)
       {
          ConnectionStringSettings  ccs = ConfigurationManager.ConnectionStrings[connStr];
          string providerName = ccs.ProviderName.ToLower();
          DatabaseType dbType = DatabaseType.SqlServer;
          switch (providerName)
          {
              case "":
              case "sqlserver":
              case "system.data.sqlclient":
                  dbType = DatabaseType.SqlServer;
                  break;
              case "oracle":
              case"system.data.oracleclient":
                  dbType = DatabaseType.Oracle;
                  break;
              case "odbc":
              case "system.data.odbc":
                  dbType = DatabaseType.ODBC;
                  break;
              case "oledb":
              case "system.data.oledb":
                  dbType = DatabaseType.OLEDB;
                  break;
              default:
                  throw new Exception("请按照格式定义ProviderName属性");
          }
          switch (dbType)
          {
              case DatabaseType.SqlServer:
                  return new DBhANDlerSQLServer(ccs.ConnectionString); 
              case DatabaseType.Oracle:
                  return null;
              case DatabaseType.ODBC:
                  return null;
              case DatabaseType.OLEDB:
                  return null;
              default:
                  return null;
          }
       }

       /// <summary>
       /// 直接以连接字符串和数据库类型构造
       /// </summary>
       /// <param name="connStr"></param>
       /// <param name="dbType"></param>
       /// <returns></returns>
       public static DBHandler GetHandler(string connStr,DatabaseType dbType)
       {
           switch (dbType)
           {
               case DatabaseType.SqlServer:
                   return new DBhANDlerSQLServer(connStr);
               case DatabaseType.Oracle:
                   return null;
               case DatabaseType.ODBC:
                   return null;
               case DatabaseType.OLEDB:
                   return null;
               default:
                   return null;
           }
       }
    }

5.....其它的数据库访问类，可以参照sqlserver数据库访问类创建。