分享我的数据库访问帮助类EasyDBUtility.dll C#

   先说说写这个帮助类的原由吧，以前编写项目代码，发现读取数据库数据到List集合或添加、更新参数化SQL语句的时候，需要编写大量的代码，尤其是表的字段比较多的时候，感觉非常明显，不仅是个体力活，而且编程效率很低。在Google，百度了很多帮助类后，发现一个问题是很多帮助类自定义的方法名称很多，不利于初学者快速掌握，而且性能灵活性方面也感觉不太好，于是我在以前老师给我的数据库帮助类上面进行了大量改进，使其使用简单方便，效率更高。在编写这个帮助类的时候，其实ORM已经出现，但是个人感觉ORM一个是效率不是很好，再就是对于刚入职的员工，有很多根本就不会，还得重新学习，这样对项目开发有很大影响。分享这个帮助类的原因一个是代码现在基本上稳定了(使用了2年半)，再就是看到了很多人刚入职的都在重复我以前的痛苦，包括我的同学、同事，深有体会。好了，不废话了，下面就对EasyDBUtility进行介绍。个人语言组织能力有限，希望大家理解。

数据库访问帮助类EasyDBUtility简介
1.支持.net frmework3.0及以上版本；
2.支持SqlServer、SqlServerCe、Access、Sqlite、MySql、Oracle、PostgreSQL、Sybase、Db2、Firebird数据库；
3.支持增删改查、参数化、存储过程、事务、分布式事务等；
4.支持多个数据库同时调用，只需传入不同连接字符串；
5.增删改查时能够根据传入的对象自动参数化SQL语句中需要参数化的变量，减少参数化代码编写，在插入和更新字段比较多时优点明显；
6.查询使用Emit绑定DataReader中数据到List的对象集合，缓存Emit后效率高过通过非索引方式获取数据的手写代码，开发者不用再繁复的编写读取DataReader中数据的大量代码，有效节约开发时间；
7.在执行完sql命令后自动关闭数据库连接，除非设置AutoClose属性为false，帮助类实现了IDisposable接口，即使忘记关闭数据库，在GC回收时也会关闭未使用的数据库连接；
8.使用比较简单灵活，基本没有自创语法或方法名称，CreateCommand()、AddParameter()、CommandText()、BeginTransaction()、Commit()、RollBack()、Close()、ExecuteNonQuery()、ExecuteReader()、ExecuteScalar()、ExecuteDataAdapter()的使用和.net frmework中自带数据库访问类的使用很相似，ExecuteMultipleReader()的使用可以参照示例的第三点的(4)小点使用，用于对查询的多个结果集合的读取；
9.帮助类中提供数据库操作对象的对外属性，可以方便设置数据库的相关参数，如helper.Cmd.CommandTimeout=60等

  /// <summary>
  /// 数据库连接对象
  /// </summary>
  public SqlConnection Conn
  {
      get { return _conn; }
  }
  /// <summary>
  /// 执行SQL命令对象
  /// </summary>
  public SqlCommand Cmd
  {
      get { return _cmd; }
      set { _cmd = value; }
  }
  /// <summary>
  /// 事务
  /// </summary>
  public SqlTransaction Trans
  {
      get { return _trans; }
      set { _trans = value; }
  }

使用注意：调用需要传入sql语句，不提供ORM支持，不提供日志功能，执行错误后直接向上抛出异常，需在最外层处理

待改善和缺点：
1.查询结果集自动填充到对象集合，对象的属性名称必须和数据库列名称相同（已实现可以忽略大小写），未实现特性Attribute；
2.增删改查时自动参数化的sql语句中的参数化变量名称也必须和对象中的属性名称相同，也就是和数据库中的字段名相同（已实现可以忽略大小写），未实现特性Attribute；
3.未能对对象中的对象的属性赋值；
4.未完成代码生成器编写，仍然需要编写大量代码的工作；
5.帮助类有待大量测试；

 

帮助类中的属性和方法

属性:
Conn：对外的数据库连接对象，如SqlConnection
Cmd：对外的执行SQL命令对象，如SqlCommand
Trans：对外的事务对象，如SqlTransaction
AutoClose：设置是否自动关闭数据库连接，默认自动关闭，如设置为false，在执行完一次Sql命令后将不会关闭数据库连接

