这个类是采用工厂模式创建DB连接的封装

发布 ASP.NET 730

搭建一套自己实用的.net架构(3)【ORM-Dapper+DapperExtensions】

现在成熟的ORM比比皆是,这里只介绍Dapper的使用(最起码我在使用它,已经运用到项目中,小伙伴们反馈还可以)。


????

优点:


????

1、开源、轻量、小巧、上手容易。


????

2、支持的数据库还蛮多的, Mysql,SqlLite,Sqlserver,Oracle等一系列的数据库。


????

3、Dapper原理通过Emit反射IDataReader的序列队列来快速的得到和产生对象。性能貌似很牛逼的样子


????

缺点:


????

作为一款ORM太过于轻量级了,根据对象自动生成sql的功能还是空白,需要自己来扩展,


????

当然这也是优点,? 好声音的导师们经常说某人就是张白纸……??


????

?


????

因此针对Dapper已经有很多成熟的扩展项目了,Dapper.Rainbow、Dapper.Contrib,DapperExtensions。


????

我们这里介绍的是DapperExtensions


????

?


????

dapper-dot-net源码:https://github.com/StackExchange/dapper-dot-net?? (更新频率快,项目包含了各种除了Dapper-Extensions的?扩展项目)


????

Dapper-Extensions?源码:https://github.com/tmsmith/Dapper-Extensions?


????

Dapper-Extensions的优点


????

1、开源


????

2、针对Dapper封装了常用的CRUD方法,有独立的查询语法。


????

3、需要映射的实体类本身0配置,无需加特性什么的。是通过独立的映射类来处理,可以设置类映射到DB的别名,字段的别名等等。


????

Dapper-Extensions的缺点:


????

1、好几年没更新了


????

2、不支持oracle(木有oracle的方言,已经搞定)??


????

3、不能同时支持多种数据库(已经搞定)


????

4、部分代码有些bug(发现的都搞定了)


????

?


????

下面先简单介绍一下Dapper的基本语法。


????

Dapper就一个.cs文件,可以放到项目代码中直接编译,也可以直接引用DLL文件。


????

Dapper对DB的操作依赖于Connection,为了支持多库,咱们用?IDbConnection conn


????

????
using (IDbConnection conn = GetConnection())
???? {
????
const string query = "select * from XO order by id desc";
????
return conn.Query(query,null);
???? }

????

????

下面是带参数的语法


????

????
int xoID=666; //变量主键
using (IDbConnection conn = GetConnection())
???? {
????
const string query = "select * from XO where Id=@MyID";
????
return conn.Query(query, new { MyID = xoID});
????
???? }

????

????

?


????

各种方法都重载了事务的操作,一般的数据库操作都支持。但是每次执行都需要传递sql,而且每次都要使用Using,看着不爽啊, 这……?


????

好吧下面简单介绍下使用Dapper-Extensions的基本语法(在Dapper-Extensions ?的基础上用了Repository模式,代码效果如下)。


????

????
            //实体类
???? DemoEntity entity = new DemoEntity();
????
//根据实体主键删除
???? this.Delete(entity);
????
//根据主键ID删除
???? this.Delete(1);
????
//增加
???? this.Insert(entity);
????
//更新
???? bool result = this.Update(entity);
????
//根据主键返回实体
???? entity = this.GetById(1);
????
//返回 行数
???? this.Count(new { ID = 1 });
????
//查询所有
???? IEnumerable list = this.GetAll();
???? IList
sort = new List();
???? sort.Add(
new Sort { PropertyName = "ID", Ascending = false });
????
//条件查询
???? list = this.GetList(new { ID = 1, Name = "123" }, sort);
????
//orm 拼接条件 查询
???? IList predList = new List();
???? predList.Add(Predicates.Field
(p => p.Name, Operator.Like, "不知道%"));
???? predList.Add(Predicates.Field
(p => p.ID, Operator.Eq, 1));
???? IPredicateGroup predGroup
= Predicates.Group(GroupOperator.And, predList.ToArray());
????
???? list
= this.GetList(predGroup);
????
//分页查询
???? long allRowsCount = 0;
????
this.GetPageList(1, 10, out allRowsCount, new { ID = 1 }, sort);

????

????

?


????

在说ORM之前,还是要说一下HY.DataAccess这个模块


????

?这个类是采用工厂模式创建DB连接的封装


????
?

????

这个模块是对数据访问提供的一个Helper的功能,里面包含了 各种DB的SqlHelper,分页。


????

SqlConnectionFactory.cs 这个类是采用工厂模式创建DB连接的封装,代码如下:


????
这个类是采用工厂模式创建DB连接的封装
????

