使用复合设计模式扩展持久化的CURD,Select能力
大家可能会经常遇到接口需要经常增加新的方法和实现，可是我们原则上是不建议平凡的增加修改删除接口方法，熟不知这样使用接口是不是正确的接口用法，比如我见到很多的项目分层都是IDAL，DAL，IBLL，BLL，使用了2层接口，名义上是数据隔离和业务隔离，但是会使代码难调试，难维护，有可能代码写的不好或者需求变更造成IBLL,BLL,IDAL,DAL的全部修改，这真的是代码架构之疡。
基于上述观点，我i重构DAL来废除IDAL，IBLL造成的架构臃肿
使用模板模式创建Get和GetList方法，使他具有分页和多表查询，事务查询的能力
使用命令模式创建AddWhere方法，使他具有Where族的能力，能处理复杂SQL查询的能力
通过此模式，程序员可以在BLL中自由组合Where,Order,Gounp by不需要写一个方法，再也不会在DAL中出现GetByName，GetListByType这样的方法。
如需迁移redis和mongodb，建议重新搞一套redis的Base
代码大致如下
AbsSelectBase.cs
public class AbsSelectBase<T> where T : BaseModel { private List<absWhere> lWhere { get; set; } public PageModel page { get; set; } protected string Table { get; set; } public RBase1() { page = new PageModel(); lWhere = new List<absWhere>(); Init(); } protected virtual void Init() { } public virtual List<T> GetList(MySqlTransaction tran = null) { string where; var lpar = GetWhere(out where); var list = new List<T>(); if (page.Order_field.IsNull()) { page.Order_field = "Id desc"; } if (page.Current_Page.HasValue) { list = GetPageList("", page.Order_field, Table, page, where, "", lpar, tran); } else { list = Query("select * from " + Table + " where " + where, page.Order_field, lpar, tran); } return list; } public void AddWhere(absWhere where) { lWhere.Add(where); } public T Get(MySqlTransaction tran = null) { page.Current_Page = 1; page.Page_Size = 1; var entity = GetList(tran).FirstOrDefault(); return entity; } public virtual DynamicParameters GetWhere(out string where) { DynamicParameters lpar = new DynamicParameters(); where = " IsValid=1 "; foreach (var ewhere in lWhere) { where += ewhere.CreateWhere(lpar); } lWhere.clear(); lWhere=null; return lpar; } public object GetCount(MySqlTransaction tran = null) { string where = ""; var lpar = GetWhere(out where); var res = SqlConnections.Sqlconnection.Query<int>("select count(1) from " + Table + " where " + where, lpar, tran); return res; } protected virtual List<T> GetPageList(string Field, string Order, string Table, PageModel model, string Where, string bigOrderBy = null, object param = null, MySqlTransaction sqlTransation = null) { var res = SqlConnections.Sqlconnection.GetPageList<T>(Field, Order, Table, model, Where, bigOrderBy, param, sqlTransation).ToList(); return res; } }ConcreteSelectA.cs
public class RScoreRecordSelect : RBase1<ScoreRecord> { public RScoreRecordSelect() { } }ConcreteSelectB.cs，多表
/// <summary> /// ProductBuy inner join User /// </summary> public class RProBuyUserSelect : RBase1<ProductBuy> { public override List<ProductBuy> GetList(MySql.Data.MySqlClient.MySqlTransaction tran = null) { string where; var lpar = GetWhere(out where); var list = new List<ProductBuy>(); string table = @" User inner join ProductBuy on User.ID=ProductBuy.UserID "; if (page.Order_field.IsNull()) { page.Order_field = "ProductBuy.Id desc"; } if (page.Current_Page.HasValue) { list = GetPageList("", page.Order_field, table, page, where, "", lpar, tran); } else { list = Query("select ProductBuy.* from " + table + " where " + where, page.Order_field, lpar, tran); } return list; } public override CZCore.Data.DynamicParameters GetWhere(out string where) { DynamicParameters lpar = new DynamicParameters(); where = " User.IsValid=1 and ProductBuy.IsValid=1 "; foreach (var ewhere in lWhere) { where += ewhere.CreateWhere(lpar); } return lpar; } public enum EnumOrder { /// <summary> /// /// </summary> [Category("UserAct.BuyCount")] [Description("")] _0, /// <summary> /// /// </summary> [Category("UserAct.BuyMoney")] [Description("")] _1, /// <summary> /// /// </summary> [Category("UserAct.CashScore")] [Description("")] _2, /// <summary> /// /// </summary> [Category("DBB.Money")] [Description("")] _3, /// <summary> /// /// </summary> [Category("DBB.Score")] [Description("")] _4 } }absWhere.cs
public abstract class absWhere { private string _field; public string field { get { return _field; } set { _field = value; } } public object value { get; set; } private string _par; public string par { get { if (_par.IsNull()) { _par = field.Replace(".",""); } return _par; } set { _par = value; } } public abstract string CreateWhere(DynamicParameters lpar); }WhereAndIn.cs
public class WhereAndIn : absWhere { public override string CreateWhere(CZCore.Data.DynamicParameters lpar) { string sql=""; if (value.IsNull() == false) { sql = " and "+field+" in @"+par+" "; lpar.Add(par, value); } return sql; } }WhereAndLike.cs
public class WhereAndLike : absWhere { public override string CreateWhere(CZCore.Data.DynamicParameters lpar) { string sql=""; if (value.IsNull() == false) { sql = " and "+field+" like @"+par+" "; lpar.Add(par, "%"+value+"%"); } return sql; } }单表查询
多表查询
简单的处理增删改查主键，等于Entity属性的简单查询不分页功能，并在上层提供一个总的facade门面
AbsBase.cs
public class RBase<T> where T : BaseModel { public List<T> GetAll(MySqlTransaction tran = null) { var list = SqlConnections.Sqlconnection.GetAll<T>(tran); return list; } public T GetById(object id, MySqlTransaction tran = null) { var res = SqlConnections.Sqlconnection.GetById<T>(id, tran); return res; } public UInt64 Insert(T T, MySqlTransaction tran = null, MySqlConnection _conn = null, bool IsAuto = true) { var res = SqlConnections.Sqlconnection.Insert<T>(T, tran, IsAuto); return res; } public void Insert(List<T> list, MySqlTransaction tran = null, MySqlConnection _conn = null) { SqlConnections.Sqlconnection.Insert<T>(list, tran); } public void Update(T T, MySqlTransaction tran = null, MySqlConnection _conn = null) { SqlConnections.Sqlconnection.Update<T>(T, tran); } public void Update(List<T> list, MySqlTransaction tran = null, MySqlConnection _conn = null) { SqlConnections.Sqlconnection.Update<T>(list, tran); } public List<T> Search(T t, MySqlTransaction tran = null, MySqlConnection _conn = null) { var res = SqlConnections.Sqlconnection.Search<T>(t, tran); return res; } public bool Delete(object id, bool isDelete = false, MySqlTransaction tran = null, MySqlConnection _conn = null) { var res = SqlConnections.Sqlconnection.Delete<T>(id, tran, isDelete: isDelete); return res; } public void DeleteBySearch(T t, bool isDelete = false, MySqlTransaction tran = null, MySqlConnection _conn = null) { SqlConnections.Sqlconnection.DeleteBySearch<T>(t, tran, isDelete: isDelete); } public RBase() { Init(); } public virtual void Init() { } public void TruncateTable(MySqlTransaction sqlTransation = null) { bool result = Execute( string.Format( "Truncate Table {0}", "`" + typeof(T).Name.ToLower() + "`" ), null, sqlTransation) > 0; } }Concrete1.cs
public class RScoreRecord:RBase<ScoreRecord> { }Facade.cs
Model层封装Entity层,并懒加载Entity数据，只加载关联id一级数据
MUser.cs
public class MUser { private User eUser; public MUser(User _eUser) { eUser=_eUser; } private UserAct eUserAct; public UserAct GetEUserAct(MySql.Data.MySqlClient.MySqlTransaction ts = null) { if (eUserAct == null) { eUserAct = RCommon.rUserAct.GetById(eUser.Id,ts); } return eUserAct; } private DBB eDBB; public DBB GetDBB(MySql.Data.MySqlClient.MySqlTransaction ts=null) { if (eDBB == null) { eDBB = RCommon.rdbb.GetById(eUser.Id,ts); } return eDBB; } }
