public override CommandData CreateAggregateTableCommand(DataEntityMapping mapping, AggregateSelector selector,
AggregateGroupBy groupBy, QueryExpression query, QueryExpression having, OrderExpression order,
Region region, CreateSqlState state)
{
var command =
base.CreateAggregateTableCommand(mapping, selector, groupBy, query, having, order, region, state);
if (region != null)
{
command.CommandText = region.Start == 0
? $"{command.CommandText} limit {region.Size}"
: $"{command.CommandText} limit {region.Start},{region.Size}";
command.InnerPage = true;
}
return(command);
}
public override CommandData CreateAggregateTableCommand(DataEntityMapping mapping, AggregateSelector selector,
AggregateGroupBy groupBy, QueryExpression query, QueryExpression having, OrderExpression order,
Region region, CreateSqlState state)
{
if (region != null && region.Start > 0)
{
if (order == null)
{
order = CreateGroupByOrderExpression(groupBy);
}
if (order != null)
{
var command = base.CreateAggregateTableCommand(mapping, selector, groupBy, query, having, order,
null, state);
command.CommandText =
$"{command.CommandText} offset {region.Start} row fetch next {region.Size} rows only";
command.InnerPage = true;
return(command);
}
}
return(base.CreateAggregateTableCommand(mapping, selector, groupBy, query, having, order, region, state));
}
public override CommandData CreateAggregateTableCommand(DataEntityMapping mapping, AggregateSelector selector, AggregateGroupBy groupBy, QueryExpression query, QueryExpression having, OrderExpression order, Region region, CreateSqlState state)
{
CommandData command = base.CreateAggregateTableCommand(mapping, selector, groupBy, query, having, order, region, state);
if (region != null)
{
if (region.Start == 0)
{
command.CommandText = string.Format("{0} limit {1}", command.CommandText, region.Size);
}
else
{
command.CommandText = string.Format("{0} limit {2} offset {1}", command.CommandText, region.Start, region.Size);
}
command.InnerPage = true;
}
return(command);
}
public override CommandData CreateAggregateTableCommand(DataEntityMapping mapping, AggregateSelector selector,
AggregateGroupBy groupBy, QueryExpression query, QueryExpression having, OrderExpression order,
Region region, CreateSqlState state)
{
if (region != null)
{
if (region.Start == 0)
{
var sql = new StringBuilder();
var selectString = selector.CreateSelectString(this, false, state);
sql.Append(
$"select top {region.Size} {selectString} from {CreateDataTableMappingSql(mapping, state)}");
if (query != null)
{
sql.Append(GetQueryString(query, false, state));
}
if (groupBy != null)
{
sql.Append(GetGroupByString(groupBy, false, state));
}
if (having != null)
{
sql.Append(GetHavingString(having, false, state));
}
if (order != null)
{
sql.Append(GetAggregateOrderString(order, false, state));
}
var command = new CommandData(sql.ToString())
{
InnerPage = true
};
return(command);
}
if (order == null)
{
order = CreateGroupByOrderExpression(groupBy);
}
if (order != null)
{
var rName = CreateDataFieldSql("R" + Guid.NewGuid().ToString("N"));
var sql = new StringBuilder();
var selectString = selector.CreateSelectString(this, false, state);
sql.Append("select * from (");
var totalSize = region.Start + (long)region.Size;
sql.Append(
$"select top {totalSize} {selectString},row_number() over (order by {order.CreateSqlString(this, false, state)}) as {rName} from {CreateDataTableMappingSql(mapping, state)}");
if (query != null)
{
sql.Append(GetQueryString(query, false, state));
}
if (groupBy != null)
{
sql.Append(GetGroupByString(groupBy, false, state));
}
if (having != null)
{
sql.Append(GetHavingString(having, false, state));
}
sql.Append($") as RT where {rName}>{region.Start}");
var commandData = new CommandData(sql.ToString())
{
InnerPage = true
};
return(commandData);
}
}
return(base.CreateAggregateTableCommand(mapping, selector, groupBy, query, having, order, region, state));
}
public override CommandData CreateSelectInsertCommand(InsertSelector insertSelector, DataEntityMapping mapping,
AggregateSelector selector, AggregateGroupBy groupBy, QueryExpression query, QueryExpression having,
OrderExpression order, CreateSqlState state)
{
var selectCommandData =
CreateAggregateTableCommand(mapping, selector, groupBy, query, having, null, null, state);
var insertFields = insertSelector.GetInsertFields();
var insertFieldNames = new string[insertFields.Length];
for (var i = 0; i < insertFields.Length; i++)
{
insertFieldNames[i] = CreateDataFieldSql(insertFields[i].FieldName);
}
var insertString = string.Join(",", insertFieldNames);
var selectString = insertSelector.CreateSelectString(this, false, state);
var sql =
$"insert into {CreateDataTableMappingSql(insertSelector.InsertMapping, state)}({insertString})select {selectString} from ({selectCommandData.CommandText}) as A";
if (order != null)
{
state.UseFieldAlias = true;
sql += GetOrderString(order, false, state);
state.UseFieldAlias = false;
}
selectCommandData.CommandText = sql;
return(selectCommandData);
}
public override CommandData CreateAggregateTableCommand(DataEntityMapping mapping, AggregateSelector selector, AggregateGroupBy groupBy, QueryExpression query, QueryExpression having, OrderExpression order, Region region, CreateSqlState state)
{
if (region != null)
{
if (region.Start == 0)
{
StringBuilder sql = new StringBuilder();
string selectString = selector.CreateSelectString(this, false, state);
sql.AppendFormat("select top {2} {0} from {1}", selectString, CreateDataTableMappingSql(mapping, state), region.Size);
if (query != null)
{
sql.Append(GetQueryString(query, false, state));
}
if (groupBy != null)
{
sql.Append(GetGroupByString(groupBy, false, state));
}
if (having != null)
{
sql.Append(GetHavingString(having, false, state));
}
if (order != null)
{
sql.Append(GetAggregateOrderString(order, false, state));
}
CommandData command = new CommandData(sql.ToString());
command.InnerPage = true;
return(command);
}
else
{
if (order == null)
{
order = CreateGroupByOrderExpression(groupBy);
}
if (order != null)
{
string rname = CreateDataFieldSql("R" + Guid.NewGuid().ToString("N"));
StringBuilder sql = new StringBuilder();
string selectString = selector.CreateSelectString(this, false, state);
sql.Append("select * from (");
long totalsize = region.Start + (long)region.Size;
sql.AppendFormat("select top {4} {0},row_number() over (order by {2}) as {3} from {1}", selectString, CreateDataTableMappingSql(mapping, state), order.CreateSqlString(this, false, state), rname, totalsize);
if (query != null)
{
sql.Append(GetQueryString(query, false, state));
}
if (groupBy != null)
{
sql.Append(GetGroupByString(groupBy, false, state));
}
if (having != null)
{
sql.Append(GetHavingString(having, false, state));
}
sql.AppendFormat(") as RT where {0}>{1}", rname, region.Start);
CommandData commandData = new CommandData(sql.ToString());
commandData.InnerPage = true;
return(commandData);
}
}
}
return(base.CreateAggregateTableCommand(mapping, selector, groupBy, query, having, order, region, state));
}
