Example #1
0
        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))
                {
                    tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, progress);
                    using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
                    {
                        await sqlBulkCopy.WriteToServerAsync(reader).ConfigureAwait(false);
                    }
                }
            }
            finally
            {
                if (transaction == null)
                {
                    sqlConnection.Close();
                }
            }
        }
Example #2
0
        public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
        {
            var sqlConnection = OpenAndGetSqlConnection(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()))
                        {
                            sqlBulkCopy.WriteToServer(reader);
                        }
                    }
                    else // With OwnedTypes DataTable is used since library FastMember can not (https://github.com/mgravell/fast-member/issues/21)
                    {
                        var dataTable = GetDataTable <T>(context, entities);
                        sqlBulkCopy.WriteToServer(dataTable);
                    }
                }
            }
            finally
            {
                if (transaction == null)
                {
                    sqlConnection.Close();
                }
            }
        }
Example #3
0
        public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
        {
            var sqlConnection = await OpenAndGetSqlConnectionAsync(context, tableInfo.BulkConfig);

            var transaction = context.Database.CurrentTransaction;

            try
            {
                using (var sqlBulkCopy = GetSqlBulkCopy(sqlConnection, transaction, tableInfo.BulkConfig))
                {
                    bool useFastMember = tableInfo.HasOwnedTypes == false &&
                                         tableInfo.ShadowProperties.Count == 0 &&
                                         tableInfo.ColumnNameContainsSquareBracket == false;
                    bool setColumnMapping = useFastMember;
                    tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
                    try
                    {
                        if (useFastMember)
                        {
                            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, tableInfo);
                            await sqlBulkCopy.WriteToServerAsync(dataTable);
                        }
                    }
                    catch (InvalidOperationException ex)
                    {
                        if (!tableInfo.BulkConfig.UseTempDB)
                        {
                            await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
                        }
                        if (ex.Message.Contains(ColumnMappingExceptionMessage))
                        {
                            if (!await tableInfo.CheckTableExistAsync(context, tableInfo))
                            {
                                await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));

                                await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
                            }
                        }
                        throw ex;
                    }
                }
            }
            finally
            {
                if (transaction == null)
                {
                    sqlConnection.Close();
                }
            }
        }
Example #4
0
        public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
        {
            var sqlConnection = OpenAndGetSqlConnection(context, tableInfo.BulkConfig);
            var transaction   = context.Database.CurrentTransaction;

            try
            {
                using (var sqlBulkCopy = GetSqlBulkCopy(sqlConnection, transaction, tableInfo.BulkConfig))
                {
                    bool useFastMember = tableInfo.HasOwnedTypes == false &&                    // With OwnedTypes DataTable is used since library FastMember can not (https://github.com/mgravell/fast-member/issues/21)
                                         tableInfo.ShadowProperties.Count == 0 &&               // With Shadow prop. Discriminator (TPH inheritance) also because FastMember is slow for Update (https://github.com/borisdj/EFCore.BulkExtensions/pull/17)
                                         tableInfo.ColumnNameContainsSquareBracket == false;    // FastMember does not support escaped columnNames  ] -> ]]
                    bool setColumnMapping = useFastMember;
                    tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
                    try
                    {
                        if (useFastMember)
                        {
                            using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
                            {
                                sqlBulkCopy.WriteToServer(reader);
                            }
                        }
                        else
                        {
                            var dataTable = GetDataTable <T>(context, entities, sqlBulkCopy, tableInfo);
                            sqlBulkCopy.WriteToServer(dataTable);
                        }
                    }
                    catch (InvalidOperationException ex)
                    {
                        if (!tableInfo.BulkConfig.UseTempDB)
                        {
                            context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
                        }
                        if (ex.Message.Contains(ColumnMappingExceptionMessage))
                        {
                            if (!tableInfo.CheckTableExist(context, tableInfo))
                            {
                                context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)); // Will throw Exception specify missing db column: Invalid column name ''
                                context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
                            }
                        }
                        throw ex;
                    }
                }
            }
            finally
            {
                if (transaction == null)
                {
                    sqlConnection.Close();
                }
            }
        }
Example #5
0
        public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
        {
            var sqlConnection = OpenAndGetSqlConnection(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()))
                            {
                                sqlBulkCopy.WriteToServer(reader);
                            }
                        }
                        else // With OwnedTypes DataTable is used since library FastMember can not (https://github.com/mgravell/fast-member/issues/21)
                        {
                            var dataTable = GetDataTable <T>(context, entities, sqlBulkCopy);
                            sqlBulkCopy.WriteToServer(dataTable);
                        }
                    }
                    catch (InvalidOperationException ex)
                    {
                        if (!ex.Message.Contains(ColumnMappingExceptionMessage))
                        {
                            throw ex;
                        }
                        context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)); // Exception specify missing db column: Invalid column name ''
                        if (!tableInfo.BulkConfig.UseTempDB)
                        {
                            context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
                        }
                        throw ex;
                    }
                }
            }
            finally
            {
                if (transaction == null)
                {
                    sqlConnection.Close();
                }
            }
        }
Example #6
0
        public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
        {
            var sqlConnection = OpenAndGetSqlConnection(context);
            var transaction   = context.Database.CurrentTransaction;

            try
            {
                using (var sqlBulkCopy = GetSqlBulkCopy(sqlConnection, transaction, tableInfo.KeepIdentity))
                {
                    tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, progress);
                    using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
                    {
                        sqlBulkCopy.WriteToServer(reader);
                    }
                }
            }
            finally
            {
                if (transaction == null)
                {
                    sqlConnection.Close();
                }
            }
        }
