private long GetCount(SqlTransformContext context) { List <dynamic> sqlItems = new List <dynamic>(); using (var connection = _sourceSqlDatabase.CreateConnection()) { return(connection.ExecuteScalar <long>(context.CountSql)); } }
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); }