Exemple #1
0
        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;
                        }
                    }
                }
            }
        }
Exemple #4
0
        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);
            }
        }
Exemple #6
0
 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);
 }
Exemple #7
0
 public static void Read(DbContext context, Type type, IList <object> entities, TableInfo tableInfo, Action <decimal> progress)
 {
     Read <object>(context, type, entities, tableInfo, progress);
 }
Exemple #8
0
        public static void Truncate(DbContext context, TableInfo tableInfo)
        {
            var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);

            adapter.Truncate(context, tableInfo);
        }
Exemple #9
0
 public static async Task TruncateAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken)
 {
     var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);
     await adapter.TruncateAsync(context, tableInfo);
 }
Exemple #10
0
        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);
        }
Exemple #11
0
        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);
                }
            }
        }
Exemple #15
0
 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);
 }
Exemple #16
0
 public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
 {
     Insert <T>(context, typeof(T), entities, tableInfo, progress);
 }
Exemple #17
0
 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);
 }
Exemple #18
0
        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);
        }
Exemple #19
0
 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);
 }
Exemple #20
0
 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);
 }
Exemple #21
0
        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}");
        }
Exemple #22
0
 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);
 }
Exemple #23
0
        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));
        }
Exemple #24
0
 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();
                }
            }
        }
Exemple #26
0
 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);
        }