private PrimaryKeyValue GetKey(FullLoadRecord record, TableSchema tableSchema)
{
var pkVal = new PrimaryKeyValue();
foreach (var pkCol in tableSchema.PrimaryKeys)
{
pkVal.AddKeyValue(pkCol.OrdinalPosition, pkCol.ColumnName, record.Data[pkCol.ColumnName]);
}
return(pkVal);
}
public async Task <FullLoadBatch> GetBatchAsync(TableSchema tableSchema, PrimaryKeyValue lastRetrievedKey, int batchSize)
{
var batch = new FullLoadBatch();
batch.TableSchema = tableSchema;
using (var conn = await GetOpenConnectionAsync())
{
var command = conn.CreateCommand();
command.CommandText = TableSchemaQueryBuilder.GetExtractQueryUsingAllKeys(tableSchema, batchSize);
foreach (var pk in tableSchema.PrimaryKeys.OrderBy(x => x.OrdinalPosition))
{
var columnSchema = tableSchema.GetColumn(pk.ColumnName);
var value = lastRetrievedKey.GetValue(pk.OrdinalPosition);
command.Parameters.Add(CreateSqlParameter(columnSchema, "@p" + pk.OrdinalPosition, value));
}
using (var reader = await command.ExecuteReaderAsync())
{
int ctr = 1;
while (await reader.ReadAsync())
{
var change = new FullLoadRecord();
change.ChangeKey = GetRecordId(reader, tableSchema);
change.BatchSeqNo = ctr;
foreach (var column in tableSchema.Columns)
{
change.Data.Add(column.Name, reader[column.Name]);
}
batch.Records.Add(change);
ctr++;
}
}
}
if (batch.Records.Any())
{
batch.FirstRowKey = GetKey(batch.Records.First(), tableSchema);
batch.LastRowKey = GetKey(batch.Records.Last(), tableSchema);
}
return(batch);
}
public async Task <FullLoadBatch> GetFirstBatchAsync(TableSchema tableSchema, int batchSize)
{
var batch = new FullLoadBatch();
batch.TableSchema = tableSchema;
using (var conn = await GetOpenConnectionAsync())
{
var command = conn.CreateCommand();
command.CommandText = $"SELECT TOP {batchSize} * FROM {tableSchema.Schema}.{tableSchema.TableName} ORDER BY {tableSchema.GetOrderedPrimaryKeyColumns()};";
using (var reader = await command.ExecuteReaderAsync())
{
int ctr = 1;
while (await reader.ReadAsync())
{
var change = new FullLoadRecord();
change.ChangeKey = GetRecordId(reader, tableSchema);
change.BatchSeqNo = ctr;
foreach (var column in tableSchema.Columns)
{
change.Data.Add(column.Name, reader[column.Name]);
}
batch.Records.Add(change);
ctr++;
}
}
}
if (batch.Records.Any())
{
batch.FirstRowKey = GetKey(batch.Records.First(), tableSchema);
batch.LastRowKey = GetKey(batch.Records.Last(), tableSchema);
}
return(batch);
}