[原]强大的数据库访问类dbhelper 2.0，欢迎拍砖

优点：

1、多数据库支持

2、多数据库类型支持

3、强大的查询公用方法，如支持返回泛型实体对象集合，泛型值类型集合，实体对象，数据集等。

4、所有方法都支持参数化与非参数化访问db，调用十分方便。

5、使用了线程本地存储，使之能支持在业务逻辑层显式控制事务。

6、注释较为详细，配置十分简单，方法较为简洁(加上注释总共200多行代码)。
7、开源，容易修改。

 

源代码：

public class DBManager
    {
        /// <summary>
        /// 默认的数据库连接
        /// </summary>
        public static readonly string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
        /// <summary>
        /// 数据库类型 默认支持sqlserver数据库
        /// </summary>
        public static readonly string dbProviderName = string.IsNullOrEmpty(ConfigurationManager.AppSettings["dbProviderName"])
                                                    ? "System.Data.SqlClient" : ConfigurationManager.AppSettings["dbProviderName"];
        [ThreadStatic]
        static DBHelper helper;

        /// <summary>
        /// 创建默认的数据库访问类
        /// </summary>
        /// <returns></returns>
        public static DBHelper Instance()
        {
            if (helper == null)
            {
                helper = new DBHelper(connectionString, dbProviderName);
                return helper;
            }
            return helper;
        }
    }

 

