public void SelectBlockMethod(string str)
{
var temp = _root;
foreach (var t in str.Substring(4).Split('_'))
{
if (t == "1")
{
temp = temp.Left;
}
else if (t == "0")
{
temp = temp.Right;
}
}
if (_selected != null)
{
_selected.Fill = _colorBlock;
}
_selected = temp.Rect;
_selected.Fill = _colorBlockSelect;
SelectBlock?.Invoke(new SelectBlockObject(temp.Limits, temp.New, temp.Func));
}
/// <summary>
/// 按不分页的方式解释 SELECT 命令。
/// </summary>
/// <param name="sBlock">SelectBlock对象。</param>
/// <param name="DbParameters">用于缓存在解释过程中可能会产生的参数。</param>
/// <returns></returns>
protected virtual string ParsingWithNotPage(SelectBlock sBlock, ref List <IDbDataParameter> DbParameters)
{
StringBuilder cBuffer = new StringBuilder("SELECT ");
bool AppendComma = true; // 用于标识每个段后是否添加逗号。
sBlock.Blocks[0].DescriptionParserAdapter = sBlock.DescriptionParserAdapter;
string item_buf = sBlock.Blocks[0].GetParser().Parsing(ref DbParameters);
if (item_buf[0] == (char)0x20)
{
item_buf = item_buf.Remove(0, 1);
}
cBuffer.Append(item_buf);
for (int i = 1; i < sBlock.Blocks.Count; ++i)
{
if (sBlock.Blocks[i] is FromBlock || sBlock.Blocks[i] is WhereBlock || sBlock.Blocks[i] is GroupByBlock || sBlock.Blocks[i] is OrderByBlock)
{
AppendComma = false;
}
sBlock.Blocks[i].DescriptionParserAdapter = sBlock.DescriptionParserAdapter;
item_buf = sBlock.Blocks[i].GetParser().Parsing(ref DbParameters);
if (item_buf[0] == (char)0x20)
{
cBuffer.AppendFormat("{0}{1}", AppendComma ? "," : string.Empty, item_buf);
}
else
{
cBuffer.AppendFormat("{0} {1}", AppendComma ? "," : string.Empty, item_buf);
}
}
return(cBuffer.ToString());
}
/// <summary>
/// 查询测试数据.
/// </summary>
/// <param name="page">当前的页.</param>
/// <param name="pageSize">每页数据条数.</param>
/// <param name="group">分组ID（为 null 时查所有数据）.</param>
/// <returns></returns>
public PaginatedCollection<dynamic> Query(int page, int pageSize, int? group)
{
DbCommandBuilder cb = new DbCommandBuilder();
SelectBlock sb = cb.Select(Fun.Count("*")).From(
fm.Table(ItemsTable, "itm"),
fm.LeftJoin(GROUP_TABLE, "grp").ON(td.Field("itm", "GroupId") == td.Field("grp", "GroupId"))
);
List<object> condition = new List<object>();
if (group != null)
{
condition.Add(td.Field("itm", "GroupId") == group.Value);
sb.Where(condition.ToArray());
}
object count = db.ExecuteScalar(cb);
int total = (count == null || count == DBNull.Value) ? 0 : Convert.ToInt32(count);
if (total < 1)
return null;
sb = cb.Select(td.Field("itm", "TestId"),
td.Field("grp", "GroupName"),
td.Field("itm", "TestName"),
td.Field("itm", "TestAge"),
td.Field("itm", "TestSex")
).From(
fm.Table(ItemsTable, "itm"),
fm.LeftJoin(GROUP_TABLE, "grp").ON(td.Field("itm", "GroupId") == td.Field("grp", "GroupId"))
);
if (condition.Count > 0)
sb.Where(condition.ToArray());
sb.OrderBy(td.Field("itm", "TestId"), OrderByMode.DESC).Paging(pageSize, page);
List<dynamic> entities = db.ExecuteDynamicEntity(cb);
return new PaginatedCollection<dynamic>(total, page, entities);
}
/// <summary>
/// 根据指定的条件查询指定实体对应的表，并返回数据集合.
/// </summary>
/// <typeparam name="TEntity">对应数据库表的实体类型.</typeparam>
/// <param name="action">用于指定查询条件.</param>
/// <returns></returns>
protected List <TEntity> Select <TEntity>(Action <TEntityAgent, SelectBlock> action) where TEntity : DataEntity, new()
{
EntityTableAttribute entityTable = GetTableAttribute();
DbCommandBuilder Command = new DbCommandBuilder();
SelectBlock select = Command.Select(td.Field("*")).From(entityTable.TableName);
TEntityAgent agent = new TEntityAgent();
agent.TableContext = this;
action(agent, select);
return(ExecuteQuery <TEntity>(Command));
}
/// <summary>
/// 解释 SELECT 命令。
/// </summary>
/// <param name="DbParameters">用于缓存在解释过程中可能会产生的参数。</param>
/// <returns></returns>
public override string Parsing(ref List <IDbDataParameter> DbParameters)
{
SelectBlock sBlock = (SelectBlock)this.Description;
if (sBlock.Pager == null)
{
return(ParsingWithNotPage(sBlock, ref DbParameters));
}
else
{
return(ParsingWithPage(sBlock, ref DbParameters));
}
}
public VMSelectBlock(double x, double y, double width, double height)
{
X = x;
Y = y;
ParentEl = new SelectBlock()
{
DataContext = this
};
ParentEl.Height = height / Constants.MainScale;
ParentEl.Width = width / Constants.MainScale;
Canvas.SetLeft(ParentEl, X);
Canvas.SetTop(ParentEl, Y);
Panel.SetZIndex(ParentEl, -1);
}
/// <summary>
/// 按分页的方式解释 SELECT 命令。
/// </summary>
/// <param name="sBlock">SelectBlock对象。</param>
/// <param name="DbParameters">用于缓存在解释过程中可能会产生的参数。</param>
/// <returns></returns>
protected override string ParsingWithPage(SelectBlock sBlock, ref List <IDbDataParameter> DbParameters)
{
StringBuilder CommandText = new StringBuilder(ParsingCore(sBlock, ref DbParameters));
if (sBlock.Pager.CurrentPage > 1)
{
int Offset = (sBlock.Pager.CurrentPage - 1) * sBlock.Pager.PageSize + 1;
int Limit = sBlock.Pager.CurrentPage * sBlock.Pager.PageSize;
CommandText.AppendFormat("SELECT * FROM [PAGE_TEMP] WHERE [ROWNUM] BETWEEN {0} AND {1}", Offset, Limit);
}
else
{
CommandText.AppendFormat("SELECT TOP {0} * FROM [PAGE_TEMP]", sBlock.Pager.PageSize);
}
return(CommandText.ToString());
}
/// <summary>
/// 设置要查询的结果子表达式，并返回 SELECT 表达式树对象.
/// </summary>
/// <param name="Expressions">要查询的结果表太式（字段信息或函数表达式）</param>
/// <returns></returns>
public SelectBlock Select(params object[] Expressions)
{
selectBlock = _dbCommand.Select();
IDescription descr;
foreach (object desObject in Expressions)
{
descr = desObject as IDescription;
if (descr != null) // 当返回的字段元素非 IDescription 对象时忽略它（否则命令解无法解释）.
{
selectBlock.AddElement(descr);
}
}
selectBlock.From(tableName);
return(selectBlock);
}
/// <summary>
/// 按分页的方式解释 SELECT 命令。
/// </summary>
/// <param name="sBlock">SelectBlock对象。</param>
/// <param name="DbParameters">用于缓存在解释过程中可能会产生的参数。</param>
/// <returns></returns>
protected override string ParsingWithPage(SelectBlock sBlock, ref List <IDbDataParameter> DbParameters)
{
int offset;
if (sBlock.Pager.CurrentPage > 1)
{
offset = sBlock.Pager.PageSize * (sBlock.Pager.CurrentPage - 1);
}
else
{
offset = 0;
}
StringBuilder cBuffer = new StringBuilder(ParsingWithNotPage(sBlock, ref DbParameters));
// 在结尾处添加 LIMIT ... OFFSET ... 语句实现分页。
cBuffer.AppendFormat(" LIMIT {0} OFFSET {1}", sBlock.Pager.PageSize, offset);
return(cBuffer.ToString());
}
/// <summary>
/// 开始建立查询命令对象树.
/// </summary>
/// <typeparam name="TEntityAgent">表对应的实体映射代理对象.</typeparam>
/// <returns></returns>
internal MultiQuerySelector From <TEntityAgent>() where TEntityAgent : EntityAgent, new()
{
sBlock = Command.Select();
TEntityAgent agent = new TEntityAgent();
TableMapper context = agent.CreateContext();
context.SetDataEngine(ReadEngine, null);
agent.TableContext = context;
agent.IncludeTableName = true; // 指法查询代理对象在生成字段信息时带上表名.
// 将实体表的代理类记录下来，后面组织查询时还要用到.
if (!(AgentsIndexes.ContainsKey(typeof(TEntityAgent))))
{
AgentBufferItem BufferItem = new AgentBufferItem(agent);
AgentsIndexes.Add(typeof(TEntityAgent), BufferItem);
Agents.Add(BufferItem);
}
// 向 FROM 了向中添加查询的表.
EntityTableAttribute tableAttribute = context.GetTableAttribute();
fBlock.Content.Add(fm.Table(tableAttribute.TableName));
sBlock.Blocks.Add(fBlock);
return(this);
}
public void CreateBlock()
{
modifyTerrian.AddBlockCenter(SelectBlock.GetCurBlock());
audio.PlayOneShot(blockPickSound, 1.0f);
modifyTerrian.StartLoadChunks();
}
/// <summary>
/// 分布关键的命令部份解释。
/// </summary>
/// <param name="sBlock">SELECT命令段描述对象。</param>
/// <param name="DbParameters">用于缓存在解释过程中可能会产生的参数。</param>
/// <returns></returns>
private string ParsingCore(SelectBlock sBlock, ref List <IDbDataParameter> DbParameters)
{
bool AppendComma = true; // 用于标识每个段后是否添加逗号。
sBlock.Blocks[0].DescriptionParserAdapter = sBlock.DescriptionParserAdapter;
string item_buf = sBlock.Blocks[0].GetParser().Parsing(ref DbParameters);
if (item_buf[0] == (char)0x20)
{
item_buf = item_buf.Remove(0, 1);
}
StringBuilder FieldsBuffer = new StringBuilder(item_buf); // 查询的字段信息部份
StringBuilder OtherBuffer = new StringBuilder(); // 除字段信息之外的其它子句部份
OrderByBlock OverOrderBy = null;
for (int i = 1; i < sBlock.Blocks.Count; ++i)
{
if (sBlock.Blocks[i] is FromBlock || sBlock.Blocks[i] is WhereBlock || sBlock.Blocks[i] is GroupByBlock)
{
AppendComma = false;
}
if (sBlock.Blocks[i] is OrderByBlock)
{
AppendComma = false;
OverOrderBy = (OrderByBlock)sBlock.Blocks[i];
continue; // 不在内嵌SELECT子句中生成 ORDER BY 子句（否则会产生查询错误）
}
sBlock.Blocks[i].DescriptionParserAdapter = sBlock.DescriptionParserAdapter;
item_buf = sBlock.Blocks[i].GetParser().Parsing(ref DbParameters);
if (AppendComma)
{
if (item_buf[0] == (char)0x20)
{
FieldsBuffer.AppendFormat(",{0}", item_buf);
}
else
{
FieldsBuffer.AppendFormat(", {0}", item_buf);
}
}
else
{
if (item_buf[0] == (char)0x20)
{
OtherBuffer.Append(item_buf);
}
else
{
OtherBuffer.AppendFormat(" {0}", item_buf);
}
}
}
if (OverOrderBy == null)
{
if (IsNull(sBlock.Pager.HelpField))
{
throw (new Exception("解释分页时失败：没有提供排序字段。"));
}
OverOrderBy = new OrderByBlock();
OverOrderBy.Field = sBlock.Pager.HelpField;
OverOrderBy.Sort = sBlock.Pager.HelpSort;
}
StringBuilder SqlCore = new StringBuilder("WITH [PAGE_TEMP] AS (SELECT ");
OverOrderBy.DescriptionParserAdapter = sBlock.DescriptionParserAdapter;
SqlCore.AppendFormat("{0}, ROW_NUMBER() OVER({1}) AS [ROWNUM]", FieldsBuffer, OverOrderBy.GetParser().Parsing(ref DbParameters));
SqlCore.Append(OtherBuffer);
SqlCore.AppendFormat(") ");
return(SqlCore.ToString());
}
/// <summary>
/// 按分页的方式解释 SELECT 命令。
/// </summary>
/// <param name="sBlock">SelectBlock对象。</param>
/// <param name="DbParameters">用于缓存在解释过程中可能会产生的参数。</param>
/// <returns></returns>
protected virtual string ParsingWithPage(SelectBlock sBlock, ref List <IDbDataParameter> DbParameters)
{
return("尚未实现。");
}
