private static void AddValues(TypeMapping typeMapping, BaseQueryBuilder baseQueryBuilder, string baseName)
{
bool first = true;
foreach (var property in typeMapping.Columns.Values)
{
if (property.Readonly)
{
continue;
}
if (first)
{
first = false;
}
else
{
baseQueryBuilder.StringBuilder.Append(",");
}
if (property.Reference != null && property.Reference.IsComplexType)
{
AddValues(property.Reference, baseQueryBuilder, property.ComposeName(baseName));
}
else
{
baseQueryBuilder.StringBuilder.DuringColumn(null, property.ComposeName(baseName));
}
}
}
private static void AddParameters <T, TMe>(ISetTarget <T, TMe> target, object value, TypeMapping typeMapping, SelectedTable table,
BaseQueryBuilder baseQueryBuilder, string baseName)
{
foreach (var property in typeMapping.Columns.Values)
{
if (property.Readonly)
{
continue;
}
var parameter = value != null?property.PropertyInfo.GetValue(value) : null;
if (property.Reference != null && property.Reference.IsComplexType)
{
AddParameters(target, parameter, property.Reference, table, baseQueryBuilder, property.ComposeName(baseName));
}
else
{
if (property.IsJson)
{
parameter = JsonConvert.SerializeObject(parameter);
}
target.AppendSet();
string tableName = table.Alias;
baseQueryBuilder.StringBuilder.DuringColumn(tableName, property.ComposeName(baseName));
baseQueryBuilder.StringBuilder.Append("=");
var index = baseQueryBuilder.AddParameter(parameter);
baseQueryBuilder.StringBuilder.DuringParameter(index);
}
}
}
private static void AddParameters(object value, TypeMapping typeMapping, BaseQueryBuilder baseQueryBuilder)
{
bool first = true;
foreach (var property in typeMapping.Columns.Values)
{
if (property.Readonly)
{
continue;
}
if (first)
{
first = false;
}
else
{
baseQueryBuilder.StringBuilder.Append(",");
}
var propertyValue = value != null?property.PropertyInfo.GetValue(value) : null;
if (property.Reference != null && property.Reference.IsComplexType)
{
AddParameters(propertyValue, property.Reference, baseQueryBuilder);
}
else
{
if (property.IsJson)
{
propertyValue = JsonConvert.SerializeObject(propertyValue);
}
var parameterIndex = baseQueryBuilder.AddParameter(propertyValue);
baseQueryBuilder.StringBuilder.DuringParameter(parameterIndex);
}
}
}
public IActionResult Post(BaseQueryBuilder qb)
{
var methods = typeof(string).GetMethods();
MethodInfo methodInfo = methods.FirstOrDefault(p => p.Name == "Contains");
IQueryable <User> users = new User().GetUsers().Where(qb);
return(this.Ok(users));
}
public static IInsertIntoResult <T, TMe> InsertInto(BaseQueryBuilder baseQueryBuilder)
{
baseQueryBuilder.StringBuilder.Append("INSERT INTO");
var typeMapping = baseQueryBuilder.Mapper.GetTypeMapping(typeof(T));
baseQueryBuilder.StringBuilder.DuringTable(typeMapping.TableSchema, typeMapping.TableName);
return(new FluentBaseBuilder <T, TMe>(baseQueryBuilder));
}
public static IDeleteResult <T, TMe> Delete(BaseQueryBuilder queryBuilder)
{
var result = new FluentBaseBuilder <T, TMe>(queryBuilder);
queryBuilder.StringBuilder.BeforeDelete();
result.CurrentContext = QueryContext.Delete;
return(result);
}
public static IJoinResult <T, TMe> Join <T, TMe, TKey>(this IJoinTarget <T, TMe> joinTarget, Expression <Func <T, TKey> > tableExpression, JoinType type)
{
joinTarget.QueryBuilder.AppendJoin(type);
BaseQueryBuilder queryBuilder = joinTarget.QueryBuilder;
var selectedTable = queryBuilder.GetTable(tableExpression, register: true);
queryBuilder.StringBuilder.AppendTable(selectedTable);
return((IJoinResult <T, TMe>)joinTarget);
}
public TestFluentGenericQueryBuilder()
{
var mySqlDriver = new MySqlDriver();
var mapper = new Mapper();
fluentSelectBuilder = FluentBaseBuilder <FakeClass, FolkeTuple> .Select(mySqlDriver, mapper);
queryBuilder = fluentSelectBuilder.QueryBuilder;
}
public static IFromResult <T, TMe> From <T, TMe, TU>(this IFromTarget <T, TMe> fromTarget, Expression <Func <T, TU> > expression)
{
fromTarget.AppendFrom();
BaseQueryBuilder queryBuilder = fromTarget.QueryBuilder;
var selectedTable = queryBuilder.GetTable(expression, register: true);
queryBuilder.StringBuilder.AppendTable(selectedTable);
return((IFromResult <T, TMe>)fromTarget);
}
internal static void SubQuery <T, TMe>(this IFluentBuilder fluentBuilder, Action <ISelectResult <T, TMe> > subQuery)
{
var queryBuilder = new BaseQueryBuilder(fluentBuilder.QueryBuilder);
var builder = FluentBaseBuilder <T, TMe> .Select(queryBuilder);
subQuery(builder);
BaseQueryBuilder tempQualifier = fluentBuilder.QueryBuilder;
tempQualifier.StringBuilder.Append(" (");
tempQualifier.StringBuilder.Append(queryBuilder.Sql);
tempQualifier.StringBuilder.Append(')');
}
public static ISetResult <T, TMe> Set <T, TMe, TU>(this ISetTarget <T, TMe> target, Expression <Func <T, TU> > column, Expression <Func <T, TU> > value)
{
target.AppendSet();
BaseQueryBuilder queryBuilder = target.QueryBuilder;
var column1 = queryBuilder.ExpressionToColumn(column.Body, ParseOptions.RegisterTables);
column1.Accept(queryBuilder.StringBuilder);
queryBuilder.StringBuilder.Append("=");
queryBuilder.AddExpression(value.Body);
return((ISetResult <T, TMe>)target);
}
public static IAndWhereResult <T, TMe> Or <T, TMe>(this IAndWhereTarget <T, TMe> target, Expression <Func <T, bool> > expression)
{
BaseQueryBuilder tempQualifier = target.QueryBuilder;
if (target.CurrentContext != QueryContext.WhereExpression)
{
target.CurrentContext = QueryContext.WhereExpression;
}
else
{
tempQualifier.StringBuilder.DuringBinaryOperator(BinaryOperatorType.OrElse);
}
target.QueryBuilder.AddBooleanExpression(expression.Body);
return((IAndWhereResult <T, TMe>)target);
}
public static IOrderByResult <T, TMe> OrderBy <T, TMe, TV>(this IOrderByTarget <T, TMe> fluentBuilder, Expression <Func <T, TV> > expression)
{
BaseQueryBuilder queryBuilder = fluentBuilder.QueryBuilder;
if (fluentBuilder.CurrentContext != QueryContext.OrderBy)
{
queryBuilder.StringBuilder.BeforeOrderBy();
fluentBuilder.CurrentContext = QueryContext.OrderBy;
}
else
{
queryBuilder.StringBuilder.DuringOrderBy();
}
fluentBuilder.QueryBuilder.AddExpression(expression.Body);
return((IOrderByResult <T, TMe>)fluentBuilder);
}
private static void AppendJoin(this BaseQueryBuilder queryBuilder, JoinType type)
{
switch (type)
{
case JoinType.LeftOuter:
queryBuilder.StringBuilder.AppendAfterSpace("LEFT JOIN");
break;
case JoinType.RightOuter:
queryBuilder.StringBuilder.AppendAfterSpace("RIGHT JOIN");
break;
case JoinType.Inner:
queryBuilder.StringBuilder.AppendAfterSpace("INNER JOIN");
break;
}
}
public static IGroupByResult <T, TMe> GroupBy <T, TMe, TU>(this IGroupByTarget <T, TMe> builder, Expression <Func <T, TU> > column)
{
BaseQueryBuilder queryBuilder = builder.QueryBuilder;
if (builder.CurrentContext != QueryContext.GroupBy)
{
queryBuilder.StringBuilder.BeforeGroupBy();
builder.CurrentContext = QueryContext.GroupBy;
}
else
{
queryBuilder.StringBuilder.DuringGroupBy();
}
var column1 = queryBuilder.ExpressionToColumn(column.Body);
column1.Accept(queryBuilder.StringBuilder);
return((IGroupByResult <T, TMe>)builder);
}
public static SelectedTable AppendSelectedColumns(this BaseQueryBuilder builder, SelectedTable selectedTable, IEnumerable <PropertyMapping> columns)
{
foreach (var column in columns)
{
if (column.Reference != null && column.Reference.IsComplexType)
{
var subTable = builder.RegisterTable(selectedTable, column);
builder.AppendAllSelects(subTable);
}
else
{
var visitable = new Field(selectedTable, column);
var selectedField = builder.SelectField(visitable);
selectedField.Accept(builder.StringBuilder);
}
}
return(selectedTable);
}
public ActionResult <IEnumerable <string> > Post(BaseQueryBuilder qb)
{
IQueryable <User> users = new User().GetUsers().Where(qb);
return(this.Ok(users));
}
protected FluentBaseBuilder(BaseQueryBuilder queryBuilder)
{
baseQueryBuilder = queryBuilder;
}
public static IQueryable <TEntity> Where <TEntity>(this IQueryable <TEntity> source, BaseQueryBuilder qb)
{
var query = source.Where <TEntity>((SearchCondition)qb);
if (qb.DefaultSort && typeof(TEntity).GetProperty("SortIndex") != null)
{
qb.SortField = "SortIndex";
qb.SortOrder = "asc";
}
qb.TotolCount = query.Count();
if (!string.IsNullOrEmpty(qb.SortField))
{
string[] fields = qb.SortField.Split(',');
string[] orders = qb.SortOrder.Split(',');
for (int i = 0; i < fields.Length; i++)
{
bool isThenBy = true;
if (i == 0)
{
isThenBy = false;
}
query = query.OrderBy <TEntity>(fields[i], string.Equals(orders[i], Formula.SortMode.Asc.ToString(), StringComparison.CurrentCultureIgnoreCase), isThenBy);
}
}
else
{
query = query.OrderBy <TEntity>("ID", false);
}
if (qb.PageSize == 0)
{
return(query);
}
query = query.Skip(qb.PageSize * qb.PageIndex).Take(qb.PageSize);
return(query);
}
public static SelectedTable AppendAllSelects(this BaseQueryBuilder builder, SelectedTable selectedTable)
{
return(builder.AppendSelectedColumns(selectedTable, selectedTable.Mapping.Columns.Values));
}
public IHttpActionResult Post(BaseQueryBuilder qb)
{
IQueryable <User> users = new User().GetUsers().Where(qb);
return(this.Ok(users));
}
public static ISelectResult <T, TMe> Select(BaseQueryBuilder baseQueryBuilder)
{
return(new FluentBaseBuilder <T, TMe>(baseQueryBuilder));
}
public static IUpdateResult <T, TMe> Update(BaseQueryBuilder queryBuilder)
{
queryBuilder.StringBuilder.BeforeUpdate();
queryBuilder.StringBuilder.AppendTable(queryBuilder.RegisterRootTable());
return(new FluentBaseBuilder <T, TMe>(queryBuilder));
}
public static DataTable ExecuteDataTable(this SQLHelper sqlHelper, string sql, BaseQueryBuilder qb, bool dealOrderby = true)
{
string orderby = "";
if (dealOrderby)
{
int index = sql.LastIndexOf(" order by", StringComparison.CurrentCultureIgnoreCase);
if (index > 0)
{
orderby = sql.Substring(index + " order by".Length);
sql = sql.Substring(0, index);
}
}
SearchCondition authCnd = FormulaHelper.CreateAuthDataFilter();
#region 处理@参数
List <SqlParameter> pList = new List <SqlParameter>();
for (int i = authCnd.Items.Count - 1; i >= 0; i--)
{
var item = authCnd.Items[i];
if (item.Field.StartsWith("@"))
{
authCnd.Items.RemoveAt(i);
pList.Add(new SqlParameter(item.Field, item.Value));
}
}
for (int i = qb.Items.Count - 1; i >= 0; i--)
{
var item = qb.Items[i];
if (item.Field.StartsWith("@"))
{
qb.Items.RemoveAt(i);
pList.Add(new SqlParameter(item.Field, item.Value));
}
}
#endregion
if (authCnd.Items.Count > 0)
{
sql = string.Format("select * from ({0}) sourceTable1 {1}", sql, authCnd.GetWhereString());
}
sql = string.Format("select {2} from ({0}) sourceTable {1}", sql, qb.GetWhereString(), qb.Fields);
string[] qbSortFields = qb.SortField.Split(',');
string[] qbSortOrders = qb.SortOrder.Split(',');
for (int i = 0; i < qbSortFields.Length; i++)
{
qbSortFields[i] += " " + qbSortOrders[i];
}
string qbOrderBy = string.Join(",", qbSortFields);
if (orderby == "" || !qb.DefaultSort)
{
orderby = qbOrderBy;
}
if (qb.PageSize == 0)
{
DataTable dt = sqlHelper.ExecuteDataTable(sql + " order by " + orderby, pList.ToArray(), CommandType.Text);
qb.TotolCount = dt.Rows.Count;
return(dt);
}
else
{
object totalCount = sqlHelper.ExecuteScalar(string.Format("select count(1) from ({0}) tableCount", sql), pList.ToArray(), CommandType.Text);
qb.TotolCount = Convert.ToInt32(totalCount);
int start = qb.PageIndex * qb.PageSize + 1;
int end = start + qb.PageSize - 1;
sql = string.Format(@"select * from (select tempTable1.*, Row_number() over(order by {1}) as RowNumber from ({0}) tempTable1) tmpTable2 where RowNumber between {2} and {3}", sql, orderby, start, end);
return(sqlHelper.ExecuteDataTable(sql, pList.ToArray(), CommandType.Text));
}
}
public IHttpActionResult Post(BaseQueryBuilder qb)
{
IQueryable<User> users = new User().GetUsers().Where(qb);
return this.Ok(users);
}