public static object GetLatestId(this DbConnectionFactory <SqlConnection> targetDatabase, SqlTransformContext context)
{
var predicateSql = $"SELECT {context.IdentityColumnName} FROM {context.SqlTableNameDialect()} ORDER BY {context.IdentityColumnName} DESC";
long lastId = 0;
using (var connection = targetDatabase.CreateConnection())
{
lastId = connection.ExecuteScalar <long>(predicateSql);
}
return(lastId);
}
private async Task <int> TokenizeLoopAsync(SqlTransformContext context)
{
int take = context.BundleSize;
object indexId = context.LastIndexId;
int totalIndices = 0;
var identityColumnName = context.IdentityColumnName;
IDictionary <string, TableColumnMetadata> columns = new Dictionary <string, TableColumnMetadata>();
using (DbConnection connection = _sourceSqlDatabase.CreateConnection())
{
string targetDatabaseName = _targetSqlConnectionBuilder.InitialCatalog;
var tableName = context.TableName;
if (tableName.Contains("."))
{
tableName = tableName.Split('.').Last();
}
var sqlTableInfo = @"SELECT
c.name as Name,
t.Name as DataType,
c.max_length as MaxLength,
c.is_nullable as Nullable,
ISNULL(i.is_primary_key, 0) as IsPrimaryKey
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID(@tableName)";
columns = connection.Query <TableColumnMetadata>(sqlTableInfo, new { tableName }).ToDictionary(x => x.Name, x => x);
}
string columnsOrder = context.FieldPattern == "*" ?
string.Join(",", columns.Keys.OrderBy(x => x).Select(x => $"[{x}]")) :
context.FieldPattern;
do
{
if (context.CancellationToken.IsCancellationRequested)
{
break;
}
var predicateSql = $"SELECT TOP {take} {columnsOrder} FROM {context.SqlTableNameDialect()} " +
$"WHERE {identityColumnName} > {indexId} ORDER BY {identityColumnName} ASC";
IEnumerable <IDictionary <string, object> > sourceValues = Enumerable.Empty <Dictionary <string, object> >();
using (var connection = _sourceSqlDatabase.CreateConnection())
{
sourceValues = connection.Query(predicateSql).Select(x => (IDictionary <string, object>)x).ToList();
}
var itemsCount = sourceValues.Count();
totalIndices += itemsCount;
var tableName = context.SqlTableNameDialect();
DataTable dataTable = new DataTable(tableName);
foreach (KeyValuePair <string, TableColumnMetadata> entry in columns)
{
TableColumnMetadata metadata = entry.Value;
var column = dataTable.Columns.Add();
column.ColumnName = metadata.Name;
column.AllowDBNull = metadata.Nullable;
column.DataType = metadata.ClrType;
}
foreach (IDictionary <string, object> entry in sourceValues)
{
DataRow row = dataTable.NewRow();
foreach (KeyValuePair <string, object> item in entry)
{
if (!columns.TryGetValue(item.Key, out TableColumnMetadata metadata))
{
throw new InvalidOperationException("Unmatch field!");
}
var value = item.Value;
row[item.Key] = value == null ? (object)DBNull.Value : value;
}
dataTable.Rows.Add(row);
}
if (itemsCount > 0)
{
using (var connection = _targetSqlDatabase.CreateConnection())
{
DataRow lastRow = dataTable.Rows[itemsCount - 1];
indexId = lastRow[identityColumnName];
using (var targetConnection = _targetSqlDatabase.CreateConnection())
{
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(targetConnection.ConnectionString, SqlBulkCopyOptions.KeepIdentity);
sqlBulkCopy.DestinationTableName = tableName;
sqlBulkCopy.SqlRowsCopied += SqlBulkCopySqlRowsCopied;
await sqlBulkCopy.WriteToServerAsync(dataTable);
sqlBulkCopy.Close();
dataTable.Clear();
}
}
await _connectionManager.WsLogAsync($"Table: {context.TableName} total: {totalIndices} record(s) progressed.");
}
if (totalIndices == 0)
{
break;
}
} while (totalIndices < context.Count);
return(totalIndices);
}
