private async Task <Dictionary <long, List <Dictionary <string, object> > > > InteralGetPagedDataList(DbConnection connection, Table table, List <TableColumn> columns, string primaryKeyColumns, long total, int pageSize, string whereClause = "", bool async = false)
{
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++)
{
string pagedSql = this.GetPagedSql(quotedTableName, columnNames, primaryKeyColumns, whereClause, pageNumber, pageSize);
var dataTable = async ? await this.GetDataTableAsync(connection, pagedSql) : this.GetDataTable(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.ColumnName == 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.FeedbackInfo($"Transfer data from table {table.Name}, rows:{rows.Count}.");
this.OnDataRead(table, columns, rows, dataTable);
}
}
return(dictPagedData);
}
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 {columnNames}, ROW_NUMBER() OVER (ORDER BY (SELECT 0 FROM DUAL)) AS ROWNUMBER
FROM {tableName}
{whereClause}
)
SELECT *
FROM PagedRecords
WHERE ROWNUMBER BETWEEN {startEndRowNumber.StartRowNumber} AND {startEndRowNumber.EndRowNumber}" ;
return(pagedSql);
}
private Dictionary <long, List <Dictionary <string, object> > > GetSortedPageDatas(DbConnection connection, Table table, string primaryKeyColumns, string parentColumnName, List <TableColumn> columns, GenerateScriptOption option, string whereClause = "")
{
string quotedTableName = this.GetQuotedObjectName(table);
int pageSize = option.DataBatchSize;
long total = Convert.ToInt64(this.GetScalar(connection, $"SELECT COUNT(1) FROM {quotedTableName} {whereClause}"));
var dictPagedData = this.GetPagedDataList(connection, table, columns, primaryKeyColumns, total, pageSize, whereClause);
List <object> parentValues = dictPagedData.Values.SelectMany(item => item.Select(t => t[primaryKeyColumns.Trim(QuotationLeftChar, QuotationRightChar)])).ToList();
if (parentValues.Count > 0)
{
TableColumn parentColumn = columns.FirstOrDefault(item => item.Owner == table.Owner && item.ColumnName == parentColumnName);
long parentValuesPageCount = PaginationHelper.GetPageCount(parentValues.Count, option.InQueryItemLimitCount);
for (long parentValuePageNumber = 1; parentValuePageNumber <= parentValuesPageCount; parentValuePageNumber++)
{
IEnumerable <object> pagedParentValues = parentValues.Skip((int)(parentValuePageNumber - 1) * pageSize).Take(option.InQueryItemLimitCount);
whereClause = $" WHERE {GetQuotedString(parentColumnName)} IN ({string.Join(",", pagedParentValues.Select(item => SelectDataScriptValue(parentColumn, item, true)))})";
total = Convert.ToInt64(this.GetScalar(connection, $"SELECT COUNT(1) FROM {quotedTableName} {whereClause}"));
if (total > 0)
{
Dictionary <long, List <Dictionary <string, object> > > dictChildPagedData = this.GetSortedPageDatas(connection, table, primaryKeyColumns, parentColumnName, columns, option, whereClause);
foreach (var kp in dictChildPagedData)
{
long pageNumber = dictPagedData.Keys.Max(item => item);
dictPagedData.Add(pageNumber + 1, kp.Value);
}
}
}
}
return(dictPagedData);
}
