protected string GetSql(IFilterable <T> filterable, DbCommand cmd)
{
var expr = filterable.QueryExpression;
var wOpts = new WhereOpts();
string where = null;
string orderBy = null;
if (filterable.QueryExpression != null)
{
where = this.SqlWhere(expr, cmd, wOpts);
}
if (filterable.AscendingExpression != null)
{
orderBy = this.Sql.DbTrait.SqlFieldname(this.SqlWhere((filterable.AscendingExpression.Body as UnaryExpression).Operand, cmd, wOpts)) + " ASC";
}
else if (filterable.DescendingExpression != null)
{
orderBy = this.Sql.DbTrait.SqlFieldname(this.SqlWhere((filterable.DescendingExpression.Body as UnaryExpression).Operand, cmd, wOpts)) + " DESC";
}
if (where == null && orderBy == null && filterable.SkipCount < -0 && filterable.TakeCount <= 0)
{
return(this.Sql.SqlTableAndFields);
}
return(this.Sql.DbTrait.SqlPaginate(
this.Sql.Tablename(true),
this.Sql.SqlFields,
where,
orderBy,
filterable.TakeCount,
filterable.SkipCount
));
}
protected string GetSql(Expression expr, DbCommand cmd)
{
if (_tbAndFields == null)
{
lock (this)
{
if (_tbAndFields == null)
{
_tbAndFields = $"SELECT COUNT(*) FROM {this.Sql.Tablename(true)} ";
_tbAndFieldsWithWhere = _tbAndFields + " WHERE ";
}
}
}
if (expr != null)
{
WhereOpts wOpts = null;
var cond = this.SqlWhere(expr, cmd, wOpts);
if (string.IsNullOrEmpty(cond))
{
return(_tbAndFields);
}
var sql = _tbAndFieldsWithWhere + cond;
return(sql);
}
else
{
return(_tbAndFields);
}
}
//public int noSeed { get; private set; }
public string SqlWhere(Expression exp, DbCommand cmd, WhereOpts opts = null)
{
if (exp == null)
{
return(null);
}
if (opts == null)
{
opts = new WhereOpts();
}
BinaryExpression bExp = null;
switch (exp.NodeType)
{
case ExpressionType.Lambda:
return(SqlWhere(((LambdaExpression)exp).Body, cmd, opts));
case ExpressionType.AndAlso:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " AND "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.OrElse:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " OR "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.Equal:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " = "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.NotEqual:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " <> "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.GreaterThan:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " > "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.GreaterThanOrEqual:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " >= "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.LessThan:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " < "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.LessThanOrEqual:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " <= "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.Add:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " + "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.Decrement:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " - "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.Multiply:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " * "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.Divide:
bExp = exp as BinaryExpression;
return("(" + SqlWhere(bExp.Left, cmd, opts)
+ " / "
+ SqlWhere(bExp.Right, cmd, opts)
+ ")");
case ExpressionType.MemberAccess:
var m = exp as MemberExpression;
var member = this.Sql.AllowedProps.Values.FirstOrDefault(p => p.Name == m.Member.Name);
opts.LastProp = member;
return(this.Sql.DbTrait.SqlFieldname(member.Field.Name));
case ExpressionType.Convert:
var cexp = exp as UnaryExpression;
return(SqlWhere(cexp.Operand, cmd, opts));
case ExpressionType.Call:
var method = exp as MethodCallExpression;
switch (method.Method.Name)
{
case "Contains":
opts.ValueEmbeded = true;
return(SqlWhere(method.Object, cmd, opts)
+ " LIKE ('%"
+ SqlWhere(method.Arguments[0], cmd, opts)
+ "%')");
case "StartsWith":
opts.ValueEmbeded = true;
return(SqlWhere(method.Object, cmd, opts)
+ " LIKE ('"
+ SqlWhere(method.Arguments[0], cmd, opts)
+ "%')");
case "EndsWith":
opts.ValueEmbeded = true;
return(SqlWhere(method.Object, cmd, opts)
+ " LIKE ('%"
+ SqlWhere(method.Arguments[0], cmd, opts)
+ "')");
default:
throw new InvalidExpressionException("无法支持的表达式");
}
case ExpressionType.Constant:
var cst = (exp as ConstantExpression);
if (opts.ValueEmbeded)
{
opts.ValueEmbeded = false;
return(cst.Value.ToString().Replace("'", "''"));
}
var par = cmd.CreateParameter();
par.ParameterName = "@_WP" + (++opts.NoSeed).ToString();
par.Value = cst.Value;
par.DbType = opts.LastProp.Field.DbType;
cmd.Parameters.Add(par);
return(par.ParameterName);
default:
throw new InvalidExpressionException("无法支持的表达式");
}
}