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

优点：

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

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

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

4、注释较为详细，方法较为简洁(加上注释总共200多行代码)。
5、开源，容易修改。

 

缺点：

暂时仅支持sqlserver数据库。

 

源代码：

public class SqlHelper
    {
        public string connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
        private SqlConnection _connection;
        public SqlConnection connection
        {
            get { return _connection; }
            set { _connection = value; }
        }
        private SqlTransaction _transaction;
        public SqlTransaction transaction
        {
            get { return _transaction; }
            set { _transaction = value; ;}
        }

        private SqlHelper()
        {
        }

        /// <summary>
        /// 如果当前线程已经存在SqlHelper则使用当前线程的SqlHelper，否则重新创建一个SqlHelper
        /// </summary>
        /// <returns></returns>
        public static SqlHelper Instance()
        {
            object obj = Thread.GetData(Thread.GetNamedDataSlot("SqlHelper"));
            if (obj == null)
            {
                SqlHelper helper = new SqlHelper();
                Thread.SetData(Thread.GetNamedDataSlot("SqlHelper"), helper);
                return helper;
            }
            return (SqlHelper)obj;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenConnection()
        {
            if (connection == null) connection = new SqlConnection(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 SqlCommand CreateCommand(CommandType cmdType, string cmdText, params SqlParameter[] cmdParas)
        {
            SqlCommand 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 SqlParameter[] cmdParas)
        {
            try
            {
                OpenConnection();
                SqlCommand mand = CreateCommand(cmdType, cmdText, cmdParas);
                SqlDataAdapter data = new SqlDataAdapter(mand);
                DataSet ds = new DataSet();
                data.Fill(ds);
                return ds;
            }
            catch (Exception ex) { throw ex; }
            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 SqlParameter[] cmdParas)
        {
            try
            {
                OpenConnection();
                SqlCommand mand = CreateCommand(cmdType, cmdText, cmdParas);
                return mand.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            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 SqlParameter[] cmdParas)
        {
            try
            {
                OpenConnection();
                SqlCommand mand = CreateCommand(cmdType, cmdText, cmdParas);
                return mand.ExecuteScalar();
            }
            catch (Exception ex) { throw ex; }
            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 SqlParameter[] cmdParas)
        {
            try
            {
                OpenConnection();
                SqlCommand mand = CreateCommand(cmdType, cmdText, cmdParas);
                SqlDataReader reader = mand.ExecuteReader();
                IList<T> list = ToList<T>(reader);
                return list;
            }
            catch (Exception ex) { throw ex; }
            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 SqlParameter[] 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>(SqlDataReader 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>(SqlDataReader 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>(SqlDataReader 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
            {
                SqlHelper.Instance().BeginTransaction();
                SqlHelper.Instance().ExecuteNonQuery(CommandType.Text, "update  warningTest set pid=0 where id=3");
                SqlHelper.Instance().ExecuteNonQuery(CommandType.Text, "update  warningTest set pid=aaa where id=4");
                SqlHelper.Instance().CommitTransaction();
                int num = SqlHelper.Instance().ExecuteNonQuery(System.Data.CommandType.Text, "select * from warningTest");
            }
            catch { SqlHelper.Instance().RollbackTransaction(); }
        }

        /// <summary>
        /// 返回实体集合示例
        /// </summary>
        public void TestExecuteReaderList()
        {

            SqlParameter para = new SqlParameter("@id", 1);
            warningTest warning = SqlHelper.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 = SqlHelper.Instance().ExecuteReaderList<string>(CommandType.Text, "select Logic from warningTest");
            foreach (string i in list)
            {
                Console.WriteLine(i.ToString());
            }
        }

下载： 项目源代码

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