????
using System;
????
using System.Collections.Generic;
????
using System.Configuration;
????
using System.Data;
????
namespace HY.DataAccess
????{
????
public enum DatabaseType
???? {
???? SqlServer,
???? MySql,
???? Oracle,
???? DB2
???? }
????
public class SqlConnectionFactory
???? {
????
public static IDbConnection CreateSqlConnection(DatabaseType dbType, string strKey)
???? {
???? IDbConnection connection
= null;
????
string strConn = ConfigurationManager.ConnectionStrings[strKey].ConnectionString;
????
switch (dbType)
???? {
????
case DatabaseType.SqlServer:
???? connection
= new System.Data.SqlClient.SqlConnection(strConn);
????
break;
????
case DatabaseType.MySql:
????
//connection = new MySql.Data.MySqlClient.MySqlConnection(strConn);
????
//break;
???? case DatabaseType.Oracle:
????
//connection = new Oracle.DataAccess.Client.OracleConnection(strConn);
???? connection = new System.Data.OracleClient.OracleConnection(strConn);
????
break;
????
case DatabaseType.DB2:
???? connection
= new System.Data.OleDb.OleDbConnection(strConn);
????
break;
???? }
????
return connection;
???? }
???? }
????}

????

????View Code

????

?ORM也不是万能的,比如做大数据的批量插入什么的,还是需要SqlHelper,加上有的人就喜欢DataTable或者DataSet。


????

所以SqlHelper作为根基,ORM作为辅助,万无一失啊。


????

?


????

下面说说ORM这块的实现方式。见下截图


????

这个类是采用工厂模式创建DB连接的封装


????

?


????

IDataServiceRepository.cs(提供业务层使用,里面的方法不支持传递sql,包含sql的语句最好还是放在数据层操作的好)


????
这个类是采用工厂模式创建DB连接的封装
????

????
using System.Collections.Generic;
????
using System.Data;
????
using DapperExtensions;
????
using HY.DataAccess;
????
namespace HY.ORM
????{
????
public interface IDataServiceRepository
???? {
???? IDBSession DBSession {
get; }
???? T GetById
(dynamic primaryId) where T : class;
???? IEnumerable
GetByIds(IList ids) where T : class;
???? IEnumerable
GetAll() where T : class;
????
int Count(object predicate, bool buffered = false) where T : class;
????
//lsit
???? IEnumerable GetList(object predicate = null, IList sort = null, bool buffered = false) where T : class;
???? IEnumerable
GetPageList(int pageIndex, int pageSize, out long allRowsCount, object predicate = null, IList sort = null, bool buffered = true) where T : class;
???? dynamic Insert
(T entity, IDbTransaction transaction = null) where T : class;
????
bool InsertBatch(IEnumerable entityList, IDbTransaction transaction = null) where T : class;
????
bool Update(T entity, IDbTransaction transaction = null) where T : class;
????
bool UpdateBatch(IEnumerable entityList, IDbTransaction transaction = null) where T : class;
????
int Delete(dynamic primaryId, IDbTransaction transaction = null) where T : class;
????
int DeleteList(object predicate, IDbTransaction transaction = null) where T : class;
????
bool DeleteBatch(IEnumerable ids, IDbTransaction transaction = null) where T : class;
???? }
????}

????

????View Code

????

?


????

?IDataRepository.cs(提供数据层使用,继承了上面的IDataServiceRepository,支持传入sql)


????
这个类是采用工厂模式创建DB连接的封装
????

????
using System;
????
using System.Collections.Generic;
????
using System.Data;
????
using Dapper;
????
using HY.DataAccess;
????
namespace HY.ORM
????{
????
public interface IDataRepository : IDataServiceRepository
???? {
???? IDBSession DBSession {
get; }
????
???? IEnumerable
Get(string sql, dynamic param = null, bool buffered = true) where T : class;
???? IEnumerable
Get(string sql, dynamic param = null, bool buffered = true);
???? IEnumerable
Get(string sql, Func map,
???? dynamic param
= null, IDbTransaction transaction = null, bool buffered = true,
????
string splitOn = "Id", int? commandTimeout = null);
???? IEnumerable
Get(string sql, Func map,
???? dynamic param
= null, IDbTransaction transaction = null, bool buffered = true,
????
string splitOn = "Id", int? commandTimeout = null);
???? SqlMapper.GridReader GetMultiple(
string sql, dynamic param = null, IDbTransaction transaction = null,
????
int? commandTimeout = null, CommandType? commandType = null);
????
???? IEnumerable
GetPage(int pageIndex, int pageSize, out long allRowsCount, string sql, dynamic param = null, string allRowsCountSql=null, dynamic allRowsCountParam = null, bool buffered = true) where T : class;
????
???? Int32 Execute(
string sql, dynamic param = null, IDbTransaction transaction = null);
???? }
????}

