private int LoadData(Select select, Action <DataTable> onChunkLoaded, string selectExpression = null) { select.CheckArgumentNull("selectQuery"); onChunkLoaded.CheckArgumentNull("onChunkLoaded"); int chunkSize = _config.ChunkSize; int maxRecordsCount = _config.MaxRecordsCount; int minRecordsCount = _config.MinRecordsCount; QueryColumnExpression idExpression = GetSelectIdColumnExpression(select, selectExpression); var lastValueParameter = new QueryParameter("IdLastValue", null); var pageableSelectCondition = new PageableSelectCondition { LastValueParameter = lastValueParameter, LeftExpression = idExpression, OrderByItem = new OrderByItem(idExpression, OrderDirectionStrict.Ascending) }; var options = new PageableSelectOptions(pageableSelectCondition, chunkSize, PageableSelectDirection.First); Select pageableSelect = select.ToPageable(options); bool hasRecords = true; int totalRecordsCount = 0; bool useMaxRecordsLimit = maxRecordsCount > 0; using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection(QueryKind.Limited)) { while (hasRecords && (!useMaxRecordsLimit || totalRecordsCount < maxRecordsCount)) { hasRecords = false; using (IDataReader dr = pageableSelect.ExecuteReader(dbExecutor)) { var dataTable = new DataTable(); dataTable.Load(dr); int recordsCount = dataTable.Rows.Count; totalRecordsCount += recordsCount; if (totalRecordsCount < minRecordsCount && recordsCount < chunkSize) { throw new NotEnoughDataForTrainingException( $"There's not enough data for upload. Minimal number: {minRecordsCount}," + $" Current: {totalRecordsCount}"); } if (recordsCount == 0) { continue; } hasRecords = true; if (useMaxRecordsLimit && totalRecordsCount >= maxRecordsCount) { SendNotification("LocalizableStrings.RecordCountExceedsLimitMessage.Value"); } DataRow lastRow = dataTable.Rows[recordsCount - 1]; string rowId = lastRow["Id"]?.ToString(); if (string.IsNullOrEmpty(rowId)) { throw new Exception("Rowset can't contain a record with empty Id"); } onChunkLoaded(dataTable); lastValueParameter.Value = new Guid(rowId); options.Direction = PageableSelectDirection.Next; AddOrderByItemsConditions(select, options, lastRow); pageableSelect = select.ToPageable(options); } } } return(totalRecordsCount); }
private static void AddOrderByItemsConditions(Select select, PageableSelectOptions options, DataRow lastRow) { options.OrderByItemsConditions.Clear(); foreach (OrderByItem orderByItem in select.OrderByItems) { string queryColumnAlias = orderByItem.Expression.SourceColumnAlias; QueryColumnExpression column = GetColumnByName(select, queryColumnAlias); column.Alias = queryColumnAlias; object value = lastRow[queryColumnAlias]; QueryParameter orderByParameter = new QueryParameter(queryColumnAlias, value); options.AddCondition(column, orderByItem, orderByParameter); } }
private Select GetPageableSelect(Select select, PageableConfig pageableConfig) { var conditionColumn = select.Columns[0]; var direction = PageableSelectDirection.First; var conditionValue = string.Empty; if (pageableConfig.LastValue.IsNotNullOrEmpty()) { direction = PageableSelectDirection.Next; conditionValue = pageableConfig.LastValue; } var options = new PageableSelectOptions(null, pageableConfig.RowCount, direction); options.AddCondition(conditionColumn, new QueryParameter("columnLastValue", conditionValue)); Select pageableSelect = select.ToPageable(options); return(pageableSelect.Top(pageableConfig.RowCount)); }