private static void Merge <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); adapter.Merge(context, type, entities, tableInfo, operationType, progress); }
public static string SelectCountIsUpdateFromOutputTable(TableInfo tableInfo) { var q = $"SELECT COUNT(*) FROM {tableInfo.FullTempOutputTableName} WHERE [IsUpdate] = 1"; return(q); }
protected void CheckAndSetParametesForConvertibles(IEnumerable <object> innerParameters, TableInfo tableInfo) // fix for enum 'int' Conversion to nvarchar { foreach (var innerParameter in innerParameters) { string parameterColumnName = ((Microsoft.Data.SqlClient.SqlParameter)innerParameter).ParameterName.Replace("@__", ""); // @__column_N.. parameterColumnName = parameterColumnName.Contains("_") ? parameterColumnName.Substring(0, parameterColumnName.IndexOf("_")) : parameterColumnName; // column parameterColumnName = parameterColumnName.ToLower(); foreach (var convertibleProperty in tableInfo.ConvertibleProperties) { if (convertibleProperty.Key.ToLower() == parameterColumnName) { if (convertibleProperty.Value.ProviderClrType.Name == nameof(String)) { ((Microsoft.Data.SqlClient.SqlParameter)innerParameter).DbType = System.Data.DbType.String; } } } } }
public static string GetSqlSetSegment <T>(DbContext context, T updateValues, List <string> updateColumns, List <object> parameters) where T : class, new() { var tableInfo = TableInfo.CreateInstance <T>(context, new List <T>(), OperationType.Read, new BulkConfig()); return(GetSqlSetSegment(context, tableInfo, typeof(T), updateValues, new T(), updateColumns, parameters)); }
public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class { tableInfo.InsertToTempTable = true; await tableInfo.CheckHasIdentityAsync(context).ConfigureAwait(false); await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)).ConfigureAwait(false); if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity) { await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true)).ConfigureAwait(false); } try { await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false); await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false); if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity) { try { await tableInfo.UpdateOutputIdentityAsync(context, entities).ConfigureAwait(false); } finally { await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false); } } } finally { await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false); } }
private static async Task MergeAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) where T : class { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); await adapter.MergeAsync(context, type, entities, tableInfo, operationType, progress, cancellationToken); }
public static void Read(DbContext context, Type type, IList <object> entities, TableInfo tableInfo, Action <decimal> progress) { Read <object>(context, type, entities, tableInfo, progress); }
public static void Truncate(DbContext context, TableInfo tableInfo) { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); adapter.Truncate(context, tableInfo); }
public static async Task TruncateAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken) { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); await adapter.TruncateAsync(context, tableInfo); }
private static void Read <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class { var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB; if (dropTempTableIfExists) { context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB)); } var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); adapter.Read(context, type, entities, tableInfo, progress); }
private static async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken) where T : class { var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB; if (dropTempTableIfExists) { await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false); } var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); await adapter.ReadAsync(context, type, entities, tableInfo, progress, cancellationToken); }
public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) { var sqlConnection = await OpenAndGetSqlConnectionAsync(context); var transaction = context.Database.CurrentTransaction; try { using (var sqlBulkCopy = GetSqlBulkCopy(sqlConnection, transaction, tableInfo.BulkConfig.SqlBulkCopyOptions)) { bool setColumnMapping = !tableInfo.HasOwnedTypes; tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress); try { if (!tableInfo.HasOwnedTypes) { using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray())) { await sqlBulkCopy.WriteToServerAsync(reader).ConfigureAwait(false); } } else { var dataTable = GetDataTable <T>(context, entities, sqlBulkCopy); await sqlBulkCopy.WriteToServerAsync(dataTable); } } catch (InvalidOperationException ex) { if (!tableInfo.BulkConfig.UseTempDB) { await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)); } if (ex.Message.Contains(ColumnMappingExceptionMessage)) { await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)); } throw ex; } } } finally { if (transaction == null) { sqlConnection.Close(); } } }
public static async Task ReadAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class { Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context); await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)); try { await InsertAsync(context, entities, tableInfo, progress); tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict; var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo); //var existingEntities = await context.Set<T>().FromSql(sqlQuery).ToListAsync(); Expression <Func <DbContext, IQueryable <T> > > expression = null; if (tableInfo.BulkConfig.TrackingEntities) { expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery); } else { expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery).AsNoTracking(); } var compiled = EF.CompileAsyncQuery(expression); var existingEntities = (await compiled(context).ToListAsync().ConfigureAwait(false)); tableInfo.UpdateReadEntities(entities, existingEntities); } finally { if (!tableInfo.BulkConfig.UseTempDB) { await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)); } } }
public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class { tableInfo.InsertToTempTable = true; await tableInfo.CheckHasIdentityAsync(context).ConfigureAwait(false); await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)).ConfigureAwait(false); if (tableInfo.CreatedOutputTable) { await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true)).ConfigureAwait(false); if (tableInfo.TimeStampColumnName != null) { context.Database.ExecuteSqlCommand(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType)); } } try { await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false); await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false); if (tableInfo.CreatedOutputTable) { try { await tableInfo.LoadOutputDataAsync(context, entities).ConfigureAwait(false); } finally { if (!tableInfo.BulkConfig.UseTempDB) { await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false); } } } } finally { if (!tableInfo.BulkConfig.UseTempDB) { await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false); } } }
public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) where T : class { await MergeAsync <T>(context, typeof(T), entities, tableInfo, operationType, progress, cancellationToken).ConfigureAwait(false); }
public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) { Insert <T>(context, typeof(T), entities, tableInfo, progress); }
public static async Task MergeAsync(DbContext context, Type type, IList <object> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) { await MergeAsync <object>(context, type, entities, tableInfo, operationType, progress, cancellationToken).ConfigureAwait(false); }
private static void Insert <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); adapter.Insert(context, type, entities, tableInfo, progress); }
public static void Read <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class { Read <T>(context, typeof(T), entities, tableInfo, progress); }
public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken) { await InsertAsync <T>(context, typeof(T), entities, tableInfo, progress, cancellationToken).ConfigureAwait(false); }
private static void AddSqlParameter(StringBuilder sqlColumns, List <object> sqlParameters, TableInfo tableInfo, string columnName, object value) { var parmName = $"param_{sqlParameters.Count}"; if (columnName != null && tableInfo.ConvertibleProperties.TryGetValue(columnName, out var valueConverter)) { value = valueConverter.ConvertToProvider.Invoke(value); } // will rely on SqlClientHelper.CorrectParameterType to fix the type before executing sqlParameters.Add(new Microsoft.Data.SqlClient.SqlParameter(parmName, value ?? DBNull.Value)); sqlColumns.Append($" @{parmName}"); }
private static async Task InsertAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken) { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); await adapter.InsertAsync(context, type, entities, tableInfo, progress, cancellationToken); }
public static string GetSqlSetSegment(DbContext context, Type updateValuesType, object updateValues, List <string> updateColumns, List <object> parameters) { var tableInfo = TableInfo.CreateInstance(context, updateValuesType, new List <object>(), OperationType.Read, new BulkConfig()); return(GetSqlSetSegment(context, tableInfo, updateValuesType, updateValues, Activator.CreateInstance(updateValuesType), updateColumns, parameters)); }
public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class { Merge <T>(context, typeof(T), entities, tableInfo, operationType, progress); }
public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) { var sqlConnection = await OpenAndGetSqlConnectionAsync(context); var transaction = context.Database.CurrentTransaction; try { using (var sqlBulkCopy = GetSqlBulkCopy(sqlConnection, transaction, tableInfo.BulkConfig.SqlBulkCopyOptions)) { bool setColumnMapping = !tableInfo.HasOwnedTypes; tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress); if (!tableInfo.HasOwnedTypes) { using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray())) { await sqlBulkCopy.WriteToServerAsync(reader).ConfigureAwait(false); } } else { var dataTable = GetDataTable <T>(context, entities); await sqlBulkCopy.WriteToServerAsync(dataTable); } } } finally { if (transaction == null) { sqlConnection.Close(); } } }
public static void Merge(DbContext context, Type type, IList <object> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) { Merge <object>(context, type, entities, tableInfo, operationType, progress); }
public static string CreateTableCopy(string existingTableName, string newTableName, TableInfo tableInfo, bool isOutputTable = false) { // TODO: (optionaly) if CalculateStats = True but SetOutputIdentity = False then Columns could be ommited from Create and from MergeOutput List <string> columnsNames = (isOutputTable ? tableInfo.OutputPropertyColumnNamesDict : tableInfo.PropertyColumnNamesDict).Values.ToList(); if (tableInfo.TimeStampColumnName != null) { columnsNames.Remove(tableInfo.TimeStampColumnName); } string isUpdateStatsColumn = (tableInfo.BulkConfig.CalculateStats && isOutputTable) ? ",[IsUpdate] = CAST(0 AS bit)" : ""; var q = $"SELECT TOP 0 {GetCommaSeparatedColumns(columnsNames, "T")} " + isUpdateStatsColumn + $"INTO {newTableName} FROM {existingTableName} AS T " + $"LEFT JOIN {existingTableName} AS Source ON 1 = 0;"; // removes Identity constrain return(q); }
internal static DataTable GetDataTable <T>(DbContext context, IList <T> entities, SqlBulkCopy sqlBulkCopy, TableInfo tableInfo) { var dataTable = new DataTable(); var columnsDict = new Dictionary <string, object>(); var ownedEntitiesMappedProperties = new HashSet <string>(); var type = typeof(T); var entityType = context.Model.FindEntityType(type); var entityPropertiesDict = entityType.GetProperties().Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name)).ToDictionary(a => a.Name, a => a); var entityNavigationOwnedDict = entityType.GetNavigations().Where(a => a.GetTargetType().IsOwned()).ToDictionary(a => a.Name, a => a); var properties = type.GetProperties(); var discriminatorColumn = tableInfo.ShadowProperties.Count == 0 ? null : tableInfo.ShadowProperties.ElementAt(0); foreach (var property in properties) { if (entityPropertiesDict.ContainsKey(property.Name)) { var relational = entityPropertiesDict[property.Name].Relational(); string columnName = relational.ColumnName; var propertyType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType; if (propertyType.IsEnum && relational.ColumnType.StartsWith("nvarchar")) // temporary fix for EnumToStringConverter when DataTables are used { // TODO replace with checking somehow for DataConverters (not found in Relational) propertyType = typeof(string); } dataTable.Columns.Add(columnName, propertyType); columnsDict.Add(property.Name, null); } else if (entityNavigationOwnedDict.ContainsKey(property.Name)) // isOWned { Type navOwnedType = type.Assembly.GetType(property.PropertyType.FullName); var ownedEntityType = context.Model.FindEntityType(property.PropertyType); if (ownedEntityType == null) { ownedEntityType = context.Model.GetEntityTypes().SingleOrDefault(a => a.DefiningNavigationName == property.Name && a.DefiningEntityType.Name == entityType.Name); } var ownedEntityProperties = ownedEntityType.GetProperties().ToList(); var ownedEntityPropertyNameColumnNameDict = new Dictionary <string, string>(); foreach (var ownedEntityProperty in ownedEntityProperties) { if (!ownedEntityProperty.IsPrimaryKey()) { string columnName = ownedEntityProperty.Relational().ColumnName; ownedEntityPropertyNameColumnNameDict.Add(ownedEntityProperty.Name, columnName); ownedEntitiesMappedProperties.Add(property.Name + "_" + ownedEntityProperty.Name); } } var innerProperties = property.PropertyType.GetProperties(); foreach (var innerProperty in innerProperties) { if (ownedEntityPropertyNameColumnNameDict.ContainsKey(innerProperty.Name)) { string columnName = ownedEntityPropertyNameColumnNameDict[innerProperty.Name]; var ownedPropertyType = Nullable.GetUnderlyingType(innerProperty.PropertyType) ?? innerProperty.PropertyType; dataTable.Columns.Add(columnName, ownedPropertyType); columnsDict.Add(property.Name + "_" + innerProperty.Name, null); } } } } if (discriminatorColumn != null) { dataTable.Columns.Add(discriminatorColumn, typeof(string)); columnsDict.Add(discriminatorColumn, typeof(T).Name); } foreach (var entity in entities) { foreach (var property in properties) { var propertyValue = property.GetValue(entity, null); if (entityPropertiesDict.ContainsKey(property.Name)) { columnsDict[property.Name] = propertyValue; } else if (entityNavigationOwnedDict.ContainsKey(property.Name)) { var ownedProperties = property.PropertyType.GetProperties().Where(a => ownedEntitiesMappedProperties.Contains(property.Name + "_" + a.Name)); foreach (var ownedProperty in ownedProperties) { columnsDict[property.Name + "_" + ownedProperty.Name] = propertyValue == null ? null : ownedProperty.GetValue(propertyValue, null); } } } var record = columnsDict.Values.ToArray(); dataTable.Rows.Add(record); } foreach (DataColumn item in dataTable.Columns) //Add mapping { sqlBulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName); } return(dataTable); }