C# Database.GetTransaction方法代碼示例

        private void ProcessStats(IList<FeatureStatistics> stats)
        {
            try
            {
                Logger.Debug("Adding Stats to database");
                using (var database = new PetaPoco.Database(_connectionStringName))
                {
                    using (ITransaction transaction = database.GetTransaction())
                    {
                        foreach (var stat in stats)
                        {

                            foreach (var reading in stat.Readings)
                            {
                                var data = new FeatureData()
                                {
                                    Reading = reading.Name,
                                    Timestamp = DateTime.Now,
                                    Name = stat.Name,
                                    Group = stat.Group
                                };
                                data.ValueType = reading.GetType().Name;
                                SetValue(data, reading);
                                database.Insert("FeatureData", "Id", true, data);
                            }
                        }
                        transaction.Complete();
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error("Error saving stats to the database", ex);
            }
        }

        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 AddTagsToTopic(int topicId, List<Tag> tags)
        {
            var db = new Database("umbracoDbDSN");

            using (var scope = db.GetTransaction())
            {
                foreach (var tag in tags)
                {
                    addTagToTopic(topicId, tag);
                }
                scope.Complete();
            }
        }

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

                    transaction.Complete();
                }
            }
        }

        public static void SaveOffice(string catalog, string regionalDataFile, string officeCode, string officeName,
            string nickName,
            DateTime registrationDate, string currencyCode, string currencySymbol, string currencyName,
            string hundredthName, string fiscalYearCode,
            string fiscalYearName, DateTime startsFrom, DateTime endsOn,
            bool salesTaxIsVat, bool hasStateSalesTax, bool hasCountySalesTax,
            int quotationValidDays, decimal incomeTaxRate, int weekStartDay, DateTime transactionStartDate,
            bool isPerpetual, string valuationMethod, string logo,
            string adminName, string username, string password)
        {
            try
            {
                using (Database db = new Database(Factory.GetConnectionString(catalog), Factory.ProviderName))
                {
                    using (Transaction transaction = db.GetTransaction())
                    {
                        string sql = File.ReadAllText(regionalDataFile, Encoding.UTF8);
                        db.Execute(sql);

                        sql =
                            "SELECT * FROM office.add_office(@0::varchar(12), @1::varchar(150), @2::varchar(50), @3::date, @4::varchar(12), @5::varchar(12), @6::varchar(48), @7::varchar(48), @8::varchar(12), @9::varchar(50), @10::date,@11::date, @12::boolean, @13::boolean, @14::boolean, @15::integer, @16::numeric, @17::integer, @18::date, @19::boolean, @20::character varying(5), @21::text, @22::varchar(100), @23::varchar(50), @24::varchar(48));";
                        db.Execute(sql, officeCode, officeName, nickName, registrationDate, currencyCode,
                            currencySymbol, currencyName, hundredthName, fiscalYearCode, fiscalYearName, startsFrom,
                            endsOn,
                            salesTaxIsVat, hasStateSalesTax, hasCountySalesTax, quotationValidDays,
                            incomeTaxRate, weekStartDay, transactionStartDate, isPerpetual, valuationMethod, logo,
                            adminName,
                            username, password);

                        transaction.Complete();
                    }
                }
            }
            catch (NpgsqlException ex)
            {
                if (ex.Code.StartsWith("P"))
                {
                    string errorMessage = Factory.GetDBErrorResource(ex);
                    throw new MixERPException(errorMessage, ex);
                }

                throw;
            }
        }

        /// <summary>
        /// Inserts or updates multiple instances of LeaveApplication class on the database table "hrm.leave_applications";
        /// </summary>
        /// <param name="leaveApplications">List of "LeaveApplication" class to import.</param>
        /// <returns></returns>
        public List<object> BulkImport(List<MixERP.Net.Entities.HRM.LeaveApplication> leaveApplications)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.ImportData, this._LoginId, false);
                }

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

            var result = new List<object>();
            int line = 0;
            try
            {
                using (Database db = new Database(Factory.GetConnectionString(this._Catalog), Factory.ProviderName))
                {
                    using (Transaction transaction = db.GetTransaction())
                    {
                        foreach (var leaveApplication in leaveApplications)
                        {
                            line++;

                            leaveApplication.AuditUserId = this._UserId;
                            leaveApplication.EnteredBy = this._UserId;
                            leaveApplication.AuditTs = System.DateTime.UtcNow;

                            if (leaveApplication.LeaveApplicationId > 0)
                            {
                                result.Add(leaveApplication.LeaveApplicationId);
                                db.Update(leaveApplication, leaveApplication.LeaveApplicationId);
                            }
                            else
                            {
                                result.Add(db.Insert(leaveApplication));
                            }
                        }

                        transaction.Complete();
                    }

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

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

                    throw new MixERPException(errorMessage, ex);
                }

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

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

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

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

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

                            object primaryKeyValue = workCenter.work_center_id;

                            if (Cast.To<int>(primaryKeyValue) > 0)
                            {
                                result.Add(workCenter.work_center_id);
                                db.Update("office.work_centers", "work_center_id", workCenter, workCenter.work_center_id);
                            }
                            else
                            {
                                result.Add(db.Insert("office.work_centers", "work_center_id", workCenter));
                            }
                        }

                        transaction.Complete();
                    }

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

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

                    throw new MixERPException(errorMessage, ex);
                }

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

        public void RecreateFilters(string objectName, string filterName, List<MixERP.Net.Entities.Core.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(Factory.GetConnectionString(this._Catalog), Factory.ProviderName))
            {
                using (Transaction transaction = db.GetTransaction())
                {

                    var toDelete = this.GetWhere(1, new List<EntityParser.Filter>
                        {
                            new EntityParser.Filter { ColumnName = "object_name", FilterCondition = (int) FilterCondition.IsEqualTo, FilterValue = objectName },
                            new EntityParser.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();
                }
            }
        }

        /// <summary>
        /// Inserts or updates multiple instances of AttachmentLookup class on the database table "core.attachment_lookup";
        /// </summary>
        /// <param name="attachmentLookups">List of "AttachmentLookup" class to import.</param>
        /// <returns></returns>
        public List<object> BulkImport(List<MixERP.Net.Entities.Core.AttachmentLookup> attachmentLookups)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.ImportData, this.LoginId, false);
                }

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

            var result = new List<object>();
            int line = 0;
            try
            {
                using (Database db = new Database(Factory.GetConnectionString(this.Catalog), Factory.ProviderName))
                {
                    using (Transaction transaction = db.GetTransaction())
                    {
                        foreach (var attachmentLookup in attachmentLookups)
                        {
                            line++;

                            if (attachmentLookup.AttachmentLookupId > 0)
                            {
                                result.Add(attachmentLookup.AttachmentLookupId);
                                db.Update(attachmentLookup, attachmentLookup.AttachmentLookupId);
                            }
                            else
                            {
                                result.Add(db.Insert(attachmentLookup));
                            }
                        }

                        transaction.Complete();
                    }

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

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

                    throw new MixERPException(errorMessage, ex);
                }

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

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

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

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

                            object primaryKeyValue = widgetGroup.widget_group_name;

                            if (!string.IsNullOrWhiteSpace(widgetGroup.widget_group_name))
                            {
                                result.Add(widgetGroup.widget_group_name);
                                db.Update("core.widget_groups", "widget_group_name", widgetGroup, widgetGroup.widget_group_name);
                            }
                            else
                            {
                                result.Add(db.Insert("core.widget_groups", "widget_group_name", widgetGroup));
                            }
                        }

                        transaction.Complete();
                    }

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

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

                    throw new MixERPException(errorMessage, ex);
                }

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

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

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

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

                            object primaryKeyValue = exchangeRate.exchange_rate_id;

                            if (Cast.To<long>(primaryKeyValue) > 0)
                            {
                                result.Add(exchangeRate.exchange_rate_id);
                                db.Update("core.exchange_rates", "exchange_rate_id", exchangeRate, exchangeRate.exchange_rate_id);
                            }
                            else
                            {
                                result.Add(db.Insert("core.exchange_rates", "exchange_rate_id", exchangeRate));
                            }
                        }

                        transaction.Complete();
                    }

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

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

                    throw new MixERPException(errorMessage, ex);
                }

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

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

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

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

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

                            object primaryKeyValue = currency.currency_code;

                            if (!string.IsNullOrWhiteSpace(currency.currency_code))
                            {
                                result.Add(currency.currency_code);
                                db.Update("core.currencies", "currency_code", currency, currency.currency_code);
                            }
                            else
                            {
                                result.Add(db.Insert("core.currencies", "currency_code", currency));
                            }
                        }

                        transaction.Complete();
                    }

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

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

                    throw new MixERPException(errorMessage, ex);
                }

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

        /// <summary>
        /// Inserts or updates multiple instances of ScrudFactory class on the database table "config.scrud_factory";
        /// </summary>
        /// <param name="scrudFactories">List of "ScrudFactory" class to import.</param>
        /// <returns></returns>
        public List<object> BulkImport(List<MixERP.Net.Entities.Config.ScrudFactory> scrudFactories)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.ImportData, this.LoginId, false);
                }

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

            var result = new List<object>();
            int line = 0;
            try
            {
                using (Database db = new Database(Factory.GetConnectionString(this.Catalog), Factory.ProviderName))
                {
                    using (Transaction transaction = db.GetTransaction())
                    {
                        foreach (var scrudFactory in scrudFactories)
                        {
                            line++;

                            scrudFactory.AuditUserId = this.UserId;
                            scrudFactory.AuditTs = System.DateTime.UtcNow;

                            if (!string.IsNullOrWhiteSpace(scrudFactory.Key))
                            {
                                result.Add(scrudFactory.Key);
                                db.Update(scrudFactory, scrudFactory.Key);
                            }
                            else
                            {
                                result.Add(db.Insert(scrudFactory));
                            }
                        }

                        transaction.Complete();
                    }

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

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

                    throw new MixERPException(errorMessage, ex);
                }

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