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)); }