[置顶] 第一次使用事物 利用线性表

使用数据可事物可有效避免数据库当中的脏数据

protected void btnSave_Click(object sender, EventArgs e)

        {
            Disasters d = new Disasters();
           
            d.GetModel(id);
            d.DisasterTitle = this.txtDisasterTitle.Text.Trim();
            d.DisasterDesc = this.txtDisasterDesc.Text.Trim();
            if (this.txtEventTime.Text.Trim() != "")
            {
                d.EventTime = Convert.ToDateTime(this.txtEventTime.Text);
            }
            else
            {
                d.EventTime = null;
            }
            if (this.txtLatitude.Text.Trim() != "")
            {
                d.Latitude = Convert.ToDouble(this.txtLatitude.Text.Trim());
            }
            else
            {
                d.Latitude = null;
            }
            if (this.txtLongitude.Text.Trim() != "")
            {
                d.Longitude = Convert.ToDouble(this.txtLongitude.Text.Trim());
            }
            else
            {
                d.Longitude = null;
            }
            if (this.txtMagnitude.Text.Trim() != "")
            {
                d.Magnitude = Convert.ToDouble(this.txtMagnitude.Text);
            }
            else
            {
                d.Magnitude = null;
            }
            if (this.txtRegion.Text.Trim() != "")
            {
                d.Region = this.txtRegion.Text;
            }
            else
            {
                d.Region = null;
            } if (this.txtDepth.Text.Trim() != "")
            {
                d.Depth = Convert.ToDouble(this.txtDepth.Text.Trim());
            }
            else
            {
                d.Depth = null;
            }
            d.AdminAuthFlag = 1;
            if (id > 0)
            {

                if (!d.Exists(id))
                {
                    this.lblMSG.Text = "保存失败";
                    return;
                }
            }
            string  strSql="update disasters set "+
                "DisasterTitle='"+d.DisasterTitle+"',"+"DisasterDesc='"+d.DisasterDesc+"',"+"Longitude='"+  d.Longitude+"',"+
                "Latitude='"+d.Latitude+"',"+"Depth='"+d.Depth+"',"+"Magnitude='"+d.Magnitude+"',"+
                "Region='"+ d.Region+"',"+"EventTime='"+d.EventTime+"' "+" where DisasterId='"+id+"',";
            List<String> SQLStringList=new List<String>();
            SQLStringList.Add(strSql);
            //遍历rptDisasters的选择项 更新AdminAuthFlag=-1 并将关联的图片 视频 disasterId更新为当前有效的DisasterId
            for (int i = 0; i < rptDisasters.Items.Count; i++)
            {

                int disasterId= Convert.ToInt32(((HiddenField)rptDisasters.Items[i].FindControl("hidId")).Value);
                CheckBox SelectThis = (CheckBox)rptDisasters.Items[i].FindControl("SelectThis");
                if (SelectThis.Checked)
                {
                    SQLStringList.Add("Update Videos set DisasterId='" + id + "' where DisasterId='" + disasterId + "'");
                    SQLStringList.Add("Update Pictures set DisasterId='" + id + "' where DisasterId='" + disasterId + "' ");
                    SQLStringList.Add("Update Texts set DisasterId='" + id + "' where DisasterId='" + disasterId + "'");
                    SQLStringList.Add("Update Disasters set AdminAuthFlag='-1' where DisasterId='" + disasterId + "'");
                    
                }
            }
            //执行
            int rows = DbHelperMySQL.ExecuteSqlTran(SQLStringList);
            if (rows <= 0)
            {
                this.lblMSG.Text = "保存失败";
            }

        }

DbHelperMySQL 类中ExecuteSqlTran函数||                                 

 /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }