大家可能会经常遇到接口需要经常增加新的方法和实现，可是我们原则上是不建议平凡的增加修改删除接口方法，熟不知这样使用接口是不是正确的接口用法，比如我见到很多的项目分层都是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 = ;
page.Page_Size = ;
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) > ;
} }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;
} }