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