????

????View Code

????

?


????

?RepositoryServiceBase.cs(IDataServiceRepository的实现类)


????
这个类是采用工厂模式创建DB连接的封装
????

????
using System.Collections.Generic;
????
using System.Data;
????
using System.Linq;
????
using Dapper;
????
using DapperExtensions;
????
using HY.DataAccess;
????
namespace HY.ORM
????{
????
public class RepositoryServiceBase : IDataServiceRepository
???? {
????
public RepositoryServiceBase()
???? {
???? }
????
public RepositoryServiceBase(IDBSession dbSession)
???? {
???? DBSession
= dbSession;
???? }
????
public IDBSession DBSession { get; private set; }
????
public void SetDBSession(IDBSession dbSession)
???? {
???? DBSession
= dbSession;
???? }
????
///
???? /// 根据Id获取实体
????
///
???? ///
???? ///
???? ///
???? public T GetById(dynamic primaryId) where T : class
???? {
????
return DBSession.Connection.Get(primaryId as object, databaseType: DBSession.DatabaseType);
???? }
????
///
???? /// 根据多个Id获取多个实体
????
///
???? ///
???? ///
???? ///
???? public IEnumerable GetByIds(IList ids) where T : class
???? {
????
var tblName = string.Format("dbo.{0}", typeof(T).Name);
????
var idsin = string.Join(",", ids.ToArray());
????
var sql = "SELECT * FROM @table WHERE Id in (@ids)";
???? IEnumerable
dataList = DBSession.Connection.Query(sql, new { table = tblName, ids = idsin });
????
return dataList;
???? }
????
///
???? /// 获取全部数据集合
????
///
???? ///
???? ///
???? public IEnumerable GetAll() where T : class
???? {
????
return DBSession.Connection.GetList(databaseType: DBSession.DatabaseType);
???? }
????
///
???? /// 统计记录总数
????
///
???? ///
???? ///
???? ///
???? ///
???? public int Count(object predicate, bool buffered = false) where T : class
???? {
????
return DBSession.Connection.Count(predicate, databaseType: DBSession.DatabaseType);
???? }
????
///
???? /// 查询列表数据
????
///
???? ///
???? ///
???? ///
???? ///
???? ///
???? public IEnumerable GetList(object predicate = null, IList sort = null,
????
bool buffered = false) where T : class
???? {
????
return DBSession.Connection.GetList(predicate, sort, null, null, buffered, databaseType: DBSession.DatabaseType);
???? }
????
///
???? /// 分页
????
///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? public IEnumerable GetPageList(int pageIndex, int pageSize, out long allRowsCount,
????
object predicate = null, IList sort = null, bool buffered = true) where T : class
???? {
????
if (sort == null)
???? {
???? sort
= new List();
???? }
???? IEnumerable
entityList = DBSession.Connection.GetPage(predicate, sort, pageIndex, pageSize, null, null, buffered, databaseType: DBSession.DatabaseType);
???? allRowsCount
= DBSession.Connection.Count(predicate, databaseType: DBSession.DatabaseType);
????
return entityList;
???? }
????
///
???? /// 插入单条记录
????
///
???? ///
???? ///
???? ///
???? ///
???? public dynamic Insert(T entity, IDbTransaction transaction = null) where T : class
???? {
???? dynamic result
= DBSession.Connection.Insert(entity, transaction, databaseType: DBSession.DatabaseType);
????
return result;
???? }
????
///
???? /// 更新单条记录
????
///
???? ///
???? ///
???? ///
???? ///
???? public bool Update(T entity, IDbTransaction transaction = null) where T : class
???? {
????
bool isOk = DBSession.Connection.Update(entity, transaction, databaseType: DBSession.DatabaseType);
????
return isOk;
???? }
????
///
???? /// 删除单条记录
????
///
???? ///
???? ///
???? ///
???? ///
???? public int Delete(dynamic primaryId, IDbTransaction transaction = null) where T : class
???? {
????
var entity = GetById(primaryId);
????
var obj = entity as T;
????
int isOk = DBSession.Connection.Delete(obj, databaseType: DBSession.DatabaseType);
????
return isOk;
???? }
????
///
???? /// 删除单条记录
????
///
???? ///
???? ///
???? ///
???? ///
???? public int DeleteList(object predicate = null, IDbTransaction transaction = null) where T : class
???? {
????
return DBSession.Connection.Delete(predicate, transaction, databaseType: DBSession.DatabaseType);
???? }
????
///
???? /// 批量插入功能
????
///
???? ///
???? ///
???? ///
???? public bool InsertBatch(IEnumerable entityList, IDbTransaction transaction = null) where T : class
???? {
????
bool isOk = false;
????
foreach (var item in entityList)
???? {
???? Insert
(item, transaction);
???? }
???? isOk
= true;
????
return isOk;
???? }
????
///
???? /// 批量更新()
????
///
???? ///
???? ///
???? ///
???? ///
???? public bool UpdateBatch(IEnumerable entityList, IDbTransaction transaction = null) where T : class
???? {
????
bool isOk = false;
????
foreach (var item in entityList)
???? {
???? Update
(item, transaction);
???? }
???? isOk
= true;
????
return isOk;
???? }
????
///
???? /// 批量删除
????
///
???? ///
???? ///
???? ///
???? ///
???? public bool DeleteBatch(IEnumerable ids, IDbTransaction transaction = null) where T : class
???? {
????
bool isOk = false;
????
foreach (var id in ids)
???? {
???? Delete
(id, transaction);
???? }
???? isOk
= true;
????
return isOk;
???? }
???? }
????}