Example #7
0
        public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
        {
            string providerName = context.Database.ProviderName;             // "Microsoft.EntityFrameworkCore.*****"

            // -- SQL Server --
            if (providerName.EndsWith(DbServer.SqlServer.ToString()))
            {
                var connection  = OpenAndGetSqlConnection(context, tableInfo.BulkConfig);
                var transaction = context.Database.CurrentTransaction;
                try
                {
                    using (var sqlBulkCopy = GetSqlBulkCopy((SqlConnection)connection, transaction, tableInfo.BulkConfig))
                    {
                        bool useFastMember = tableInfo.HasOwnedTypes == false &&                                           // With OwnedTypes DataTable is used since library FastMember can not (https://github.com/mgravell/fast-member/issues/21)
                                             tableInfo.ColumnNameContainsSquareBracket == false &&                                             // FastMember does not support escaped columnNames  ] -> ]]
                                             tableInfo.ShadowProperties.Count == 0 &&                                      // With Shadow prop. Discriminator (TPH inheritance) also not used because FastMember is slow for Update (https://github.com/borisdj/EFCore.BulkExtensions/pull/17)
                                             !tableInfo.ConvertibleProperties.Any() &&                                     // With ConvertibleProperties FastMember is slow as well
                                             !tableInfo.HasAbstractList &&                                                 // AbstractList not working with FastMember
                                             !tableInfo.BulkConfig.UseOnlyDataTable;
                        bool setColumnMapping = useFastMember;
                        tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
                        try
                        {
                            if (useFastMember)
                            {
                                using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
                                {
                                    sqlBulkCopy.WriteToServer(reader);
                                }
                            }
                            else
                            {
                                var dataTable = GetDataTable <T>(context, entities, sqlBulkCopy, tableInfo);
                                sqlBulkCopy.WriteToServer(dataTable);
                            }
                        }
                        catch (InvalidOperationException ex)
                        {
                            if (ex.Message.Contains(ColumnMappingExceptionMessage))
                            {
                                if (!tableInfo.CheckTableExist(context, tableInfo))
                                {
                                    context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));                                     // Will throw Exception specify missing db column: Invalid column name ''
                                    context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
                                }
                            }
                            throw ex;
                        }
                    }
                }
                finally
                {
                    if (transaction == null)
                    {
                        connection.Close();
                    }
                }
            }
            // -- SQLite --
            else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
            {
                var connection  = OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
                var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
                try
                {
                    var command = GetSqliteCommand(context, entities, tableInfo, connection, transaction);

                    var typeAccessor = TypeAccessor.Create(typeof(T), true);
                    int rowsCopied   = 0;
                    foreach (var item in entities)
                    {
                        LoadSqliteValues(tableInfo, typeAccessor, item, command);
                        command.ExecuteNonQuery();
                        SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
                    }
                }
                finally
                {
                    if (tableInfo.BulkConfig.SqliteTransaction == null)
                    {
                        transaction.Commit();
                        transaction.Dispose();
                    }
                    if (tableInfo.BulkConfig.SqliteConnection == null)
                    {
                        connection.Close();
                    }
                }
            }
            else
            {
                throw new SqlProviderNotSupportedException(providerName);
            }
        }
Example #8
0
        public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken)
        {
            string providerName = context.Database.ProviderName;             // "Microsoft.EntityFrameworkCore.*****"

            // -- SQL Server --
            if (providerName.EndsWith(DbServer.SqlServer.ToString()))
            {
                var connection = await OpenAndGetSqlConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);

                var transaction = context.Database.CurrentTransaction;
                try
                {
                    using (var sqlBulkCopy = GetSqlBulkCopy((SqlConnection)connection, transaction, tableInfo.BulkConfig))
                    {
                        bool useFastMember = tableInfo.HasOwnedTypes == false &&
                                             tableInfo.ColumnNameContainsSquareBracket == false &&
                                             tableInfo.ShadowProperties.Count == 0 &&
                                             !tableInfo.ConvertibleProperties.Any() &&
                                             !tableInfo.HasAbstractList &&
                                             !tableInfo.BulkConfig.UseOnlyDataTable;
                        bool setColumnMapping = useFastMember;
                        tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
                        try
                        {
                            if (useFastMember)
                            {
                                using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
                                {
                                    await sqlBulkCopy.WriteToServerAsync(reader, cancellationToken).ConfigureAwait(false);
                                }
                            }
                            else
                            {
                                var dataTable = GetDataTable <T>(context, entities, sqlBulkCopy, tableInfo);
                                await sqlBulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
                            }
                        }
                        catch (InvalidOperationException ex)
                        {
                            if (ex.Message.Contains(ColumnMappingExceptionMessage))
                            {
                                if (!await tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken).ConfigureAwait(false))
                                {
                                    await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);

                                    await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName), cancellationToken).ConfigureAwait(false);
                                }
                            }
                            throw ex;
                        }
                    }
                }
                finally
                {
                    if (transaction == null)
                    {
                        connection.Close();
                    }
                }
            }
            // -- SQLite --
            else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
            {
                var connection = await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);

                var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
                try
                {
                    var command = GetSqliteCommand(context, entities, tableInfo, connection, transaction);

                    var typeAccessor = TypeAccessor.Create(typeof(T), true);
                    int rowsCopied   = 0;
                    foreach (var item in entities)
                    {
                        LoadSqliteValues(tableInfo, typeAccessor, item, command);
                        await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);

                        SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
                    }
                }
                finally
                {
                    if (tableInfo.BulkConfig.SqliteTransaction == null)
                    {
                        transaction.Commit();
                        transaction.Dispose();
                    }
                    if (tableInfo.BulkConfig.SqliteConnection == null)
                    {
                        connection.Close();
                    }
                }
            }
            else
            {
                throw new SqlProviderNotSupportedException(providerName);
            }
        }