dapper实现的简单架构
1.首先建立空白的解决方案，然后分别创建TestData.Business（业务逻辑类类库）引入Dapper和DapperExtensions两个类库，可以再网上下载下。TestData.Common（工具类类库）TestData.Entity（实体类类库）TestData.Filter（查询条件类库）TextData.Web（web应用程序）
在TextData.Web中引入mvc的架构和easyui最终情况如图所示：
2.创建数据库中的实体，并创建公用的实体接口：IDataEntity 它里面放着每个实体中都存在的属性比如：主键、修改人、修改时间等
实体类生成小技巧：用ef生成codefirst然后删除他的映射文件和appconfig然后所有的类继承IDataEntity接口即可。
在实体类中添加mapping文件夹，写入基类CustomMapping代码如下：
using DapperExtensions.Mapper; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace TestData.Entity.Mapping { public class CustomMapping<T> : ClassMapper<T> where T : class,IDataEntity { public CustomMapping() { Map(x => x.Id).ReadOnly(); } } }
注：mapping的作用（过滤不想读取的字段或设置一些字段在数据库中的含义）
在继承CustomMapping的时候除了要写一些字段的过滤意外在最后一定要写上AutoMap();他的作用是提交所有的字段
3.在TestData.Filter中添加查询条件的基类，因为用到了easyui他的表格要接收的东西包含rows、total、所以封装了一个基类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace TestData.Filter { /// <summary> /// 搜索条件的基类包含了页数 行数 派讯 /// </summary> public class BaseFilter { private int _page = 1; public int page { set { _page = value; } get { return _page; } } public int _rows = 10; public int rows { set { _rows = value; } get { return _rows; } } private string _sortName = "EditorDate"; public string sort { get { return _sortName; } set { _sortName = value; } } } }
只要在为每个实体写搜索条件的时候继承这个类就好咯
4.封装业务逻辑
1）添加链接对象工厂
using System; using System.Collections.Generic; using System.Configuration; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace TestData.Business { public class ConnectionFactory { private static readonly string ConnString = ConfigurationManager.ConnectionStrings["ShopEntity"].ConnectionString; private static object _obj = new object(); public static SqlConnection Connection { get { SqlConnection connection = null; if (connection == null) { lock (_obj) { if (connection == null) { connection = new SqlConnection(ConnString); } } } return connection; } } } }
2）编写业务逻辑的基类
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Net.Cache; using System.Text; using Dapper; using DapperExtensions; using TestData.Entity; using TestData.Business; namespace TestData.Business { /// <summary> /// 业务逻辑的基类 包含了一些简单的操作 /// </summary> /// <typeparam name="T"></typeparam> public abstract class BaseManager<T> where T : class, IDataEntity { /// <summary> /// 查询所有 /// </summary> /// <returns></returns> public IEnumerable<T> GetAll() { using (var conn = ConnectionFactory.Connection) { return conn.GetList<T>(); } } /// <summary> /// 根绝sql查询 /// </summary> /// <param name="sql">sql</param> /// <param name="parameters">参数列表</param> /// <returns></returns> public IEnumerable<T> SelectCommond(string sql, object parameters = null) { using (var conn = ConnectionFactory.Connection) { return conn.Query<T>(sql, parameters); } } /// <summary> /// 根据表明查询 /// </summary> /// <param name="name"></param> /// <returns></returns> public IEnumerable<T> GetAll(string name) { using (var conn = ConnectionFactory.Connection) { return conn.Query<T>(string.Format("select * from {0}", name)); } } /// <summary> /// 根据编号查询 /// </summary> /// <param name="id"></param> /// <returns></returns> public T GetById(int? id) { if (id == null) return default(T); using (var conn = ConnectionFactory.Connection) { return conn.Get<T>(id.Value); } } /// <summary> /// 修改实体 /// </summary> /// <param name="t">实体对象</param> /// <returns></returns> public bool Update(T t) { using (var conn = ConnectionFactory.Connection) { t.EditorDate = DateTime.Now; return conn.Update(t); } } /// <summary> /// 得到数量 /// </summary> /// <returns></returns> public int GetCount() { using (var conn = ConnectionFactory.Connection) { return conn.Count<T>(null); } } /// <summary> /// 分页 /// </summary> /// <param name="predicate"></param> /// <param name="pageindex"></param> /// <param name="pageSize"></param> /// <returns></returns> public Tuple<int, IEnumerable<T>> GetPaged(object predicate, int pageindex, int pageSize) { using (var conn = ConnectionFactory.Connection) { var sort = new List<ISort> { Predicates.Sort<T>(p=>p.EditorDate) }; var total = conn.Count<T>(predicate); return new Tuple<int, IEnumerable<T>>(total, conn.GetPage<T>(predicate, sort, pageindex, pageSize).ToList()); } } /// <summary> /// 添加 /// </summary> /// <param name="t">实体对象</param> /// <returns></returns> public bool Insert(T t) { t.EditorDate = DateTime.Now; return this.Add(t, false) == t.Id; } /// <summary> /// 添加实体集合 /// </summary> /// <param name="list"></param> /// <returns></returns> public bool Insert(List<T> list) { using (var conn = ConnectionFactory.Connection) { list.ForEach(p => p.EditorDate = DateTime.Now); return conn.Insert(list); } } /// <summary> /// 添加实体 /// </summary> /// <param name="t">实体对象</param> /// <param name="isAutoGenId"></param> /// <returns></returns> public int Add(T t, bool isAutoGenId = true) { using (var conn = ConnectionFactory.Connection) { //var maxindex = conn.Query<int?>(string.Format("select max(indexs) from {0}", typeof(T).Name)).FirstOrDefault() ?? 0; //t.Indexs = maxindex + 1; return conn.Insert(t, isGenId: isAutoGenId); } } /// <summary> /// 根据编号删除 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool Delete(int? id) { var obj = this.GetById(id); if (obj == null) return false; return this.Update(obj); } /// <summary> /// 根据编号修改 /// </summary> /// <param name="id"></param> /// <param name="mark"></param> /// <returns></returns> public bool UpdataStatus(int? id) { var obj = this.GetById(id); if (obj == null) return false; return this.Update(obj); } /// <summary> /// 根据外键得到数据 /// </summary> /// <param name="foreignKeyName">外键名称</param> /// <param name="foreignKeyValue">外键的值</param> /// <returns></returns> public IEnumerable<T> GetByForeignKey(string foreignKeyName, Guid foreignKeyValue) { using (var conn = ConnectionFactory.Connection) { return conn.Query<T>(string.Format("select * from {0} where {1}=@value", typeof(T).Name, foreignKeyName), new { value = foreignKeyValue }); } } /// <summary> /// 根据列查询 /// </summary> /// <param name="fieldName">列名称</param> /// <param name="fieldValue">列的值</param> /// <returns></returns> public IEnumerable<T> GetByField(string fieldName, dynamic fieldValue) { using (var conn = ConnectionFactory.Connection) { return conn.Query<T>(string.Format("select * from {0} where {1}=@value", typeof(T).Name, fieldName), new { value = fieldValue }); } } /// <summary> /// lxh 根据某列查询的方法--带排序 /// </summary> /// <param name="fieldName">查询列名</param> /// <param name="fieldValue">条件内容</param> /// <param name="sortFieldName">排序列名</param> /// <returns></returns> public IEnumerable<T> GetByField(string fieldName, dynamic fieldValue, string sortFieldName) { using (var conn = ConnectionFactory.Connection) { return conn.Query<T>(string.Format("select * from {0} where {1}=@value order by {2}", typeof(T).Name, fieldName, sortFieldName), new { value = fieldValue }); } } /// <summary> /// lxh 获取排序号的方法 /// </summary> /// <returns></returns> public int GetNextSequence(T t) { using (var conn = ConnectionFactory.Connection) { return conn.Query<int>(string.Format("select isnull(max(Sequence),0)+1 from {0}", typeof(T).Name)).FirstOrDefault(); } } /// <summary> /// 存储过程 /// </summary> /// <param name="procName"></param> /// <param name="obj"></param> /// <returns></returns> public List<dynamic> SelectProc(string procName, object obj = null) { using (var conn = ConnectionFactory.Connection) { return conn.Query(procName, obj, commandType: CommandType.StoredProcedure).ToList(); } } } }
3）编写一些特殊的方法、为特殊的sql所使用
using DapperExtensions; using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Linq.Expressions; using System.Text; using System.Threading.Tasks; using TestData.Entity; using TestData.Filter; using Dapper; namespace TestData.Business { public static class DataExtensions { public static bool ExecuteTransaction(this SqlConnection conn, Action<SqlConnection, SqlTransaction> action) { var transaction = conn.BeginTransaction(); try { action(conn, transaction); transaction.Commit(); return true; } catch (Exception) { transaction.Rollback(); return false; } } public static PredicateGroup GetPredicate<T>(this BaseFilter filter, int? id = 0) where T : class ,IDataEntity { var predicate = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() }; if (id != 0) predicate.Predicates.Add(Predicates.Field<T>(p => p.Id, Operator.Eq, Convert.ToInt32(id))); return predicate; } public static List<ISort> GetSorts<T>(this BaseFilter filter) where T : class { var expressionparam = Expression.Parameter(typeof(T), "p"); var bodyExp = Expression.Convert(Expression.Property(expressionparam, filter.sort), typeof(Object)); var expression = Expression.Lambda<Func<T, object>>(bodyExp, expressionparam); return new List<ISort> { Predicates.Sort(expression) }; } public static int Total<T>(this IEnumerable<T> list) where T : class,IDataEntity { using (var conn = ConnectionFactory.Connection) { return conn.Count<T>(Predicates.Field<T>(p => p.Id, Operator.Eq, null)); } } public static int Total<T>(object predicate) where T : class { using (var conn = ConnectionFactory.Connection) { return conn.Count<T>(predicate); } } public static IDataEntity GetSingle(IDataEntity entity) { using (var conn = ConnectionFactory.Connection) { return conn.Query<IDataEntity>(string.Format("select * from {0} where id=@ii", entity.GetType().Name), new { ii = entity.Id }).FirstOrDefault(); } } public static PageList<T> ToPageList<T>(this IEnumerable<T> iEnumerable, object predicate) where T : class { return new PageList<T> { DataList = iEnumerable.ToList(), Total = Total<T>(predicate) }; } } public class PageList<T> { public List<T> DataList { set; get; } public int Total { set; get; } } }
好啦这样一个简单的架构就实现啦！！！
