C# Database.GetTransaction方法代码示例

        public async Task DeleteAsync(Room room)
        {
            var groups = (await _roomScriptGroupRepository.GetAsync(room)).ToList();
            var scripts = new List<RoomScript>();

            foreach (var group in groups)
            {
                scripts.AddRange(await _roomScriptRepository.GetAsync(group.Id));
            }

            using (var database = new Database("ConnectionString"))
            {
                using (var transaction = database.GetTransaction())
                {
                    foreach (var script in scripts)
                    {
                        await database.DeleteAsync(script);
                    }

                    foreach (var group in groups)
                    {
                        await database.DeleteAsync(group);
                    }

                    await database.DeleteAsync(room);

                    transaction.Complete();
                }
            }
        }

        /// <summary>
        /// Executes the migrations against the database.
        /// </summary>
        /// <param name="database">The PetaPoco Database, which the migrations will be run against</param>
        /// <param name="databaseProvider"></param>
        /// <param name="isUpgrade">Boolean indicating whether this is an upgrade or downgrade</param>
        /// <returns><c>True</c> if migrations were applied, otherwise <c>False</c></returns>
        public bool Execute(Database database, DatabaseProviders databaseProvider, bool isUpgrade = true)
        {
            LogHelper.Info<MigrationRunner>("Initializing database migrations");

	        var foundMigrations = MigrationResolver.Current.Migrations;

            var migrations = isUpgrade
                                 ? OrderedUpgradeMigrations(foundMigrations).ToList()
                                 : OrderedDowngradeMigrations(foundMigrations).ToList();
            
            if (Migrating.IsRaisedEventCancelled(new MigrationEventArgs(migrations, _configuredVersion, _targetVersion, true), this))
                return false;

            //Loop through migrations to generate sql
            var context = new MigrationContext(databaseProvider, database);
            foreach (MigrationBase migration in migrations)
            {
                if (isUpgrade)
                {
                    migration.GetUpExpressions(context);
                    LogHelper.Info<MigrationRunner>(string.Format("Added UPGRADE migration '{0}' to context", migration.GetType().Name));
                }
                else
                {
                    migration.GetDownExpressions(context);
                    LogHelper.Info<MigrationRunner>(string.Format("Added DOWNGRADE migration '{0}' to context", migration.GetType().Name));
                }
            }

            //Transactional execution of the sql that was generated from the found migrations
            using (Transaction transaction = database.GetTransaction())
            {
                int i = 1;
                foreach (var expression in context.Expressions)
                {
                    var sql = expression.Process(database);
                    if (string.IsNullOrEmpty(sql))
                    {
                        i++;
                        continue;
                    }

                    LogHelper.Info<MigrationRunner>("Executing sql statement " + i + ": " + sql);
                    database.Execute(sql);
                    i++;
                }

                transaction.Complete();
            }

            Migrated.RaiseEvent(new MigrationEventArgs(migrations, context, _configuredVersion, _targetVersion, false), this);

            return true;
        }

        protected override void SelectFetchEntities(int entityCount)
        {
            using (var database = new Database("SQLiteTest"))
            {
                using (var transaction = database.GetTransaction())
                {
                    var results = database.Fetch<Entity>(new Sql("SELECT * FROM Entity WHERE Id <= @0", entityCount));

                    transaction.Complete();
                }
            }
        }

        protected override void PagedEntities(int entityCount)
        {
            using (var database = new Database("SQLiteTest"))
            {
                using (var transaction = database.GetTransaction())
                {
                    var results = database.Page<Entity>(1, entityCount, new Sql("SELECT * FROM Entity"));

                    transaction.Complete();
                }
            }
        }

        protected override void InsertEntities(List<Entity> entities)
        {
            using (var database = new Database("SQLiteTest"))
            {
                using (var transaction = database.GetTransaction())
                {
                    entities.ForEach(entity => database.Insert(entity));

                    transaction.Complete();
                }
            }
        }

        public static void RestartTestDatabase()
        {
            var database = new Database("Presencia");

            using (var transaction = database.GetTransaction())
            {
                database.Execute("DELETE FROM Pausas;");
                database.Execute("DELETE FROM Jornadas;");
                database.Execute("DELETE FROM UsuariosRoles;");
                database.Execute("DELETE FROM Roles;");
                database.Execute("DELETE FROM Usuarios;");

                // Añadir usuarios y roles
                database.Execute("INSERT INTO Usuarios ([Usuario], [Password]) VALUES (@0, @1)", Usuario, "Prueba");
                database.Execute("INSERT INTO Roles ([Name]) VALUES (@0)", Rol);
                database.Execute("INSERT INTO UsuariosRoles ([Usuario], [Rol]) VALUES (@0, @1)", Usuario, Rol);

                // Añadir jornadas terminadas anteriores al día actual
                var dia = DateTime.Today.AddDays(-NumeroDiasCreados);

                for (var i = 0; i < NumeroDiasCreados; i++)
                {
                    var idJornada = database.ExecuteScalar<int>(
                        "INSERT INTO Jornadas ([Usuario],[Fecha],[Entrada],[Salida]) VALUES (@0, @1, @2, @3);\nSELECT SCOPE_IDENTITY() AS NewID;",
                        Usuario,
                        dia,
                        dia.AddHours(8),
                        dia.AddHours(16));

                    for (var j = 0; j < 4; j++)
                    {
                        var horaInicioPausa = 9 + j;
                        database.Execute(
                        "INSERT INTO Pausas ([IdJornada],[Inicio],[Fin]) VALUES (@0, @1, @2)",
                        idJornada,
                        dia.AddHours(horaInicioPausa),
                        dia.AddMinutes((horaInicioPausa * 60) + 30));
                    }

                    dia = dia.AddDays(1);
                }

                transaction.Complete();
            }
        }

        public async Task DeleteAsync(RoomScriptGroup group)
        {
            var scripts = await _roomScriptRepository.GetAsync(group.Id);

            using (var database = new Database("ConnectionString"))
            {
                using (var transaction = database.GetTransaction())
                {
                    foreach (var script in scripts)
                    {
                        await database.DeleteAsync(script);
                    }

                    await database.DeleteAsync(group);

                    transaction.Complete();
                }
            }
        }

        /// <summary>
        /// Inserts or updates multiple instances of User class on the database table "account.users";
        /// </summary>
        /// <param name="users">List of "User" class to import.</param>
        /// <returns></returns>
        public List<object> BulkImport(List<ExpandoObject> users)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.ImportData, this._LoginId, this._Catalog, false);
                }

                if (!this.HasAccess)
                {
                    Log.Information("Access to import entity \"User\" was denied to the user with Login ID {LoginId}. {users}", this._LoginId, users);
                    throw new UnauthorizedException("Access is denied.");
                }
            }

            var result = new List<object>();
            int line = 0;
            try
            {
                using (Database db = new Database(ConnectionString.GetConnectionString(this._Catalog), Factory.ProviderName))
                {
                    using (ITransaction transaction = db.GetTransaction())
                    {
                        foreach (dynamic user in users)
                        {
                            line++;

                            user.audit_user_id = this._UserId;
                            user.audit_ts = System.DateTime.UtcNow;

                            object primaryKeyValue = user.user_id;

                            if (Cast.To<int>(primaryKeyValue) > 0)
                            {
                                result.Add(user.user_id);
                                db.Update("account.users", "user_id", user, user.user_id);
                            }
                            else
                            {
                                result.Add(db.Insert("account.users", "user_id", user));
                            }
                        }

                        transaction.Complete();
                    }

                    return result;
                }
            }
            catch (NpgsqlException ex)
            {
                string errorMessage = $"Error on line {line} ";

                if (ex.Code.StartsWith("P"))
                {
                    errorMessage += Factory.GetDbErrorResource(ex);

                    throw new DataAccessException(errorMessage, ex);
                }

                errorMessage += ex.Message;
                throw new DataAccessException(errorMessage, ex);
            }
            catch (System.Exception ex)
            {
                string errorMessage = $"Error on line {line} ";
                throw new DataAccessException(errorMessage, ex);
            }
        }

        /// <summary>
        /// Inserts or updates multiple instances of RefreshToken class on the database table "account.refresh_tokens";
        /// </summary>
        /// <param name="refreshTokens">List of "RefreshToken" class to import.</param>
        /// <returns></returns>
        public List<object> BulkImport(List<ExpandoObject> refreshTokens)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.ImportData, this._LoginId, this._Catalog, false);
                }

                if (!this.HasAccess)
                {
                    Log.Information("Access to import entity \"RefreshToken\" was denied to the user with Login ID {LoginId}. {refreshTokens}", this._LoginId, refreshTokens);
                    throw new UnauthorizedException("Access is denied.");
                }
            }

            var result = new List<object>();
            int line = 0;
            try
            {
                using (Database db = new Database(ConnectionString.GetConnectionString(this._Catalog), Factory.ProviderName))
                {
                    using (ITransaction transaction = db.GetTransaction())
                    {
                        foreach (dynamic refreshToken in refreshTokens)
                        {
                            line++;



                            object primaryKeyValue = refreshToken.refresh_token_id;

                            if (refreshToken.refresh_token_id != null)
                            {
                                result.Add(refreshToken.refresh_token_id);
                                db.Update("account.refresh_tokens", "refresh_token_id", refreshToken, refreshToken.refresh_token_id);
                            }
                            else
                            {
                                result.Add(db.Insert("account.refresh_tokens", "refresh_token_id", refreshToken));
                            }
                        }

                        transaction.Complete();
                    }

                    return result;
                }
            }
            catch (NpgsqlException ex)
            {
                string errorMessage = $"Error on line {line} ";

                if (ex.Code.StartsWith("P"))
                {
                    errorMessage += Factory.GetDbErrorResource(ex);

                    throw new DataAccessException(errorMessage, ex);
                }

                errorMessage += ex.Message;
                throw new DataAccessException(errorMessage, ex);
            }
            catch (System.Exception ex)
            {
                string errorMessage = $"Error on line {line} ";
                throw new DataAccessException(errorMessage, ex);
            }
        }

        /// <summary>
        /// Inserts or updates multiple instances of CustomFieldForm class on the database table "config.custom_field_forms";
        /// </summary>
        /// <param name="customFieldForms">List of "CustomFieldForm" class to import.</param>
        /// <returns></returns>
        public List<object> BulkImport(List<ExpandoObject> customFieldForms)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.ImportData, this._LoginId, this._Catalog, false);
                }

                if (!this.HasAccess)
                {
                    Log.Information("Access to import entity \"CustomFieldForm\" was denied to the user with Login ID {LoginId}. {customFieldForms}", this._LoginId, customFieldForms);
                    throw new UnauthorizedException("Access is denied.");
                }
            }

            var result = new List<object>();
            int line = 0;
            try
            {
                using (Database db = new Database(ConnectionString.GetConnectionString(this._Catalog), Factory.ProviderName))
                {
                    using (ITransaction transaction = db.GetTransaction())
                    {
                        foreach (dynamic customFieldForm in customFieldForms)
                        {
                            line++;



                            object primaryKeyValue = customFieldForm.form_name;

                            if (!string.IsNullOrWhiteSpace(customFieldForm.form_name))
                            {
                                result.Add(customFieldForm.form_name);
                                db.Update("config.custom_field_forms", "form_name", customFieldForm, customFieldForm.form_name);
                            }
                            else
                            {
                                result.Add(db.Insert("config.custom_field_forms", "form_name", customFieldForm));
                            }
                        }

                        transaction.Complete();
                    }

                    return result;
                }
            }
            catch (NpgsqlException ex)
            {
                string errorMessage = $"Error on line {line} ";

                if (ex.Code.StartsWith("P"))
                {
                    errorMessage += Factory.GetDbErrorResource(ex);

                    throw new DataAccessException(errorMessage, ex);
                }

                errorMessage += ex.Message;
                throw new DataAccessException(errorMessage, ex);
            }
            catch (System.Exception ex)
            {
                string errorMessage = $"Error on line {line} ";
                throw new DataAccessException(errorMessage, ex);
            }
        }

        private void ExecuteMigrations(IMigrationContext context, Database database)
        {
            //Transactional execution of the sql that was generated from the found migrations
            using (var transaction = database.GetTransaction())
            {
                int i = 1;
                foreach (var expression in context.Expressions)
                {
                    var sql = expression.Process(database);
                    if (string.IsNullOrEmpty(sql))
                    {
                        i++;
                        continue;
                    }

                    //TODO: We should output all of these SQL calls to files in a migration folder in App_Data/TEMP
                    // so if people want to executed them manually on another environment, they can.

                    //The following ensures the multiple statement sare executed one at a time, this is a requirement
                    // of SQLCE, it's unfortunate but necessary.
                    // http://stackoverflow.com/questions/13665491/sql-ce-inconsistent-with-multiple-statements
                    var sb = new StringBuilder();
                    using (var reader = new StringReader(sql))
                    {
                        string line;
                        while ((line = reader.ReadLine()) != null)
                        {
                            line = line.Trim();
                            if (line.Equals("GO", StringComparison.OrdinalIgnoreCase))
                            {
                                //Execute the SQL up to the point of a GO statement
                                var exeSql = sb.ToString();
                                _logger.Info<MigrationRunner>("Executing sql statement " + i + ": " + exeSql);
                                database.Execute(exeSql);

                                //restart the string builder
                                sb.Remove(0, sb.Length);
                            }
                            else
                            {
                                sb.AppendLine(line);
                            }
                        }
                        //execute anything remaining
                        if (sb.Length > 0)
                        {
                            var exeSql = sb.ToString();
                            _logger.Info<MigrationRunner>("Executing sql statement " + i + ": " + exeSql);
                            database.Execute(exeSql);
                        }
                    }

                    i++;
                }

                transaction.Complete();

            }
        }

        /// <summary>
        /// Inserts or updates multiple instances of AppDependency class on the database table "config.app_dependencies";
        /// </summary>
        /// <param name="appDependencies">List of "AppDependency" class to import.</param>
        /// <returns></returns>
        public List<object> BulkImport(List<ExpandoObject> appDependencies)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.ImportData, this._LoginId, this._Catalog, false);
                }

                if (!this.HasAccess)
                {
                    Log.Information("Access to import entity \"AppDependency\" was denied to the user with Login ID {LoginId}. {appDependencies}", this._LoginId, appDependencies);
                    throw new UnauthorizedException("Access is denied.");
                }
            }

            var result = new List<object>();
            int line = 0;
            try
            {
                using (Database db = new Database(ConnectionString.GetConnectionString(this._Catalog), Factory.ProviderName))
                {
                    using (ITransaction transaction = db.GetTransaction())
                    {
                        foreach (dynamic appDependency in appDependencies)
                        {
                            line++;

                            object primaryKeyValue = appDependency.app_dependency_id;

                            if (Cast.To<int>(primaryKeyValue) > 0)
                            {
                                result.Add(appDependency.app_dependency_id);
                                db.Update("config.app_dependencies", "app_dependency_id", appDependency, appDependency.app_dependency_id);
                            }
                            else
                            {
                                result.Add(db.Insert("config.app_dependencies", "app_dependency_id", appDependency));
                            }
                        }

                        transaction.Complete();
                    }

                    return result;
                }
            }
            catch (NpgsqlException ex)
            {
                string errorMessage = $"Error on line {line} ";

                if (ex.Code.StartsWith("P"))
                {
                    errorMessage += Factory.GetDbErrorResource(ex);

                    throw new DataAccessException(errorMessage, ex);
                }

                errorMessage += ex.Message;
                throw new DataAccessException(errorMessage, ex);
            }
            catch (System.Exception ex)
            {
                string errorMessage = $"Error on line {line} ";
                throw new DataAccessException(errorMessage, ex);
            }
        }

        public void RecreateFilters(string objectName, string filterName, List<Frapid.Config.Entities.Filter> filters)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.Create, this._LoginId, this._Catalog, false);
                }

                if (!this.HasAccess)
                {
                    Log.Information("Access to add entity \"Filter\" was denied to the user with Login ID {LoginId}. {filters}", this._LoginId, filters);
                    throw new UnauthorizedException("Access is denied.");
                }
            }


            using (Database db = new Database(ConnectionString.GetConnectionString(this._Catalog), Factory.ProviderName))
            {
                using (ITransaction transaction = db.GetTransaction())
                {

                    var toDelete = this.GetWhere(1, new List<Frapid.DataAccess.Models.Filter>
                        {
                            new Frapid.DataAccess.Models.Filter { ColumnName = "object_name", FilterCondition = (int) FilterCondition.IsEqualTo, FilterValue = objectName },
                            new Frapid.DataAccess.Models.Filter { ColumnName = "filter_name", FilterCondition = (int) FilterCondition.IsEqualTo, FilterValue = filterName }
                        });


                    foreach (var filter in toDelete)
                    {
                        db.Delete(filter);
                    }

                    foreach (var filter in filters)
                    {
                        filter.AuditUserId = this._UserId;
                        filter.AuditTs = System.DateTime.UtcNow;

                        db.Insert(filter);
                    }

                    transaction.Complete();
                }
            }
        }

        protected override void SelectSingleEntity(int entityCount)
        {
            using (var database = new Database("SQLiteTest"))
            {
                using (var transaction = database.GetTransaction())
                {
                    do
                    {
                        database.SingleById<Entity>(entityCount);
                        entityCount--;
                    }
                    while (entityCount > 0);

                    transaction.Complete();
                }
            }
        }