[置顶] c# 执行多条SQL语句，实现数据库事务 线性表 Hashtable
菜鸟初尝线性表
一下是页面源代码
<%@ Page Title="" Language="C#" MasterPageFile="~/admin/Admin.Master" AutoEventWireup="true"
CodeBehind="MyDisasterEdit.aspx.cs" Inherits="cn.edu.cidp.zqfxWeb.admin.Edit.MyDisasterEdit" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolderTitle" runat="server">
<script type="text/javascript" language="javascript">
function CheckAll(controlId, select) {
var control = document.getElementById(controlId);
//alert(select);
var nodeList = control.getElementsByTagName("input");
for (var i = 0; i < nodeList.length; i++) {
var node = nodeList[i];
if (node.type == "checkbox") {
node.checked = select;
}
}
}
</script>
<p>
编辑灾情</p>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolderBody" runat="server">
<table align="center">
<tr>
<td>
</td>
</tr>
<tr>
<td>
<table>
<tr>
<td>
标题：
</td>
<td>
<asp:TextBox ID="txtDisasterTitle" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td valign="top">
描述：
</td>
<td>
<asp:TextBox ID="txtDisasterDesc" runat="server" Width="500px" Height="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
发生时间：
</td>
<td>
<asp:TextBox ID="txtEventTime" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
地区：
</td>
<td>
<asp:TextBox ID="txtRegion" runat="server" Width="500px"></asp:TextBox>
</td>
</tr>
<tr>
<%-- 只有地震才有--%>
<td colspan="2">
<asp:Panel ID="PanelEarthquake" runat="server">
<table>
<tr>
<td>
<table>
<tr>
<td>
震级：
</td>
<td>
<asp:TextBox ID="txtMagnitude" runat="server" Width="150px"></asp:TextBox>
</td>
</tr>
</table>
</td>
<td>
<table>
<tr>
<td>
深度：
</td>
<td>
<asp:TextBox ID="txtDepth" runat="server" Width="150px"></asp:TextBox>
</td>
</tr>
</table>
</td>
</tr>
</table>
</asp:Panel>
</td>
<tr>
<td colspan="2">
<table>
<tr>
<td>
<table>
<tr>
<td>
经度：
</td>
<td>
<asp:TextBox ID="txtLongitude" runat="server" Width="150px"></asp:TextBox>
</td>
</tr>
</table>
</td>
<td>
<table>
<tr>
<td>
纬度：
</td>
<td>
<asp:TextBox ID="txtLatitude" runat="server" Width="150px"></asp:TextBox>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<table width="*">
<tr>
<td>
相关灾情：
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<table width="*">
<asp:Repeater ID="rptDisasters" runat="server">
<HeaderTemplate>
<tr>
<td colspan="3">
<input id="cbSelect" type="checkbox" onclick="CheckAll('ContentPlaceHolderBody_gvTexts',this.checked)" />全选
</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<asp:CheckBox ID="SelectThis" runat="server" />
<asp:HiddenField ID="hidId" Value='<%#Eval("DisasterId")%>' runat="server" />
</td>
<td>
<ul style="text-align: left; line-height: 160%;">
<li>[标题]<%# Eval("DisasterTitle")%></li>
<li>[地区]<%# Eval("Region")%></li>
<li>[经度]<%# Eval("Longitude")%>，[纬度]<%#Eval("Latitude")%></li>
<li>[发生时间]<%# Eval("EventTime")%></li>
</ul>
</td>
<td>
</td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
</td>
</tr>
<tr>
<td colspan="*" align="center">
<asp:Button ID="btnSave" runat="server" Text="关联并保存" onclick="btnSave_Click"></asp:Button>
<asp:Button ID="btnCancel" runat="server" Text="取消" onclick="btnCancel_Click"></asp:Button>
</td>
</tr>
<tr>
<td colspan="*" align="center">
<asp:Label ID="lblMSG" runat="server" Font-Bold="True" ForeColor="#CC0000"></asp:Label>
</td>
</tr>
</table>
</asp:Content>
补界面截图一张
作为以为已经大三的人来说 第一次写关于线性表的程序 略表惭愧
用线性的原因呢是应为我要用数据库事物来执行一些操作 ，比如将多条重复的事件归纳为一条时间 而是这些事件关联的新闻Texts，图片Pictures，视频Videos，都要更新，所以就选择了数据库事物，
最关键的是数据库helper类当中是这样的
1：
/// <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;
}
}
}
2：
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
MySqlCommand cmd = new MySqlCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}
一开始我想用哈希表Hashtable把
结果就有了 如下事件
解析了之后呢还是不可用
郁闷了 上网搜到：http://www.cnblogs.com/leischen/archive/2011/12/19/2293939.html
里面东西是要用到model的
而我是为了避开model才这样弄的
所以自己写
：
代码如下
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using cn.edu.cidp.zqfxLib;
namespace cn.edu.cidp.zqfxWeb.admin.Edit
{
public partial class MyDisasterEdit : MyAdminPage
{
int id;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request["ID"] != null || Request["ID"] != "")
{
id = Convert.ToInt32(Request["ID"]);
LoadData(id);
}
}
}
public void LoadData(int id)
{
if (id > 0)
{
Disasters d = new Disasters();
if (!d.Exists(id))
{
this.lblMSG.Text = "参数不正确";
return;
}
d.GetModel(id);
this.txtDisasterTitle.Text = d.DisasterTitle;
this.txtDisasterDesc.Text = d.DisasterDesc;
this.txtEventTime.Text = d.EventTime.ToString();
this.txtLatitude.Text = d.Latitude.ToString();
this.txtLongitude.Text = d.Longitude.ToString();
this.txtMagnitude.Text = d.Magnitude.ToString();
this.txtRegion.Text = d.Region.ToString();
this.txtDepth.Text = d.Depth.ToString();
if (d.DisasterCategoryId > 0)
{
// PageUtil.GetDisasterNameByDisterId(d.DisasterCategoryId);
if (PageUtil.GetCategoryNameById(Convert.ToInt32(d.DisasterCategoryId)).ToString().Contains("地震"))
{
this.PanelEarthquake.Visible = true;
}
else
this.PanelEarthquake.Visible = false;
}
string strWhere = "DisasterTitle like '%{0}%' Longitude like '%{1}%' Latitude like '%{2}%' order by UploadUName Desc";
strWhere = string.Format(strWhere, d.DisasterTitle, d.Longitude, d.Latitude);
Disasters disasters = new Disasters();
rptDisasters.DataSource = disasters.GetList(strWhere);
rptDisasters.DataBind();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
Disasters d = new Disasters();
d.DisasterTitle = this.txtDisasterTitle.Text;
d.DisasterDesc= this.txtDisasterDesc.Text;
d.EventTime = Convert.ToDateTime(this.txtEventTime.Text) ;
d.Latitude = Convert.ToDouble(this.txtLatitude.Text);
d.Longitude = Convert.ToDouble(this.txtLongitude.Text);
d.Magnitude = Convert.ToDouble( this.txtMagnitude.Text);
d.Region = this.txtRegion.Text;
d.Depth = Convert.ToDouble(this.txtDepth.Text);
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 = "保存失败";
}
}
protected void btnCancel_Click(object sender, EventArgs e)
{
Response.Redirect("../MyAuditDisastersList.aspx");
}
}
}