方法：
void CreateCommand(string sql)：
初始化Command对象，相当于SqlCommand cmd=new SqlCommand(sql,conn);
void CreateStoredProcedureCommand(string name)：
初始化调用存储过程的Command对象，相当于SqlCommand cmd=new SqlCommand(sql,conn);cmd.CommandType=CommandType.StoredProcedure;
void CommandText(string sql)：
void CommandText(string sql, CommandType commandType)：
再次设置要执行的sql语句，调用此方法时要设置AutoClose为false，并在所有sql命令执行完毕后调用Close()关闭数据库链接
void AddParameter(string name, object value)：
void AddParameter(params IDataParameter[] param)：
void AddParameter(string name, SqlDbType type, int size, object value, ParameterDirection direction)：
void AddParameter(object data)：根据对象自动设置参数化sql语句的参数值
object GetValue(string name)：获取调用存储过程返回的Output或Return参数值
int ExecuteNonQuery()：执行增、删、改操作
object ExecuteScalar()：执行查询命令并返回结果集第一行第一列的值
DataReader ExecuteReader()：执行查询命令,返回DataReader结果集，可循环遍历
List<ClassName> ExecuteReader<ClassName>()：执行查询命令,返回ClassName类型的结果集，自动读取结果到对象中
List<List<ClassName>> ExecuteMultipleReader<ClassName>()：执行查询命令,返回多个结果集
DataSet ExecuteDataAdapter()：用SqlDataAdapter获取DataSet的数据集合
void BeginTransaction()：启用事务
void Commit()：事务提交
void RollBack()：事务回滚
void Close()：关闭数据库连接

 

使用示例

如何关联数据库？
在web.config或App.config中配置数据库连接字符串即可
默认的名称为SqlConnectionString、SqlCeConnectionString、OleDbConnectionString、SqliteConnectionString、MySqlConnectionString、OracleConnectionString、
PostgreConnectionString、SybaseConnectionString、Db2ConnectionString、FirebirdConnectionString，该名称的命名规则为帮助类类名去掉Helper后加上ConnectionString

<connectionStrings>
    <add name="SqlConnectionString" connectionString="数据库连接字符串"/>
</connectionStrings>

 

以下以访问SqlServer2005数据库示例，其它数据库访问类似

定义一个实体类User

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool Sex { get; set; }
    public int NationId{get;set;}
    public string NationName{get;set;}
}

无参数化的增删改查可以调用静态类SimpleDBHelper<T>，T为要调用的数据库帮助类如SqlHelper；
不需要参数化增删改查只需一行代码搞定，大部分需要参数化增删改查也只要3-5行代码即可；

一、添加
(1).无参数化添加

int result=SimpleDBHelper<SqlHelper>.ExecuteNonQuery("insert into [User] values('事理',1)");

(2).参数化添加

SqlHelper helper = new SqlHelper();
helper.CreateCommand("insert into [User] values(@Name,@Sex)");
helper.AddParameter("@Name",name);
helper.AddParameter("@Sex",sex);
helper.ExecuteNonQuery();

(3).根据传入对象自动参数化

public bool Add(UserData data)
{
    SqlHelper helper = new SqlHelper();
    helper.CreateCommand("insert into [User] values(@Name,@Sex)");
    //增删改查都可以用，前提是Sql语句中的参数化变量名称必须和对象中的属性名称相同，不区分大小写
    //方法中有缓存对象参数，再次调用将从缓存中读取，提高根据对象自动参数化效率，参考Pet Shop 5.0中参数缓存
    helper.AddParameter(data);
    helper.ExecuteNonQuery();
}

也可以

SqlHelper helper = new SqlHelper();
helper.CreateCommand("insert into [User] values('事理',1)");
helper.ExecuteNonQuery();

 

二、删除更新和添加的代码类似，CreateCommand传入Sql语句为更新或删除语句即可

 

三、查询
(1).无参数化查询

List<User> list = SimpleDBHelper<SqlHelper>.ExecuteReader<User>("select * from [User]");

(2).参数化查询

