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