Sun_Blue_Sky

菩提本无树,明镜亦非台,本来无一物,何处惹尘埃 寻求内心的平静
posts - 21, comments - 75, trackbacks - 0, articles - 93
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

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

Posted on 2011-09-03 11:33  Sun_Blue_Sky  阅读(...)  评论(...编辑  收藏

优点:

1、多数据库支持

2、多数据库类型支持

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

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

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

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

 

源代码:

DBManager
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;
        }
    }

 

DBHelper
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;
        }
    }

 

调用示例:

View Code
 /// <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文件夹下面