Exemple #1
0
        internal static SqliteCommand GetSqliteCommand <T>(DbContext context, IList <T> entities, TableInfo tableInfo, SqliteConnection connection, SqliteTransaction transaction)
        {
            SqliteCommand command = connection.CreateCommand();

            command.Transaction = transaction;

            OperationType operationType = tableInfo.BulkConfig.OperationType;

            if (operationType == OperationType.Insert)
            {
                command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.Insert);
            }
            else if (operationType == OperationType.InsertOrUpdate)
            {
                command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.InsertOrUpdate);
            }
            else if (operationType == OperationType.InsertOrUpdateDelete)
            {
                throw new NotSupportedException("Sqlite supports only UPSERT(analog for MERGE WHEN MATCHED) but does not have functionality to do: 'WHEN NOT MATCHED BY SOURCE THEN DELETE'" +
                                                "What can be done is to read all Data, find rows that are not is input List, then with those do the BulkDelete.");
            }
            else if (operationType == OperationType.Update)
            {
                command.CommandText = SqlQueryBuilderSqlite.UpdateSetTable(tableInfo);
            }
            else if (operationType == OperationType.Delete)
            {
                command.CommandText = SqlQueryBuilderSqlite.DeleteFromTable(tableInfo);
            }

            var type                 = tableInfo.HasAbstractList ? entities[0].GetType() : 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 properties           = type.GetProperties();

            foreach (var property in properties)
            {
                if (entityPropertiesDict.ContainsKey(property.Name))
                {
                    var    propertyEntityType = entityPropertiesDict[property.Name];
                    string columnName         = propertyEntityType.GetColumnName();
                    var    propertyType       = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;

                    /*var sqliteType = SqliteType.Text; // "String" || "Decimal" || "DateTime"
                     * if (propertyType.Name == "Int16" || propertyType.Name == "Int32" || propertyType.Name == "Int64")
                     *  sqliteType = SqliteType.Integer;
                     * if (propertyType.Name == "Float" || propertyType.Name == "Double")
                     *  sqliteType = SqliteType.Real;
                     * if (propertyType.Name == "Guid" )
                     *  sqliteType = SqliteType.Blob; */

                    var parameter = new SqliteParameter($"@{columnName}", propertyType); // ,sqliteType // ,null
                    command.Parameters.Add(parameter);
                }
            }

            var shadowProperties = tableInfo.ShadowProperties;

            foreach (var shadowProperty in shadowProperties)
            {
                var parameter = new SqliteParameter($"@{shadowProperty}", typeof(string));
                command.Parameters.Add(parameter);
            }

            command.Prepare(); // Not Required (check if same efficiency when removed)
            return(command);
        }
Exemple #2
0
        public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) where T : class
        {
            string providerName = context.Database.ProviderName;

            // -- SQL Server --
            if (providerName.EndsWith(DbServer.SqlServer.ToString()))
            {
                tableInfo.InsertToTempTable = true;
                await tableInfo.CheckHasIdentityAsync(context, cancellationToken).ConfigureAwait(false);

                await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);

                if (tableInfo.CreatedOutputTable)
                {
                    await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true), cancellationToken).ConfigureAwait(false);

                    if (tableInfo.TimeStampColumnName != null)
                    {
                        await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType), cancellationToken).ConfigureAwait(false);
                    }
                }

                bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
                try
                {
                    await InsertAsync(context, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);

                    if (keepIdentity && tableInfo.HasIdentity)
                    {
                        await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);

                        await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true), cancellationToken).ConfigureAwait(false);
                    }

                    await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType), cancellationToken).ConfigureAwait(false);

                    if (tableInfo.CreatedOutputTable)
                    {
                        await tableInfo.LoadOutputDataAsync(context, entities, cancellationToken).ConfigureAwait(false);
                    }
                }
                finally
                {
                    if (!tableInfo.BulkConfig.UseTempDB)
                    {
                        if (tableInfo.CreatedOutputTable)
                        {
                            await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName), cancellationToken).ConfigureAwait(false);
                        }
                        await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName), cancellationToken).ConfigureAwait(false);
                    }

                    if (keepIdentity && tableInfo.HasIdentity)
                    {
                        await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false), cancellationToken).ConfigureAwait(false);

                        context.Database.CloseConnection();
                    }
                }
            }
            // -- 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);
                    }

                    if (operationType != OperationType.Delete && tableInfo.BulkConfig.SetOutputIdentity && tableInfo.IdentityColumnName != null)
                    {
                        command.CommandText = SqlQueryBuilderSqlite.SelectLastInsertRowId();
                        long lastRowIdScalar = (long)await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);

                        int    lastRowId            = (int)lastRowIdScalar;
                        var    accessor             = TypeAccessor.Create(typeof(T), true);
                        string identityPropertyName = tableInfo.PropertyColumnNamesDict.SingleOrDefault(a => a.Value == tableInfo.IdentityColumnName).Key;
                        for (int i = entities.Count - 1; i >= 0; i--)
                        {
                            accessor[entities[i], identityPropertyName] = lastRowId;
                            lastRowId--;
                        }
                    }
                }
                finally
                {
                    if (tableInfo.BulkConfig.SqliteTransaction == null)
                    {
                        transaction.Commit();
                        transaction.Dispose();
                    }
                    if (tableInfo.BulkConfig.SqliteConnection == null)
                    {
                        connection.Close();
                    }
                }
            }
            else
            {
                throw new SqlProviderNotSupportedException(providerName);
            }
        }