????

????View Code

????

?


????

RepositoryBase.cs(IDataRepository的实现类)


????
这个类是采用工厂模式创建DB连接的封装
????

????
using System;
????
using System.Collections.Generic;
????
using System.Data;
????
using Dapper;
????
using DapperExtensions;
????
using HY.DataAccess;
????
namespace HY.ORM
????{
????
///
???? /// Repository基类
????
///
???? public class RepositoryBase : RepositoryServiceBase, IDataRepository
???? {
????
public RepositoryBase()
???? {
???? }
????
public new void SetDBSession(IDBSession dbSession)
???? {
????
base.SetDBSession(dbSession);
???? }
????
public RepositoryBase(IDBSession dbSession)
???? :
base(dbSession)
???? {
???? }
????
///
???? /// 根据条件筛选出数据集合
????
///
???? ///
???? ///
???? ///
???? ///
???? ///
???? public IEnumerable Get(string sql, dynamic param = null, bool buffered = true) where T : class
???? {
????
return DBSession.Connection.Query(sql, param as object, DBSession.Transaction, buffered);
???? }
????
///
???? /// 根据条件筛选数据集合
????
///
???? ///
???? ///
???? ///
???? ///
???? public IEnumerable Get(string sql, dynamic param = null, bool buffered = true)
???? {
????
return DBSession.Connection.Query(sql, param as object, DBSession.Transaction, buffered);
???? }
????
///
???? /// 分页查询
????
///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? public IEnumerable GetPage(int pageIndex, int pageSize, out long allRowsCount, string sql, dynamic param = null, string allRowsCountSql = null, dynamic allRowsCountParam = null, bool buffered = true) where T : class
???? {
???? IEnumerable
entityList = DBSession.Connection.GetPage(pageIndex, pageSize, out allRowsCount, sql, param as object, allRowsCountSql, null, null, buffered, databaseType: DBSession.DatabaseType);
????
return entityList;
???? }
????
///
???? /// 根据表达式筛选
????
///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? public IEnumerable Get(string sql, Func map,
???? dynamic param
= null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id",
????
int? commandTimeout = null)
???? {
????
return DBSession.Connection.Query(sql, map, param as object, transaction, buffered, splitOn);
???? }
????
///
???? /// 根据表达式筛选
????
///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? public IEnumerable Get(string sql, Func map,
???? dynamic param
= null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id",
????
int? commandTimeout = null)
???? {
????
return DBSession.Connection.Query(sql, map, param as object, transaction, buffered, splitOn);
???? }
????
///
???? /// 获取多实体集合
????
///
???? ///
???? ///
???? ///
???? ///
???? ///
???? ///
???? public SqlMapper.GridReader GetMultiple(string sql, dynamic param = null, IDbTransaction transaction = null,
????
int? commandTimeout = null, CommandType? commandType = null)
???? {
????
return DBSession.Connection.QueryMultiple(sql, param as object, transaction, commandTimeout, commandType);
???? }
????
///
???? /// 执行sql操作
????
///
???? ///
???? ///
???? ///
???? public int Execute(string sql, dynamic param = null, IDbTransaction transaction = null)
???? {
????
return DBSession.Connection.Execute(sql, param as object, transaction);
???? }
???? }
????}

????

????View Code

????

?


????

?


????

?


????

?


????

?


????

未完待续……


????

?


????

?


????

?


????

?


????

?


????

相关文章:


????

搭建一套自己实用的.net架构(1)【概述】


????

搭建一套自己实用的.net架构(2)【日志模块-log4net】


????

搭建一套自己实用的.net架构(3)【ORM-Dapper+DapperExtensions】


????

?

2楼要神么自行车
支持,期待继续写下去
1楼阿水
顶一记,一致比较关注Dapper!
Re: 咖啡不苦不舒服
@阿水,感谢支持
?