public IList<User> Get(UserData data)
{
    //不写连接字符串，在web.config中必须配置SqlConnectionString
    SqlHelper helper = new SqlHelper();
    helper.CreateCommand("select * from [User] where Name=@Name and Id=@Id");
    helper.AddParameter(data);
    //采用Emit绑定DataReader中数据到对象，缓存后效率高过硬编码，不用再写繁复的写读取DataReader数据到对象的代码
    List<User> list = helper.ExecuteReader<User>();
    return list;
}
public IList<User> Get(UserData data)
{
    //如果要连接多个数据库，直接指定不同的连接字符串即可
    SqlHelper helper = new SqlHelper(System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnectionString2"].ConnectionString);
    helper.CreateCommand("select * from [User] where Name=@Name");
    helper.AddParameter(data);
    List<User> list = helper.ExecuteReader<User>();
    return list;
}

(3).如果读取的数据有多列(多表联查)，而没有对应的实体类，可以DataReader类来遍历数据

SQLHelper helper = new SQLHelper();
helper.AutoClose = false;//设置在执行完sql语句后不自动关闭数据库连接
helper.CreateCommand("select * from [User]");
DataReader dr = helper.ExecuteReader();
//var list = helper.ExecuteReader<User>();
List<User> list = new List<User>();
while (dr.Read())
{
    User data=new User();
    data.Id=dr.GetInt("Id");
    datat.Name=dr.GetString("Name");
    data.Sex=dr.GetBoolean("Sex");
    data.NationId=dr.GetInt("NationId");
    //使用CommandText方法
    helper.CommandText("select NationName from Nation where NationId="+data.NationId);
    object obj=helper.ExecuteScalar();
    data.NationName=obj!=null?obj.ToString():string.Empty;
    list.Add(data);    
}
helper.Close();//关闭数据库

(4).如果要读取多条sql语句同时执行返回的结果集，可以用.ExecuteMultipleReader<string>()读取，可用于文章分页

string sql="select content from News where Id=12;select Author from Author where NewId=12;";
SqlHelper helper = new SqlHelper();
helper.CreateCommand(sql);
List<List<string>> list = helper.ExecuteMultipleReader<string>();
StringBuilder content = new StringBuilder();
foreach (List<string> item in list)
{
    foreach (string data in item)
    {
        content.Append(data);
    }
}

 

四、调用分页存储过程，普通sql命令和存储过程区别在CreateStoredProcedureCommand不同
如果调用的是TableDirect，可以helper.CreateCommand(sql);helper.Cmd.CommandType = CommandType.TableDirect;进行类型更改

public static List<ClassName> GetAll<ClassName>(DataPage dataPage)
{
    SqlHelper helper = new SqlHelper();
    helper.CreateStoredProcedureCommand("[proc_DataPagination]");
    helper.AddParameter("@Table", dataPage.TableName);
    helper.AddParameter("@Fields", dataPage.Fields);
    helper.AddParameter("@Where", dataPage.Where);
    helper.AddParameter("@OrderBy", dataPage.OrderBy);
    helper.AddParameter("@CurrentPage", dataPage.CurrentPage);
    helper.AddParameter("@PageSize", dataPage.PageSize);
    helper.AddParameter("@GetCount", 0);
    helper.AddParameter("@Count", SqlDbType.Int, 0, null, ParameterDirection.Output);//输出参数
    List<ClassName> list = helper.ExecuteReader<ClassName>();
    dataPage.RecordSum = Convert.ToInt32(helper.GetValue("@Count"));
    return list;
}

 

五、事务使用
(1).普通事务

SqlHelper helper = new SqlHelper();
helper.BeginTransaction();
helper.CreateCommand("delete from [user] where Id=@Id");            
for (int i = 0; i < ids.Length; i++)
{
    if (i == 0)
    {
        helper.AddParameter("@Id", ids[i]);
        helper.Cmd.Prepare();
    }
    else 
    {
        helper.Cmd.Parameters[0].Value = ids[i];
    }
    int result= helper.ExecuteNonQuery();
    if (result <= 0)
    {
        helper.RollBack();
        break;
    }
}
//事务提交
helper.Commit();

 

(2).分布式事务

//如果提示MSDTC不可用，需要启用服务，控制面板－－管理工具－－服务－－Distributed Transaction Coordinator
//http://tangjun141.blog.163.com/blog/static/56462350200963073824318/
//http://hi.baidu.com/mytot/item/2a2acd333fd1cf20b2c0c596
//开启数据库远程http://www.cnblogs.com/haoyi0418/articles/1039543.html
//配置远程事务访问http://hi.baidu.com/yyylajzwwfbegpe/item/1a426c0add1f596dd55a119f
/*
--隔离级别
级别一 read uncommitted System.Transactions.IsolationLevel.ReadUncommitted --未提交读

级别二 read committed System.Transactions.IsolationLevel.ReadCommitted --提交读

级别三 repeatable read System.Transactions.IsolationLevel.RepeatableRead --可重复读

级别四 serializable System.Transactions.IsolationLevel.Serializable --可串行读
*/

TransactionOptions op = new TransactionOptions();
//op.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;  //提交读
op.IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead;  //提交读
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, op))
{
    try
    {
        SimpleDBHelper<SqlHelper>.ExecuteNonQuery("insert into [User] values('事理',1)");
        SqlHelper helper2 = new SqlHelper(System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectionString2"].ConnectionString);
        //helper2.CreateCommand("insert into [User] values('事理2',1)");//成功数据
        helper2.CreateCommand("insert into [User] values(事理2,1)");//错误插入，如果此处添加失败，并且上面表也没有数据，则说明分布式事务成功
        helper2.ExecuteNonQuery();
        scope.Complete(); //提交
    }
    catch
    {
    }                
}
MessageBox.Show("完毕");

帮助类参考：
1.http://my.csdn.net/qldsrx提供的SqlMapper，非常感谢qldsrx在我发布的Emit相关帖子中的解答
2.http://automapper.codeplex.com/，AutoMapper开源项目，非常强大
3.参考微软开源项目Pet Shop 5.0中DBUtitlity

如果有好的建议或者发现bug，请发邮件到qin_shili@qq.com或到我的博客http://www.cnblogs.com/slyzly留言，代码如有更新，将在http://www.cnblogs.com/slyzly中进行公布，如果要支持.net2.0，可以使用源码里面的DBUtility文件夹下面的代码

 

源代码下载

http://pan.baidu.com/s/18KiHg

解压密码：shili

链接已经更新，目前发现一个待优化问题是当iis应用池回收时，导致EasyDBUtility的缓存丢失，应该将缓存持久化，暂未解决。

2014-6-3日优化更新去掉DynamicBuilder泛型类