/// <summary>
/// 构造总数查询条件
/// </summary>
private static void BuildSelectCount <T>(ISelectBuilder <T> selector, RoleQueryRequeest requeest)
{
if (requeest.Status.HasValue)
{
selector.AndWhere("br.Status=@Status").Parameter("Status", requeest.Status);
}
}
//创建一个查询对象构造器
private ISelectBuilder CreateBuilder(DbContext db, ExecuteModel executeModel)
{
//获取当前类名
var tableName = GetTableName(executeModel.Entity.GetType());
//查询器
ISelectBuilder selectBuilder = db.Select(tableName);
//获取条件sql
var whereSql = GetConitionsql(executeModel.Conditions);
//添加SQL条件
selectBuilder.Where(whereSql);
executeModel.Conditions?.ForEach(p =>
{ //先设置参数，组装SQL
if (p.Oper == Operational.In || p.Oper == Operational.NotIn)
{ //In 或 not in 通过设置参数方式
GetParams(p.Key, p.Value, selectBuilder);
}
else
{
selectBuilder.Parameters(p.Key, p.Value);
}
});
if (executeModel.OrderColumn.IsNotNullOrEmpty())
{//如果包括了排序字段，则使用排序
selectBuilder.OrderBy(executeModel.OrderColumn);
}//end if
//返回组装的对象
return(selectBuilder);
}
//根据值类型，动态组装参数名称或值
private string GetParams(string key, object value, ISelectBuilder selectBuilder = null)
{
if (value != null)
{
var list = new List <string>();
var type = value.GetType();
var array = value as Array;
if (array != null)
{//数组类型
for (int i = 0; i < array.Length; i++)
{
var pName = $"@{key}_{i}";
list.Add(pName);
if (selectBuilder != null)
{//设置参数到查询器中
var val = array.GetValue(i);
selectBuilder.Parameters($"{key}_{i}", val);
}
}//end for
//各参数之间使用
return(string.Join(",", list));
}//end if
return(string.Empty);
}
throw new ArgumentNullException("使用了In或NotIn方式查询，但没有给出范围值");
}
public static dynamic GetDynamicWithPaging(ISelectBuilder sb, out int total)
{
var data = sb.Data;
var rows = GetDynamicWithPaging(data.Select, data.From, data.Where, data.GroupBy.Trim(), data.OrderBy.Trim(),
data.RowNumberOrderBy.Trim(), data.PageNumber, data.PageSize, out total, data.Wheres, data.Params.ToArray());
return(rows);
}
/// <summary>
/// Initializes a new instance of an ExistsFilter.
/// </summary>
/// <param name="select"></param>
public ExistsFilter(ISelectBuilder select)
{
if (select == null)
{
throw new ArgumentNullException("select");
}
Select = select;
}
public ICompareWith Compare(ISelectBuilder selectBuilder)
{
_first = t => {
selectBuilder.TryBuild(t, out string str);
return(str);
};
return(this);
}
public ICompareBuilder With(string op, ISelectBuilder selectBuilder)
{
_operator = op;
_second = t => {
selectBuilder.TryBuild(t, out string str);
return(str);
};
return(this);
}
///<summary>
///设置查询条件参数
///</summary>
protected void SetWhereParameter <T>(ISelectBuilder <T> select, Dictionary <string, object> paras)
{
if (paras != null)
{
foreach (string key in paras.Keys)
{
select.Parameter(key, paras[key]);
}
}
}
public ICompareWith Compare(ISelectBuilder selectBuilder)
{
bool translateFunction(ISqlTranslator translator, out string str)
{
return(selectBuilder.TryBuild(translator, out str) && !translator.HasError);
}
_first = translateFunction;
return(this);
}
public ICompareBuilder With(string op, ISelectBuilder selectBuilder)
{
_operator = op;
bool translateFunction(ISqlTranslator translator, out string str)
{
return(selectBuilder.TryBuild(translator, out str) && !translator.HasError);
}
_second = translateFunction;
return(this);
}
/// <summary>
/// Adds the given SELECT statement to the FROM clause.
/// </summary>
/// <param name="builder">The SELECT statement to add.</param>
/// <param name="alias">The optional alias to give the SELECT statement within the SELECT statement.</param>
/// <returns>An object to support aliasing the SELECT statement and defining columns.</returns>
public AliasedSource AddSelect(ISelectBuilder builder, string alias = null)
{
if (builder == null)
{
throw new ArgumentNullException("builder");
}
AliasedSource source = new AliasedSource(builder, alias);
sources.AddSource(source.GetSourceName(), source);
_from.Add(source);
return(source);
}
public static List <T> GetModels <T>(ISelectBuilder sb)
{
var data = sb.Data;
if (string.IsNullOrEmpty(data.Select))
{
data.Select = "*";
}
if (string.IsNullOrEmpty(data.From))
{
data.From = TableName <T>();
}
if ((data.Where + "").Trim() == "")
{
data.Where = " 1=1 ";
}
var builder = Db.Select <T>(data.Select).From(data.From).Where(data.Where);
if (data.Wheres.Any())
{
data.Wheres.ForEach(x =>
{
if (x.LogicalKey == LogicalKeyType.And)
{
builder.AndWhere(string.Format("{0} = @{1}", x.DbName, x.ParamName));
}
else if (x.LogicalKey == LogicalKeyType.Or)
{
builder.OrWhere(string.Format("{0} = @{1}", x.DbName, x.ParamName));
}
});
}
if (!string.IsNullOrEmpty(data.OrderBy.Trim()))
{
builder.OrderBy(data.OrderBy);
}
if (!string.IsNullOrEmpty(data.GroupBy.Trim()))
{
builder.GroupBy(data.GroupBy);
}
if (data.Params.Any())
{
data.Params.ForEach(x =>
{
builder.Parameter(x.ParamName, x.Value);
});
}
return(builder.QueryMany());
}
public static dynamic GetDynamic(ISelectBuilder sb)
{
var data = sb.Data;
if ((data.Select + "").Trim() == "")
{
data.Select = "*";
}
if ((data.From + "").Trim() == "")
{
throw new Exception("From cannot be empty or nothing");
}
if ((data.Where + "").Trim() == "")
{
data.Where = " 1=1 ";
}
var builder = Db.Select <dynamic>(data.Select).From(data.From).Where(data.Where);
if (data.Wheres.Any())
{
data.Wheres.ForEach(x =>
{
if (x.LogicalKey == LogicalKeyType.And)
{
builder.AndWhere(string.Format("{0} = @{1}", x.DbName, x.ParamName));
}
else if (x.LogicalKey == LogicalKeyType.Or)
{
builder.OrWhere(string.Format("{0} = @{1}", x.DbName, x.ParamName));
}
});
}
if (!string.IsNullOrEmpty(data.GroupBy.Trim()))
{
builder.GroupBy(data.GroupBy);
}
if (!string.IsNullOrEmpty(data.OrderBy.Trim()))
{
builder.OrderBy(data.OrderBy);
}
if (data.Params.Any())
{
data.Params.ForEach(x =>
{
builder.Parameter(x.ParamName, x.Value);
});
}
return(builder.QueryMany());
}
public SparqlQueryGenerator(ISelectBuilder selectBuilder)
{
SelectedVariables = new List <SparqlVariable>();
CoalescedVariables = new Dictionary <SparqlVariable, SparqlExpression>();
SelectBuilder = selectBuilder;
#if NET35
QueryBuilder = selectBuilder.GetQueryBuilder();
PatternBuilder = QueryBuilder.RootGraphPatternBuilder;
#else
QueryBuilder = SelectBuilder;
PatternBuilder = QueryBuilder.Root;
#endif
}
/// <summary>
/// Initializes a new instance of a SelectCombiner.
/// </summary>
protected SelectCombiner(ISelectBuilder leftHand, ISelectBuilder rightHand)
{
if (leftHand == null)
{
throw new ArgumentNullException("leftHand");
}
if (rightHand == null)
{
throw new ArgumentNullException("rightHand");
}
this.leftHand = leftHand;
this.rightHand = rightHand;
this.orderBy = new List <OrderBy>();
}
/// <summary>
/// 构造数据查询条件
/// </summary>
private static void BuildSelectRow <T>(ISelectBuilder <T> selector, RoleQueryRequeest requeest)
{
if (requeest.Status.HasValue)
{
selector.AndWhere("br.Status=@Status").Parameter("Status", requeest.Status);
}
if (!string.IsNullOrEmpty(requeest.OrderBy))
{
selector.OrderBy(requeest.OrderBy);
}
if (requeest.CurrentPageIndex.HasValue && requeest.PageSize.HasValue)
{
selector.Paging(requeest.CurrentPageIndex.Value, requeest.PageSize.Value);
}
}
public static List <T> GetModelListTree <T>(this ISelectBuilder source)
{
IDataReader dr = source.DataReader();
TreeMemoAttribute attr = typeof(T).GetCustomAttributes(typeof(TreeMemoAttribute), false)[0] as TreeMemoAttribute;
List <T> items = new List <T>();
List <T> quene = new List <T>();
List <string> fields = new List <string>();
using (dr)
{
while (dr.Read())
{
if (fields.Count == 0)
{
for (int i = 0; i < dr.FieldCount; i++)
{
fields.Add(dr.GetName(i).ToLower());
}
}
if (dr[attr.PID].Equals("") || dr[attr.PID] is System.DBNull)
{
items.Add(CreateNode <T>(dr, fields));
}
else
{
var pid = items.Find(p => p.GetType().GetProperty(attr.ID).GetValue(p).Equals(dr[attr.PID].ToString()));
if (pid == null)
{
quene.Add(CreateNode <T>(dr, fields));
}
else
{
List <T> temp = pid.GetType().GetProperty(attr.Children).GetValue(pid) as List <T>;
if (temp == null)
{
temp = new List <T>();
}
temp.Add(CreateNode <T>(dr, fields));
pid.GetType().GetProperty(attr.Children).SetValue(pid, temp);
}
}
Create(items, quene, attr);
}
}
return(items);
}
// common methods
static void InitBuilder(ISelectBuilder builder, SqlConfig setting)
{
builder.SetSelect(setting.Select);
builder.SetFrom(setting.From);
var orderBy = setting.OrderBy.Default;
string rowNumberOrderBy = orderBy;
orderBy = ParseOrderBy(setting, orderBy.Trim(), ref rowNumberOrderBy);
builder.AddOrderBy(string.Format(" {0} ", orderBy));
builder.AddRowNumberOrderBy(string.Format(" {0} ", rowNumberOrderBy));
var groupBy = setting.GroupBy.Default + "";
groupBy = ParseGroupBy(setting, groupBy.Trim());
builder.AddGroupBy(groupBy);
builder.SetPageSize(setting.PageSize);
builder.SetPageNumber(setting.PageNumber);
}
public void SqlBuilderSample(FeiniuBus.DynamicQuery dynamicQuery, ISelectBuilder selectBuilder)
{
var sb = new StringBuilder();
var mappings = new SqlFieldMappings();
mappings.Map("Guest", "t1.Guest")
.Map("Address", "t1.Address")
.Map("Disabled", "t1.Disabled")
.Map("Amout", "t1.Amout")
.Map("Price", "t1.Price")
.Map("Drink", "t1.Drink")
.Map("Count", "t1.Count")
.Map("Total", "t1.Total")
.Map("Url", "t1.Url");
selectBuilder.Mapping(mappings);
selectBuilder.Where(dynamicQuery.ParamGroup);
var whereClause = selectBuilder.BuildWhere();
var orderbyClause = selectBuilder.OrderBy(dynamicQuery.Order);
System.Console.WriteLine(whereClause);
System.Console.WriteLine(orderbyClause);
}
/// <summary>
/// Adds the given SELECT statement to the FROM clause.
/// </summary>
/// <param name="builder">The SELECT statement to add.</param>
/// <param name="alias">The optional alias to give the SELECT statement within the SELECT statement.</param>
/// <returns>An object to support aliasing the SELECT statement and defining columns.</returns>
public AliasedSource AddSelect(ISelectBuilder builder, string alias = null)
{
if (builder == null)
{
throw new ArgumentNullException("builder");
}
AliasedSource source = new AliasedSource(builder, alias);
sources.AddSource(source.GetSourceName(), source);
_from.Add(source);
return source;
}
private SelectCombiner buildSelectCombiner(MatchResult result, ISelectBuilder leftHand, ISelectBuilder rightHand)
{
MatchResult union = result.Matches[SqlGrammar.SelectCombiner.Union];
if (union.IsMatch)
{
return new Union(leftHand, rightHand);
}
MatchResult intersect = result.Matches[SqlGrammar.SelectCombiner.Intersect];
if (intersect.IsMatch)
{
return new Intersect(leftHand, rightHand);
}
MatchResult except = result.Matches[SqlGrammar.SelectCombiner.Except];
if (except.IsMatch)
{
return new Except(leftHand, rightHand);
}
MatchResult minus = result.Matches[SqlGrammar.SelectCombiner.Minus];
if (minus.IsMatch)
{
return new Minus(leftHand, rightHand);
}
throw new InvalidOperationException();
}
/// <summary>
/// Initializes a new instance of a Intersect.
/// </summary>
/// <param name="leftHand">The left hand SELECT command.</param>
/// <param name="rightHand">The right hand SELECT command.</param>
public Intersect(ISelectBuilder leftHand, ISelectBuilder rightHand)
: base(leftHand, rightHand)
{
}
/// <summary>
/// Initializes a new instance of a Except.
/// </summary>
/// <param name="leftHand">The left hand SELECT command.</param>
/// <param name="rightHand">The right hand SELECT command.</param>
public Except(ISelectBuilder leftHand, ISelectBuilder rightHand)
: base(leftHand, rightHand)
{
}
/// <summary>
/// 可以修改 ISelectBuilder 的 Select, From , Where 语句
/// </summary>
/// <param name="pq"></param>
protected virtual void BeforeList(ISelectBuilder builder)
{
}
protected virtual void BeforeGetMaster(ISelectBuilder builder)
{
}
public T GetModel(ISelectBuilder sb)
{
return(DbHelper.GetModel <T>(sb));
}
public IQueryBuilderSelectOrWhere SelectAs(ISelectBuilder selectBuilder, string alias) =>
SkipIfError(() =>
{
/// <summary>
/// Initializes a new instance of a Union.
/// </summary>
/// <param name="leftHand">The left hand SELECT command.</param>
/// <param name="rightHand">The right hand SELECT command.</param>
public Union(ISelectBuilder leftHand, ISelectBuilder rightHand)
: base(leftHand, rightHand)
{
}
protected IQueryBuilder <TRecord> CreateQueryBuilder(ISelectBuilder selectBuilder)
{
return(new QueryBuilder <TRecord, ISelectBuilder>(selectBuilder, RelationalTransaction, TableAliasGenerator, UniqueParameterNameGenerator, ParamValues, Params, ParamDefaults));
}
/// <summary>
/// Initializes a new instance of a Minus.
/// </summary>
/// <param name="leftHand">The left hand SELECT command.</param>
/// <param name="rightHand">The right hand SELECT command.</param>
public Minus(ISelectBuilder leftHand, ISelectBuilder rightHand)
: base(leftHand, rightHand)
{
}
SubquerySelectBuilder CreateSubqueryBuilder(ISelectBuilder subquerySelectBuilder)
{
return(new SubquerySelectBuilder(new SubquerySource(subquerySelectBuilder.GenerateSelectWithoutDefaultOrderBy(), tableAliasGenerator.GenerateTableAlias())));
}
public SelectStatement()
{
SelectBuilder = new SelectBuilder();
}
