使用数据可事物可有效避免数据库当中的脏数据
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;
}
}
}