/// <inheritdoc />
/// <summary>
/// 执行解析
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public UpdateEntityWhereExpression(object obj, ISqlDialect dialect)
{
_sqlCmd = new StringBuilder(100);
Param = new TheDynamicParameters();
_obj = obj;
Dialect = dialect;
}
//public SqlProvider<T> FormatToList(LambdaExpression[] selector)
//{
// var selectSql = ResolveExpression.Instance(Dialect).ResolveSelect(typeof(T), Context.QuerySet.TopNum, false, selector);
// var fromTableSql = FormatTableName();
// var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
// var whereSql = whereParams.SqlCmd;
// Params = whereParams.Param;
// var orderbySql = ResolveExpression.Instance(Dialect).ResolveOrderBy(Context.QuerySet.OrderbyExpressionList);
// var limitSql = ResolveExpression.Instance(Dialect).ResolveLimit(Context.QuerySet.TopNum);
// SqlString = $"{selectSql} {fromTableSql} {whereSql} {orderbySql} {limitSql}";
// return this;
//}
public SqlProvider <T> FormatToListZhanglei(Type type, IFieldAnyExpression fieldAnyExpression = null)
{
var selectSql = ResolveExpression.Instance(Dialect).ResolveSelect(type, Context.QuerySet.TopNum, Context.QuerySet.IsDistinct, Context.QuerySet.SelectExpression);
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var orderbySql = ResolveExpression.Instance(Dialect).ResolveOrderBy(Context.QuerySet.OrderbyExpressionList);
var limitSql = ResolveExpression.Instance(Dialect).ResolveLimit(Context.QuerySet.TopNum);
if (fieldAnyExpression != null)
{
var selectDistinctSql = ResolveExpression.Instance(Dialect).ResolveSelect(type, Context.QuerySet.TopNum, true, Context.QuerySet.SelectExpression);
var di = fieldAnyExpression.WhereParam.ToDictionary();
foreach (var i in di)
{
Params.Add(i.Key, i.Value);
}
SqlString = $"{selectDistinctSql} from ({selectSql} ,jsonb_array_elements({fieldAnyExpression.ListFieldName}) as \"{ResolveExpression.FieldAnyColumnName}\" {fromTableSql} {whereSql} {orderbySql}) as {ResolveExpression.FieldAnyTableName} where {fieldAnyExpression.WhereClause} {limitSql}";
}
else
{
SqlString = $"{selectSql} {fromTableSql} {whereSql} {orderbySql} {limitSql}";
}
return(this);
}
private SqlProvider <T> FormatGetDo(IWhereExpression whereParams, IFieldAnyExpression fieldAnyExpression)
{
var selectSql = ResolveExpression.Instance(Dialect).ResolveSelect(typeof(T).GetPropertiesInDb(true), Context.QuerySet.SelectExpression, 1, Context.QuerySet.IsDistinct);
var fromTableSql = FormatTableName();
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var orderbySql = ResolveExpression.Instance(Dialect).ResolveOrderBy(Context.QuerySet.OrderbyExpressionList);
var limitSql = ResolveExpression.Instance(Dialect).ResolveLimit(1);
if (fieldAnyExpression != null)
{
string selectDistinctSql = ResolveExpression.Instance(Dialect).ResolveSelect(typeof(T).GetPropertiesInDb(true), Context.QuerySet.SelectExpression, 1, true);
var di = fieldAnyExpression.WhereParam.ToDictionary();
foreach (var i in di)
{
Params.Add(i.Key, i.Value);
}
SqlString = $"{selectDistinctSql} from ({selectSql} ,jsonb_array_elements({fieldAnyExpression.ListFieldName}) as \"{ResolveExpression.FieldAnyColumnName}\" {fromTableSql} {whereSql} {orderbySql}) as {ResolveExpression.FieldAnyTableName} where {fieldAnyExpression.WhereClause} {limitSql}";
}
else
{
SqlString = $"{selectSql} {fromTableSql} {whereSql} {orderbySql} {limitSql}";
}
return(this);
}
/// <inheritdoc />
/// <summary>
/// 执行解析
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public UpdateExpression(LambdaExpression expression, ISqlDialect dialect)
{
_sqlCmd = new StringBuilder(100);
Param = new TheDynamicParameters();
Dialect = dialect;
Visit(expression);
}
public SqlProvider <T> FormatUpdateZhanglei <TKey>(Expression <Func <T, TKey> > expression, TKey value)
{
MemberExpression m = expression.Body as MemberExpression;
if (m == null)
{
var mbs = new ExpressionPropertyFinder(expression, typeof(T)).MemberList;
if (!mbs.Any())
{
throw new FieldAccessException($"未在更新表达式中找到类型 {typeof(T).Name} 的任何字段");
}
}
var update = ResolveExpression.Instance(Dialect).ResolveUpdateZhanglei <T>(expression, value);
var where = ResolveExpression.Instance(Dialect).ResolveWhere(Context.CommandSet.WhereExpression);
var whereSql = where.SqlCmd;
Params = where.Param;
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
return(this);
}
/// <inheritdoc />
/// <summary>
/// 执行解析
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public DeleteExpression(TKey id, ISqlDialect dialect)
{
_sqlCmd = new StringBuilder(100);
Param = new TheDynamicParameters();
_obj = id;
Dialect = dialect;
}
protected void CallEvent(string sqlString, TheDynamicParameters param, string message)
{
try
{
var args = new DapperExEventArgs(sqlString, param, message);
ErrorHappened?.Invoke(this, args);
}
catch { }
}
public UpdateExpression(IEnumerable <LambdaExpression> expressionList, T model, ISqlDialect dialect)
{
_sqlCmd = new StringBuilder(100);
Param = new TheDynamicParameters();
Dialect = dialect;
Model = model;
foreach (var expression in expressionList)
{
Visit(expression);
}
}
protected void SetSql()
{
if (SqlProvider.SqlString != null)
{
this.SqlString = SqlProvider.SqlString;
}
if (SqlProvider.Params != null)
{
this.Params = SqlProvider.Params;
}
}
public SqlProvider <T> FormatDelete <TKey>(TKey id)
{
var fromTableSql = FormatTableName();
var where = ResolveExpression.Instance(Dialect).ResolveWhere <T, TKey>(id);
var whereSql = where.SqlCmd;
Params = where.Param;
SqlString = $"DELETE {fromTableSql} {whereSql}";
return(this);
}
public SqlProvider <T> FormatDelete()
{
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.CommandSet.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
SqlString = $"DELETE {fromTableSql} {whereSql }";
return(this);
}
public SqlProvider <T> FormatUpdate(Expression <Func <T, T> > updateExpression)
{
var update = ResolveExpression.Instance(Dialect).ResolveUpdate(updateExpression);
var where = ResolveExpression.Instance(Dialect).ResolveWhere(Context.CommandSet.WhereExpression);
var whereSql = where.SqlCmd;
Params = where.Param;
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
return(this);
}
public SqlProvider <T> FormatUpdateNotDefault(T entity)
{
var update = ResolveExpression.Instance(Dialect).ResolveUpdateNotDefault <T>(a => entity);
var where = ResolveExpression.Instance(Dialect).ResolveWhere(entity);
var whereSql = where.SqlCmd;
Params = where.Param;
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
return(this);
}
public SqlProvider <T> FormatCount()
{
var selectSql = "SELECT COUNT(1)";
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
SqlString = $"{selectSql} {fromTableSql} {whereSql} ";
return(this);
}
public SqlProvider <T> FormatSum(LambdaExpression lambdaExpression)
{
var selectSql = ResolveExpression.Instance(Dialect).ResolveSum(typeof(T).GetPropertiesInDb(true), lambdaExpression);
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
SqlString = $"{selectSql} {fromTableSql} {whereSql} ";
return(this);
}
protected IEnumerable <TRst> QueryDatabase <TRst>(string sqlString, TheDynamicParameters param, IDbTransaction dbTransaction, int count = 0)
{
var ps = typeof(TRst).GetJsonColumnProperty();
if (ps.Length > 0 && HasSerializer)
{
var Reader = DbCon.ExecuteReader(sqlString, param, dbTransaction);
var Parser = Reader.GetRowParser <TRst>();
List <TRst> lrst = new List <TRst>();
int i = 0;
while (Reader.Read())
{
TRst rst = Parser(Reader);
foreach (var p in ps)
{
int col;
try
{
col = Reader.GetOrdinal($"{p.Name}{ResolveExpression.JsonColumnNameSuffix}");
}
catch
{
continue;
}
object o = Reader.GetValue(col);
if (o == DBNull.Value)
{
continue;
}
var s = Reader.GetString(col);
var pv = Deserializer(s, p.PropertyType);
p.SetValue(rst, pv);
}
lrst.Add(rst);
if (++i >= count && count > 0)
{
break;
}
}
Reader.Close();
return(lrst);
}
else
{
return(DbCon.Query <TRst>(sqlString, param, dbTransaction));
}
}
public SqlProvider <T> FormatExists()
{
var selectSql = Dialect.IsUseLimitInsteadOfTop ? "SELECT 1" : "SELECT TOP 1 1";
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var limitSql = Dialect.IsUseLimitInsteadOfTop ? " Limit 1" : string.Empty;
SqlString = $"{selectSql} {fromTableSql} {whereSql} {limitSql}";
return(this);
}
private int Exec(string sqlString, TheDynamicParameters param, IDbTransaction dbTransaction)
{
try
{
return(DbCon.Execute(sqlString, param, dbTransaction));
}
catch (Exception e)
{
CallEvent(sqlString, param, e.Message);
if (Throws)
{
throw new Exception($"{e.Message} sql:{sqlString} params:{param}", e);
}
else
{
return(0);
}
}
}
private IEnumerable <T> QueryWithException(string sqlString, TheDynamicParameters param, IDbTransaction dbTransaction)
{
try
{
return(QueryDatabase <T>(sqlString, param, dbTransaction));
}
catch (Exception e)
{
CallEvent(sqlString, param, e.Message);
if (Throws)
{
throw new Exception($"{e.Message} sql:{sqlString} params:{param}", e);
}
else
{
return(new List <T>());
}
}
}
private T QueryFirst(string sqlString, TheDynamicParameters param, IDbTransaction dbTransaction)
{
try
{
return(QueryDatabase <T>(sqlString, param, dbTransaction, 1).FirstOrDefault());
}
catch (Exception e)
{
CallEvent(sqlString, param, e.Message);
if (Throws)
{
throw new Exception($"{e.Message} sql:{sqlString} params:{param}", e);
}
else
{
return(default(T));
}
}
}
public SqlProvider <T> FormatUpdateSelect(Expression <Func <T, T> > updator)
{
var update = ResolveExpression.Instance(Dialect).ResolveUpdate(updator);
var selectSql = ResolveExpression.Instance(Dialect).ResolveSelectOfUpdate(typeof(T).GetPropertiesInDb(true), Context.QuerySet.SelectExpression);
var where = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
var whereSql = where.SqlCmd;
Params = where.Param;
Params.AddDynamicParams(update.Param);
var topSql = Context.QuerySet.TopNum.HasValue && !Dialect.IsUseLimitInsteadOfTop ? " TOP " + Context.QuerySet.TopNum.Value : "";
var limitSql = Context.QuerySet.TopNum.HasValue && Dialect.IsUseLimitInsteadOfTop ? $" Limit {Context.QuerySet.TopNum.Value}" : "";
SqlString = $"UPDATE {topSql} {FormatTableName(false)} WITH ( UPDLOCK, READPAST ) {update.SqlCmd} {selectSql} {whereSql} {limitSql}";
return(this);
}
public SqlProvider <T> FormatUpdateZhanglei(T entity, IEnumerable <LambdaExpression> expressionList)
{
var update = ResolveExpression.Instance(Dialect).ResolveUpdateZhanglei <T>(expressionList, entity);
IWhereExpression where;
if (Context.CommandSet.WhereExpression == null)
{
where = ResolveExpression.Instance(Dialect).ResolveWhere(entity);
}
else
{
where = ResolveExpression.Instance(Dialect).ResolveWhere(Context.CommandSet.WhereExpression);
}
var whereSql = where.SqlCmd;
Params = where.Param;
Params.AddDynamicParams(update.Param);
SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
return(this);
}
public SqlProvider <T> FormatToPageList(Type type, int pageIndex, int pageSize, IFieldAnyExpression fieldAnyExpression)
{
var orderbySql = ResolveExpression.Instance(Dialect).ResolveOrderBy(Context.QuerySet.OrderbyExpressionList);
if (string.IsNullOrEmpty(orderbySql))
{
throw new Exception("分页查询需要排序条件");
}
var selectSql = ResolveExpression.Instance(Dialect).ResolveSelect(type, pageSize, Context.QuerySet.IsDistinct, Context.QuerySet.SelectExpression);
var fromTableSql = FormatTableName();
var whereParams = ResolveExpression.Instance(Dialect).ResolveWhere(Context.QuerySet.WhereExpression);
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var limitSql = ResolveExpression.Instance(Dialect).ResolveLimit(pageSize);
if (fieldAnyExpression != null)
{
var selectDistinctSql = ResolveExpression.Instance(Dialect).ResolveSelect(type, pageSize, true, Context.QuerySet.SelectExpression);
var di = fieldAnyExpression.WhereParam.ToDictionary();
foreach (var i in di)
{
Params.Add(i.Key, i.Value);
}
string newTable = $"({selectSql} ,jsonb_array_elements({fieldAnyExpression.ListFieldName}) as \"{ResolveExpression.FieldAnyColumnName}\" {fromTableSql} {whereSql} {orderbySql}) as {ResolveExpression.FieldAnyTableName}";
string newNewTable = $"{selectDistinctSql} from {newTable} where {fieldAnyExpression.WhereClause} {limitSql}";
SqlString = $"SELECT COUNT(1) from ({newNewTable}) as {ResolveExpression.FieldAnyTableName};";
SqlString += $@"{selectDistinctSql}
FROM ( SELECT *
,ROW_NUMBER() OVER ( {orderbySql} ) AS ROWNUMBER
from {newTable} where {fieldAnyExpression.WhereClause}
) T
WHERE ROWNUMBER > {(pageIndex - 1) * pageSize}
AND ROWNUMBER <= {pageIndex * pageSize} {orderbySql} {limitSql};";
}
else
{
SqlString = $"SELECT COUNT(1) {fromTableSql} {whereSql};";
if (Dialect.pageListDialectEnum == PageListDialectEnum.Mysql)
{
SqlString += $@"{selectSql} {fromTableSql} {whereSql} {orderbySql} limit {(pageIndex - 1) * pageSize},{pageSize}";
}
else
{
SqlString += $@"{selectSql}
FROM ( SELECT *
,ROW_NUMBER() OVER ( {orderbySql} ) AS ROWNUMBER
{fromTableSql}
{whereSql}
) T
WHERE ROWNUMBER > {(pageIndex - 1) * pageSize}
AND ROWNUMBER <= {pageIndex * pageSize} {orderbySql} {limitSql};";
}
}
return(this);
}
public DapperExEventArgs(string sql, TheDynamicParameters param, string extMessage) : base()
{
Sql = sql;
Params = param;
ExtMessage = extMessage;
}
public SqlProvider(ISqlDialect dialect = null)
{
Params = new TheDynamicParameters();
Dialect = dialect ?? new SqlServerDialect();
}