/// <summary>
/// 根据当前的表达式，获取Command
/// </summary>
private DbCommand CreateCurrentCommand()
{
var traslater = new ExpressionTranslater(this.DbProvider, this.Expression);
traslater.Translate();
var command = DbProvider.CreateCommand(traslater.CommandText, traslater.Params.ToArray());
return(command);
}
/// <summary>
/// 对翻译的结果进行调整
/// 1,N-M行模式、跳过N行、前M行 处理(可结合GetSQLSelectTopClip)
/// 2,SQL格式化
/// </summary>
/// <param name="translater"></param>
/// <returns></returns>
public virtual void AdjustTranslater(ExpressionTranslater translater)
{
var exp = translater.Expression;
if (exp.IsPagable)
{
AdjustPagable(translater);
}
}
/// <summary>
/// 获取Sq语句中 "select" 和 字段之间 的部分(如果需要)
/// </summary>
/// <returns></returns>
public virtual string GetSQLSelectTopClip(ExpressionTranslater translater)
{
StringBuilder str = new StringBuilder();
var exp = translater.Expression;
if (exp.IsPagable)
{
str.Append(ExpressionTranslater.SQL_TOP + exp.EndIndex + " ");
}
else if (exp.IsTop)
{
str.Append(ExpressionTranslater.SQL_TOP + exp.EndIndex + " ");
}
return(str.ToString());
}
/// <summary>
/// 对翻译体进行 翻译前调整
/// 主要是针对表达式做调整
/// </summary>
/// <param name="translater"></param>
public virtual void PrepareTranslater(ExpressionTranslater translater)
{
var exp = translater.Expression;
if (exp.IsPagable)
{
exp.IsDistinct = false;
exp.GroupBy = null;
exp.IsOrderBy = false;
}
else if (exp.IsSkip)
{
PrepareSkip(translater);
}
}
/// <summary>
/// N-M行 针对可分页进行调整
/// 方案：
/// 1，根据条件和排序 检索出全部前M行数据(在GetSQLSelectTopClip已经完成了这一步)
/// 2，对这M行数据反转(排序反转)
/// 3，对反转完的数据，取前 M-N+1 行
/// 4，取完数据以后，再次反转
/// 如：select * from (select top [M-N+1] * from (select top [M] * from 表 [order by id desc]) [order by id asc]) [order by id desc]
/// 有一个条件：必须存在排序，如果不存在创建一个
/// </summary>
/// <param name="translater"></param>
/// <returns></returns>
protected virtual void AdjustPagable(ExpressionTranslater translater)
{
var oldSQL = translater.CommandText;
var exp = translater.Expression;
var sql = SQL_PAGING.ToString();
IList <OrderByExpression> orderby = exp.IsOrderBy ? exp.OrderByList : new List <OrderByExpression>()
{
new OrderByExpression(exp.From.GetIdentityField(), OrderByDirection.Asc)
};
sql = sql.Replace("{M_N_1}", (exp.EndIndex.Value - exp.StartIndex.Value + 1).ToString());
sql = sql.Replace("{OLDORDER}", exp.IsOrderBy ? "" : translater.TranslateOrderyByList(orderby, false));
sql = sql.Replace("{REVORDER}", translater.TranslateOrderyByList(orderby, true));
sql = sql.Replace("{ORDER}", translater.TranslateOrderyByList(orderby, false));
sql = sql.Replace("{OLDSQL}", oldSQL);
translater.SetCommandText(sql);
}
/// <summary>
/// 跳过N行
/// 实现方案： 根据排序和条件检索出全部数据
/// 再加入一个条件：id 大于/或小于(需要根据排序来判断) 前N行记录ID的最大/最小值
/// </summary>
/// <param name="translater"></param>
protected virtual void PrepareSkip(ExpressionTranslater translater)
{
var exp = translater.Expression;
var idField = exp.From.GetIdentityField();
if ((idField as object) == null)
{
throw new NotImplementedException(TextResource.AutoKeyToSearchAllow);
}
var IsIdentityFieldDesc = exp.IsIdentityFieldDesc;
//子查询
var subTableName = "subtable";
var subId = idField.SetTableName(subTableName);
var subTable = DataHelper.Clone(exp.From) as Entity;
subTable.SetAliasName(subTableName);
var subExp = new EntityQueryExpression(subTable);
//前N行记录ID的最大/最小值
if (IsIdentityFieldDesc)
{
subExp.Select = new SelectExpression(new Field[] { subId.Min() });
}
else
{
subExp.Select = new SelectExpression(new Field[] { subId.Max() });
}
//复制条件与排序
subExp.Where = exp.Where.Clone();
subExp.Where.SetTable(subTableName);
subExp.OrderByList = exp.OrderByList.Clone().SetTable(subTableName);
//再加入一个条件：id 大于/或小于(需要根据排序来判断) 前N行记录ID的最大/最小值
var newWhere = WhereExpression.Create(exp.From.GetIdentityField(), subExp,
IsIdentityFieldDesc ? QueryOperator.Less : QueryOperator.Greater);
exp.Where = WhereExpression.Create(exp.Where, newWhere, QueryOperator.And);
}