public class DBHelper
    {
        private string connectionString = string.Empty;
        private DbProviderFactory factory;
        private DbConnection _connection;
        public DbConnection connection
        {
            get { return _connection; }
            set { _connection = value; }
        }
        private DbTransaction _transaction;
        public DbTransaction transaction
        {
            get { return _transaction; }
            set { _transaction = value; ;}
        }

        public DBHelper(string connectionString, string dbProviderName)
        {
            this.connectionString = connectionString;
            factory = DbProviderFactories.GetFactory(dbProviderName);
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenConnection()
        {
            if (connection == null)
            {
                connection = factory.CreateConnection();
                connection.ConnectionString = connectionString;
            }
            if (connection.State != ConnectionState.Open)
                connection.Open();
        }

        /// <summary>
        /// 开启事务
        /// </summary>
        public void BeginTransaction()
        {
            OpenConnection();
            if (transaction == null)
                transaction = connection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务 并且 释放并关闭资源
        /// </summary>
        public void CommitTransaction()
        {
            if (transaction != null)
            {
                transaction.Commit();
                transaction.Dispose();
                transaction = null;
                Dispose();
            }
        }

        /// <summary>
        /// 回滚事务 并且 释放并关闭资源
        /// </summary>
        public void RollbackTransaction()
        {
            if (transaction != null)
            {
                transaction.Rollback();
                transaction.Dispose();
                transaction = null;
                Dispose();
            }
        }

        /// <summary>
        /// 如果没有开启事务就自动释放资源，关闭连接，否则在提交或回滚事务的时候释放
        /// </summary>
        public void Dispose()
        {
            if (transaction == null)
            {
                if (connection != null)
                {
                    connection.Dispose();
                    connection.Close();
                    connection = null;
                }
            }
        }

        private DbCommand CreateCommand(CommandType cmdType, string cmdText, params DbParameter[] cmdParas)
        {
            DbCommand mand = connection.CreateCommand();
            mand.CommandText = cmdText;
            mand.CommandType = cmdType;
            mand.Parameters.AddRange(cmdParas);
            if (transaction != null) mand.Transaction = transaction;
            return mand;
        }

        /// <summary>
        /// 返回一个数据集
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParas"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params DbParameter[] cmdParas)
        {
            try
            {
                OpenConnection();
                DbCommand mand = CreateCommand(cmdType, cmdText, cmdParas);
                DbDataAdapter data = factory.CreateDataAdapter();
                data.SelectCommand = mand;
                DataSet ds = new DataSet();
                data.Fill(ds);
                return ds;
            }
            finally { Dispose(); }
        }

        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParas"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParas)
        {
            try
            {
                OpenConnection();
                DbCommand mand = CreateCommand(cmdType, cmdText, cmdParas);
                return mand.ExecuteNonQuery();
            }
            finally { Dispose(); }
        }

        /// <summary>
        /// 返回结果集中第一行第一列
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParas"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] cmdParas)
        {
            try
            {
                OpenConnection();
                DbCommand mand = CreateCommand(cmdType, cmdText, cmdParas);
                return mand.ExecuteScalar();
            }
            finally { Dispose(); }
        }

        /// <summary>
        /// 返回泛型集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParas"></param>
        /// <returns></returns>
        public IList<T> ExecuteReaderList<T>(CommandType cmdType, string cmdText, params DbParameter[] cmdParas)
        {
            try
            {
                OpenConnection();
                DbCommand mand = CreateCommand(cmdType, cmdText, cmdParas);
                DbDataReader reader = mand.ExecuteReader();
                IList<T> list = ToList<T>(reader);
                return list;
            }
            finally { Dispose(); }
        }

        /// <summary>
        /// 返回一个对象 如数据库无结果返回将抛出异常
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParas"></param>
        /// <returns></returns>
        public T ExecuteReaderObject<T>(CommandType cmdType, string cmdText, params DbParameter[] cmdParas)
        {
            return ExecuteReaderList<T>(cmdType, cmdText, cmdParas)[0];
        }

        /// <summary>
        /// 反射创建泛型集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        private IList<T> ToList<T>(DbDataReader reader)
        {
            Type type = typeof(T);
            IList<T> list = null;
            if (type.IsValueType || type == typeof(string))
                list = CreateValue<T>(reader, type);
            else
                list = CreateObject<T>(reader, type);
            reader.Dispose();
            reader.Close();
            return list;
        }

        private IList<T> CreateObject<T>(DbDataReader reader, Type type)
        {
            IList<T> list = new List<T>();
            PropertyInfo[] properties = type.GetProperties();
            string name = string.Empty;
            while (reader.Read())
            {
                T local = Activator.CreateInstance<T>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    name = reader.GetName(i);
                    foreach (PropertyInfo info in properties)
                    {
                        if (name.Equals(info.Name)) { info.SetValue(local, Convert.ChangeType(reader[info.Name], info.PropertyType), null); break; }
                    }
                }
                list.Add(local);
            }
            return list;
        }

        private IList<T> CreateValue<T>(DbDataReader reader, Type type)
        {
            IList<T> list = new List<T>();
            while (reader.Read())
            {
                T local = (T)Convert.ChangeType(reader[0], type, null);
                list.Add(local);
            }
            return list;
        }
    }

 

调用示例：

 /// <summary>
        /// 事务示例
        /// </summary>
        public void TestExecuteNonQueryBeginTran()
        {

            try
            {
                DBManager.Instance().BeginTransaction();
                DBManager.Instance().ExecuteNonQuery(CommandType.Text, "update  warningTest set pid=0 where id=3");
                DBManager.Instance().ExecuteNonQuery(CommandType.Text, "update  warningTest set pid=aaa where id=4");
                DBManager.Instance().CommitTransaction();
                int num = DBManager.Instance().ExecuteNonQuery(System.Data.CommandType.Text, "select * from warningTest");
            }
            catch { DBManager.Instance().RollbackTransaction(); }
        }

        /// <summary>
        /// 查询实体对象集合示例
        /// </summary>
        public void TestExecuteReaderList()
        {

            SqlParameter para = new SqlParameter("@id", 1);
            warningTest warning = DBManager.Instance().ExecuteReaderObject<warningTest>(CommandType.Text, "select * from warningTest where id=@id", para);
            Assert.AreEqual(1, warning.id);
        }

        /// <summary>
        /// 查询值类型集合示例
        /// </summary>
        public void TestExecuteReaderListByInt()
        {
            IList<string> list = DBManager.Instance().ExecuteReaderList<string>(CommandType.Text, "select Logic from warningTest");
            foreach (string i in list)
            {
                Console.WriteLine(i.ToString());
            }
        }

 

 

下载：源代码

补充说明：数据库备份文件在Data.test\App_Data文件夹下面