private async Task <Dictionary <long, List <Dictionary <string, object> > > > GetPagedDataListAsync(DbConnection connection, Table table, List <TableColumn> columns, string primaryKeyColumns, long total, int pageSize, string whereClause = "")
{
string quotedTableName = this.GetQuotedObjectName(table);
string columnNames = this.GetQuotedColumnNames(columns);
var dictPagedData = new Dictionary <long, List <Dictionary <string, object> > >();
long pageCount = PaginationHelper.GetPageCount(total, pageSize);
for (long pageNumber = 1; pageNumber <= pageCount; pageNumber++)
{
if (this.CancelRequested)
{
break;
}
string pagedSql = this.GetPagedSql(quotedTableName, columnNames, primaryKeyColumns, whereClause, pageNumber, pageSize);
var dataTable = await this.GetDataTableAsync(connection, pagedSql);
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
foreach (DataRow row in dataTable.Rows)
{
var dicField = new Dictionary <string, object>();
for (var i = 0; i < dataTable.Columns.Count; i++)
{
DataColumn column = dataTable.Columns[i];
string columnName = column.ColumnName;
if (columnName == RowNumberColumnName)
{
continue;
}
TableColumn tableColumn = columns.FirstOrDefault(item => item.Name == columnName);
object value = row[i];
if (this.IsBytes(value) && this.Option.TreatBytesAsNullForData)
{
value = null;
}
object newValue = this.GetInsertValue(tableColumn, value);
dicField.Add(columnName, newValue);
}
rows.Add(dicField);
}
dictPagedData.Add(pageNumber, rows);
if (this.OnDataRead != null && !this.CancelRequested && !this.hasError)
{
await this.OnDataRead(table, columns, rows, dataTable);
}
}
return(dictPagedData);
}
protected override string GetSqlForPagination(string tableName, string columnNames, string orderColumns, string whereClause, long pageNumber, int pageSize)
{
var startEndRowNumber = PaginationHelper.GetStartEndRowNumber(pageNumber, pageSize);
var pagedSql = $@"SELECT {columnNames}
FROM {tableName}
{whereClause}
ORDER BY {(!string.IsNullOrEmpty(orderColumns) ? orderColumns : this.GetDefaultOrder())}
LIMIT { startEndRowNumber.StartRowNumber - 1 } , {pageSize}";
return(pagedSql);
}
protected override string GetPagedSql(string tableName, string columnNames, string primaryKeyColumns, string whereClause, long pageNumber, int pageSize)
{
var startEndRowNumber = PaginationHelper.GetStartEndRowNumber(pageNumber, pageSize);
var pagedSql = $@"SELECT {columnNames}
FROM {tableName}
{whereClause}
ORDER BY {(!string.IsNullOrEmpty(primaryKeyColumns) ? primaryKeyColumns : "1")}
LIMIT { startEndRowNumber.StartRowNumber - 1 } , {pageSize}";
return(pagedSql);
}
protected override string GetPagedSql(string tableName, string columnNames, string primaryKeyColumns, string whereClause, long pageNumber, int pageSize)
{
var startEndRowNumber = PaginationHelper.GetStartEndRowNumber(pageNumber, pageSize);
string pagedSql = $@"with PagedRecords as
(
SELECT TOP 100 PERCENT {columnNames}, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROWNUMBER
FROM {tableName}
{whereClause}
)
SELECT *
FROM PagedRecords
WHERE ROWNUMBER BETWEEN {startEndRowNumber.StartRowNumber} AND {startEndRowNumber.EndRowNumber}" ;
return(pagedSql);
}
private async Task <Dictionary <long, List <Dictionary <string, object> > > > GetSortedPageData(DbConnection connection, Table table, string primaryKeyColumns, string parentColumnName, List <TableColumn> columns, string whereClause = "")
{
string quotedTableName = this.GetQuotedObjectName(table);
int pageSize = this.dbInterpreter.DataBatchSize;
long total = Convert.ToInt64(await this.dbInterpreter.GetScalarAsync(connection, $"SELECT COUNT(1) FROM {quotedTableName} {whereClause}"));
var dictPagedData = await this.dbInterpreter.GetPagedDataListAsync(connection, table, columns, primaryKeyColumns, total, pageSize, whereClause);
List <object> parentValues = dictPagedData.Values.SelectMany(item => item.Select(t => t[primaryKeyColumns.Trim(this.dbInterpreter.QuotationLeftChar, this.dbInterpreter.QuotationRightChar)])).ToList();
if (parentValues.Count > 0)
{
TableColumn parentColumn = columns.FirstOrDefault(item => item.Owner == table.Owner && item.Name == parentColumnName);
long parentValuesPageCount = PaginationHelper.GetPageCount(parentValues.Count, this.option.InQueryItemLimitCount);
for (long parentValuePageNumber = 1; parentValuePageNumber <= parentValuesPageCount; parentValuePageNumber++)
{
IEnumerable <object> pagedParentValues = parentValues.Skip((int)(parentValuePageNumber - 1) * pageSize).Take(this.option.InQueryItemLimitCount);
whereClause = $" WHERE { this.GetQuotedString(parentColumnName)} IN ({string.Join(",", pagedParentValues.Select(item => this.ParseValue(parentColumn, item, true)))})";
total = Convert.ToInt64(await this.dbInterpreter.GetScalarAsync(connection, $"SELECT COUNT(1) FROM {quotedTableName} {whereClause}"));
if (total > 0)
{
Dictionary <long, List <Dictionary <string, object> > > dictChildPagedData = await this.GetSortedPageData(connection, table, primaryKeyColumns, parentColumnName, columns, whereClause);
foreach (var kp in dictChildPagedData)
{
long pageNumber = dictPagedData.Keys.Max(item => item);
dictPagedData.Add(pageNumber + 1, kp.Value);
}
}
}
}
return(dictPagedData);
}
public async Task <Dictionary <long, List <Dictionary <string, object> > > > GetPagedDataListAsync(DbConnection connection, Table table, List <TableColumn> columns, string primaryKeyColumns, long total, int pageSize, string whereClause = "")
{
var dictPagedData = new Dictionary <long, List <Dictionary <string, object> > >();
long pageCount = PaginationHelper.GetPageCount(total, pageSize);
for (long pageNumber = 1; pageNumber <= pageCount; pageNumber++)
{
if (this.CancelRequested)
{
break;
}
DataTable dataTable = await this.GetPagedDataTableAsync(connection, table, columns, primaryKeyColumns, total, pageSize, pageNumber, whereClause);
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
foreach (DataRow row in dataTable.Rows)
{
var dicField = new Dictionary <string, object>();
for (var i = 0; i < dataTable.Columns.Count; i++)
{
DataColumn column = dataTable.Columns[i];
string columnName = column.ColumnName;
if (columnName == DbInterpreter.RowNumberColumnName)
{
continue;
}
TableColumn tableColumn = columns.FirstOrDefault(item => item.Name == columnName);
object value = row[i];
if (ValueHelper.IsBytes(value))
{
if (this.Option.TreatBytesAsNullForReading)
{
if (!(((Byte[])value).Length == 16) && this.DatabaseType == DatabaseType.Oracle)
{
value = null;
if (dataTable.Columns[i].ReadOnly)
{
dataTable.Columns[i].ReadOnly = false;
}
row[i] = null;
}
}
}
dicField.Add(columnName, value);
}
rows.Add(dicField);
}
dictPagedData.Add(pageNumber, rows);
if (this.OnDataRead != null && !this.CancelRequested && !this.HasError)
{
await this.OnDataRead(new TableDataReadInfo()
{
Table = table,
Columns = columns,
TotalCount = total,
Data = rows,
DataTable = dataTable
});
}
}
return(dictPagedData);
}