C# Database.Update方法代码示例

 public static void Save(Database db, Purchase purchase)
 {
     var existing = db.SingleOrDefault<Purchase>("SELECT * FROM purchases WHERE [email protected] ", purchase.UniqueId);
     if (existing != null)
         db.Update("purchases", "PurchaseId", existing);
     else
         db.Insert("purchases", "PurchaseId", purchase);
 }

 private void btnOK_Click(object sender, EventArgs e)
 {
     if (!dxValidationProvider1.Validate()) return;
     if (EditMode == Ultra.Web.Core.Enums.EnViewEditMode.New)
     {
         using (var db = new Database())
         {
             var et = db.FirstOrDefault<t_role>("where [email protected]", txtname.Text.Trim());
             if (null != et)
             {
                 MsgBox.ShowMessage(null, "名称已存在，不可重复添加!");
                 return;
             }
             db.Save(new t_role
             {
                 Guid = Guid.NewGuid(),
                 IsUsing = chk.Checked,
                 Name = txtname.Text.Trim(),
                 Creator = this.CurUser,
                 Desc=txtdesc.Text.Trim(),
                 CreateDate=TimeSync.Default.CurrentSyncTime,
                 Remark=string.Empty
             });
         }
     } else if (EditMode == Ultra.Web.Core.Enums.EnViewEditMode.Edit)
     {
         using (var db = new Database())
         {
             var et = db.FirstOrDefault<t_role>(" where [email protected]", GuidKey);
             if (et != null)
             {
                 db.Update<t_role>(" set [email protected],[desc][email protected] where [email protected]", chk.Checked, txtdesc.Text.Trim(), et.Guid);
             }
         }
     }
     DialogResult = System.Windows.Forms.DialogResult.OK;
     Close();
 }

 void btnUsing_ItemClick(object sender, ItemClickEventArgs e)
 {
     var et = gridControlEx1.GetFocusedDataSource<t_member>();
     if (null == et) return;
     using (var db = new Database()) {
         db.Update<t_member>(" set isusing=1 where [email protected]", et.Guid);
     }
     et.IsUsing = true;
     gridControlEx1.RefreshDataSource();
 }

        /// <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);
            }
        }

        static void Main(string[] args)
        {
            try
            {
                SendEMail("Started MDC OpenPermit Sync");
                Console.WriteLine("Sync Job Started on: " + DateTime.Now.ToString());              
                Console.WriteLine("Bringing data from Socrata");
                var permits = getPermitsToSync();

                Database db = new Database("openpermit");

                Console.WriteLine("Inserting Data into DB");
                foreach (Permit permit in permits.Item1)
                {
                    try
                    {
                        processMDCInpsections(permit, db);
                        db.Insert("Permit", "PermitNum", false, permit);
                    }
                    catch (Exception ex)
                    {
                        string errorMsg = String.Format("Error found Inserting Permit {0}: Error: '{1}' with Stack Trace: {2}",
                            permit.PermitNum, ex.Message, ex.StackTrace);
                        Console.WriteLine(errorMsg);
                        SendEMail(errorMsg);
                    }
                }

                SendEMail(String.Format("Inserted {0} new permit records.", permits.Item1.Count));

                foreach (Permit permit in permits.Item2)
                {
                    try
                    {

                        if (!checkIfExpired(permit))
                        {
                            processMDCInpsections(permit, db);
                        }
                        db.Update("Permit", "PermitNum", permit);
                    }
                    catch (Exception ex)
                    {
                        string errorMsg = String.Format("Error found Processing Permit {0}: Error: '{1}' with Stack Trace: {2}",
                            permit.PermitNum, ex.Message, ex.StackTrace);
                        Console.WriteLine(errorMsg);
                        SendEMail(errorMsg);
                    }
                    
                }

                SendEMail(String.Format("Processed {0} existing permit records.", permits.Item2.Count));

                Console.WriteLine("Populating Geography Field");
                db.Execute("UPDATE Permit SET Location=geography::Point(Latitude, Longitude, 4326) WHERE Location is NULL");
                Console.WriteLine("Sync Job Done on: " + DateTime.Now.ToString());  
                SendEMail("MDC OpenPermit Sync is done");
            }
            catch (Exception ex)
            {
                string errorMsg = String.Format("Error found running MDC OpenPermit Sync. Error: '{0}' with Stack Trace: {1}",
                    ex.Message, ex.StackTrace);
                Console.WriteLine(errorMsg);
                SendEMail(errorMsg);
            }

        }

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

                    transaction.Complete();
                }
            }
        }

        /// <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);
            }
        }

        /// <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);
            }
        }

 private void UpdatePrintCnt(List<t_trade> trds)
 {
     var whr = trds.Select(k => k.Guid.ToString()).Aggregate((s1, s2) => s1 + "','" + s2);
     using (var db = new Database()) {
         db.Update<t_trade>(string.Format(" set printcnt=isnull(printcnt,0)+1 where guid in ('{0}')", whr));
     }
 }

        /// <summary>
        /// 审核
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void btnReView_ItemClick(object sender, ItemClickEventArgs e)
        {
            var et = gcUnSub.GetFocusedDataSource<t_trade>();
            if (null == et)
                return;

            if (MsgBox.ShowYesNoMessage("", "确定要审核此出货单?") == DialogResult.Yes) {
                using (var db = new Database()) {
                    try {
                        var result = new SqlParameter() {
                            Direction = ParameterDirection.Output,
                            SqlDbType = SqlDbType.Bit,
                            ParameterName = "@result"
                        };
                        db.BeginTransaction();
                        db.Update<t_trade>(" set IsAudit=1 where [email protected]", et.Guid);
                        db.Execute("exec p_tradeupdateinvt @0,@1 output", et.Guid, result);

                        if (!(bool)result.Value) {
                            MsgBox.ShowMessage("", "库存不足!");
                            db.AbortTransaction();
                        } else {
                            db.CompleteTransaction();
                            gcUnSub.RemoveSelected();
                        }
                    } catch (Exception) {
                        db.AbortTransaction();
                        throw;
                    }
                }
            }
        }

 void btnInvalid_ItemClick(object sender, ItemClickEventArgs e)
 {
     var et = gcUnSub.GetFocusedDataSource<t_trade>();
     if (null == et)
         return;
     using (var db = new Database()) {
         db.Update<t_trade>(" set IsInvalid=1 where [email protected]", et.Guid);
     }
     gcUnSub.RemoveSelected();
 }

        private void btnOK_Click(object sender, EventArgs e)
        {
            if (!dxValidationProvider1.Validate()) return;

            var pro = lookUpEdit1.EditValue as T_ERP_Procedure;

            if(ExistsRng!=null && ExistsRng.Where(j => j.ProcedureGuid == pro.Guid && j.Guid != Ent.Guid).Count() > 0){
                MsgBox.ShowMessage("已存在该工序的工价");
                return;
            }

            if (EditMode == Business.Core.Define.EnViewEditMode.New)
            {
                var oj = new UltraDbEntity.T_ERP_ProducePrice
                {
                    Creator = CurUser,
                    Updator = CurUser,
                    Guid = Guid.NewGuid(),
                    Remark = string.Empty,
                    Reserved1 = 0,
                    Reserved2 = string.Empty,
                    Reserved3 = false,

                    CostPrice = labelSpinEdit1.Value,
                    OuterIid = Item.OuterIid,
                    OuterSkuId = Item.OuterSkuId,
                    ItemGuid = Item.Guid,
                    NumIid = Item.NumIid,
                    ProcedureGuid = pro.Guid,
                    ProcedureName = pro.ProcedureName
                };

                using (var db = new Database(this.ConnString))
                {
                    db.Insert(oj);
                }
                DialogResult = System.Windows.Forms.DialogResult.OK;
                Close();
            }
            else if (EditMode == Business.Core.Define.EnViewEditMode.Edit)
            {
                Ent.CostPrice = labelSpinEdit1.Value;
                Ent.ProcedureGuid = pro.Guid;
                Ent.ProcedureName = pro.ProcedureName;
                using (var db = new Database(this.ConnString))
                {
                    db.Update(Ent);
                }
                DialogResult = System.Windows.Forms.DialogResult.OK;
                Close();
            }
        }

        public static void setNotes(int rideId, String notes)
        {
            Ride ride = getRide(rideId);
            ride.notes = notes;

            using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes")))
            {
                db.Update(ride);
            }
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            if (!dxValidationProvider1.Validate()) return;

            if (spnOrder.EditValue != null) {
                using (var db = new Database(this.ConnString))
                {
                    var count = db.ExecuteScalar<int>(" select count(1) from T_ERP_Procedure where OrderNo = @0 and Guid <> @1 ", Convert.ToInt32(spnOrder.EditValue), Entity==null?Guid.Empty:Entity.Guid);
                    if (count > 0) {
                        MsgBox.ShowMessage("当前序号已经存在");
                        return;
                    }
                }
            }

            if (EditMode == Business.Core.Define.EnViewEditMode.New)
            {
                var r = new UltraDbEntity.T_ERP_Procedure
                {
                    Guid = Guid.NewGuid(),
                    Creator = CurUser,
                    Updator = CurUser,
                    Remark = string.Empty,
                    Reserved1 = 0,
                    Reserved2 = string.Empty,
                    Reserved3 = false,
                    ProcedureName = txtName.Text,
                    LabourCost = 0,
                    OrderNo = Convert.ToInt32(spnOrder.EditValue),
                    BatchNo = Convert.ToInt32(spnBatch.EditValue),
                    IsUsing = chkusing.Checked
                };
                using (var db = new Database(this.ConnString))
                {
                    db.Insert(r);
                    Entity = r;
                    DialogResult = System.Windows.Forms.DialogResult.OK; Close();
                }
            }
            else if (EditMode == Business.Core.Define.EnViewEditMode.Edit)
            {

                Entity.IsUsing = chkusing.Checked;
                Entity.Updator = CurUser;
                Entity.ProcedureName = txtName.Text;
                Entity.LabourCost = 0;
                Entity.OrderNo = Convert.ToInt32(spnOrder.EditValue);
                Entity.BatchNo = Convert.ToInt32(spnBatch.EditValue);

                using (var db = new Database(this.ConnString))
                {
                    db.Update(Entity);
                    DialogResult = System.Windows.Forms.DialogResult.OK; Close();
                }
            }
        }

        /// <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);
            }
        }