private async Task <List <TablePrimaryKey> > GetTablePrimaryKeysAsync(string tableName)
{
var primaryKeys = new List <TablePrimaryKey>();
using (var conn = await GetOpenConnectionAsync())
{
using (var command = conn.CreateCommand())
{
command.CommandText = TableSchemaQueryBuilder.GetPrimaryKeyColumnsOfTableQuery();
command.CommandTimeout = 30;
command.Parameters.Add("TableName", SqlDbType.VarChar).Value = tableName;
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var pk = GetPrimaryKey(reader);
primaryKeys.Add(pk);
}
}
}
}
return(primaryKeys);
}
private async Task <List <TableColumn> > GetTableColumnsAsync(string tableName)
{
var columns = new List <TableColumn>();
using (var conn = await GetOpenConnectionAsync())
{
using (var command = conn.CreateCommand())
{
command.CommandText = TableSchemaQueryBuilder.GetColumnsOfTableQuery();
command.CommandTimeout = 30;
command.Parameters.Add("TableName", SqlDbType.VarChar).Value = tableName;
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var column = GetTableColumn(reader);
if (column.ColumnName.IndexOf("msrepl", StringComparison.OrdinalIgnoreCase) == -1)
{
columns.Add(column);
}
}
}
}
}
return(columns);
}
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);
}
