关于DbHelper的思考

最近一直在思考DbHelper怎么做到OOP的原则，抽象工厂是个不错的选择，但是为每一个不同连接的DbHelper都创建一个操作类，发现代码量确实大了点。而我们通常喜欢只new一个DbHelper达到切换数据库只需配置连接字符串即可。自己写了个简单的粗线条的东西，供日后参考。代码如下，后期如需增加方法，只需要修改接口，DbHelper实现接口即可，而其中的抽象类，则负责增加不同的数据的连接对象即可。个人觉得代码量减少了很多，现只实现了mssqlserver部分功能：

接口：

public interface IDbHelper
{
    int ExecuteNonQuery(string sql);
    DataTable ExecuteSql(string sql);
    object ExecuteScalar(string sql);
    void AddParameter(string key, object value);
    void BeginTransaction();
    void RollbackTransaction();
    void CommitTransaction();
}

枚举：

public enum DataBaseType
{ 
    SqlServer
}

抽象类：

    public class DbAbstract : IDisposable
    {
        protected DbConnection con = null;
        protected DbCommand cmd = null;
        protected DbTransaction trans = null;
        protected DataBaseType dbType;
        protected bool hasBegunTrans = false;
        protected bool disposed = false;

        public DbAbstract(string connectionString)
        {
            if (connectionString.Contains("Data Source="))
            {
                con = new SqlConnection(connectionString);
                cmd = con.CreateCommand();
                dbType = DataBaseType.SqlServer;
            }
        }

        protected virtual DbParameter NewParameter()
        {
            switch (dbType)
            {
                case DataBaseType.SqlServer:
                    return new SqlParameter();
                default:
                    throw new Exception("不支持该数据库");
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposed)
                return;
            if (disposing)
            {
                if (con != null)
                    con.Dispose();
                if (cmd != null)
                    cmd.Dispose();
                if (trans != null)
                    trans.Dispose();
            }
        }

        public DbConnection Connection
        {
            get { return con; }
        }

        public string ConnectionString
        {
            get { return con == null ? "" : con.ConnectionString; }
            set
            {
                if (con == null)
                    throw new Exception("DbConnection未初始化，不能设置连接字符串！");
                con.ConnectionString = value;
            }
        }

        public bool HasBegunTrans
        {
            get { return hasBegunTrans; }
        }
    }

DbHelper类：

    public class DbHelper : DbAbstract, IDbHelper
    {
        public DbHelper(string connectionString)
            : base(connectionString)
        {

        }

        private void OpenCon()
        {
            if (con != null && con.State == ConnectionState.Closed)
            {
                con.Open();
            }
        }

        private void CloseCon()
        {
            if (cmd.Parameters.Count > 0)
                cmd.Parameters.Clear();
            if (con != null && con.State != ConnectionState.Closed)
            {
                if (!hasBegunTrans)
                {
                    if (trans != null)
                    {
                        trans.Dispose();
                        trans = null;
                    }
                    con.Close();
                }
            }
        }

        public int ExecuteNonQuery(string sql)
        {
            try
            {
                SetCommandForSql(sql);
                OpenCon();
                int res = cmd.ExecuteNonQuery();
                return res;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                CloseCon();
            }
        }

        public DataTable ExecuteSql(string sql)
        {
            try
            {
                DataTable dt = new DataTable();
                SetCommandForSql(sql);
                OpenCon();
                DbDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                CloseCon();
            }

        }

        public object ExecuteScalar(string sql)
        {
            try
            {
                SetCommandForSql(sql);
                OpenCon();
                return cmd.ExecuteScalar();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                CloseCon();
            }
        }

        public void AddParameter(string key, object value)
        {
            DbParameter parameter = NewParameter();
            parameter.ParameterName = key;
            parameter.Value = value;
            cmd.Parameters.Add(parameter);
        }

        public void BeginTransaction()
        {
            OpenCon();
            trans = con.BeginTransaction();
            cmd.Transaction = trans;
            hasBegunTrans = true;
        }

        public void RollbackTransaction()
        {
            if (!hasBegunTrans)
                return;
            trans.Rollback();
            hasBegunTrans = false;
            CloseCon();
        }

        public void CommitTransaction()
        {
            if (!hasBegunTrans)
                return;
            trans.Commit();
            hasBegunTrans = false;
            CloseCon();
        }

        private void SetCommandForSql(string sql)
        {
            if (cmd.CommandType != CommandType.Text)
                cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
        }

        ~DbHelper()
        {
            Dispose(false);
        }
    }