动态组合SQL语句方式实现批量更新的实例
default.aspx
<%@ page language="c#" autoeventwireup="true" codefile="index.aspx.cs" inherits="index" %>
<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>供求信息网审核发布信息</title>
</head>
<body class="font">
<form id="form1" runat="server">
<div style="text-align: left" align="left"><asp:panel id="panel2" runat="server">
<asp:gridview id="gridview1" runat="server" autogeneratecolumns="false"
onrowdatabound="gridview1_rowdatabound"
onselectedindexchanging="gridview1_selectedindexchanging" font-size="9pt"
allowpaging="true" emptydatatext="没有相关数据可以显示！"
onpageindexchanging="gridview1_pageindexchanging" cellpadding="4"
forecolor="#333333" gridlines="none" datakeynames="id">
<columns>
<asp:templatefield>
<itemtemplate>
<asp:checkbox id="cbsingleormore" runat="server" />
</itemtemplate>
</asp:templatefield>
<asp:boundfield datafield="id" headertext="信息id" />
<asp:boundfield datafield="name" headertext="信息主题" />
<asp:boundfield datafield="type" headertext="信息分类" />
<asp:boundfield datafield="content" headertext="发布内容" />
<asp:boundfield datafield="username" headertext="发布人" />
<asp:boundfield datafield="lineman" headertext="联系人" />
<asp:boundfield datafield="issuedate" headertext="发布时间"
dataformatstring="{0:d}" />
</columns>
<footerstyle backcolor="#990000" font-bold="true" forecolor="white" />
<rowstyle backcolor="#fffbd6" forecolor="#333333" />
<selectedrowstyle backcolor="#ffcc66" font-bold="true" forecolor="navy" />
<pagerstyle backcolor="#ffcc66" forecolor="#333333" horizontalalign="right" />
<headerstyle backcolor="#990000" font-bold="true" forecolor="white" />
<alternatingrowstyle backcolor="white" />
</asp:gridview>
</asp:panel>
<asp:checkbox id="cball" runat="server" autopostback="true"
font-size="9pt" oncheckedchanged="cball_checkedchanged"
text="全选/反选" />
<asp:button id="btnupdatetime" runat="server" onclick="btnupdatetime_click"
text="更新发布时间" />
</div>
</form>
</body>
</html>
default.aspx.cs
using system;
using system.data;
using system.configuration;
using system.collections;
using system.web;
using system.web.security;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.web.ui.htmlcontrols;
using system.text;
using system.data.sqlclient;
public partial class index : system.web.ui.page
{
sqlconnection sqlcon;
string strcon = configurationmanager.appsettings["constr"];
protected void page_load(object sender, eventargs e)
{
if (!ispostback)
{
this.gv_databind();
}
}
public void gv_databind()
{
string sqlstr = "select * from tb_inf";
sqlcon = new sqlconnection(strcon);
sqldataadapter da = new sqldataadapter(sqlstr, sqlcon);
dataset ds = new dataset();
sqlcon.open();
da.fill(ds, "tb_inf");
sqlcon.close();
this.gridview1.datasource = ds;
this.gridview1.datakeynames = new string[] { "id" };
this.gridview1.databind();
if (gridview1.rows.count > 0)
{
return;//有数据，不要处理
}
else//显示表头并显示没有数据的提示信息
{
strhelper.gridviewheader(gridview1);
}
}
protected void gridview1_rowdatabound(object sender, gridviewroweventargs e)
{
if (e.row.rowtype == datacontrolrowtype.datarow)
{
string gintro = e.row.cells[4].text;
e.row.cells[4].text = strhelper.getfirststring(gintro, 12);
}
}
protected void gridview1_selectedindexchanging(object sender, gridviewselecteventargs e)
{
string id = this.gridview1.datakeys[e.newselectedindex].value.tostring();
sqlcon = new sqlconnection(strcon);
sqlcommand com = new sqlcommand("select [check] from tb_inf where id='" + id + "'", sqlcon);
sqlcon.open();
string count = convert.tostring(com.executescalar());
if (count == "false")
{
count = "1";
}
else
{
count = "0";
}
com.commandtext = "update tb_inf set [check]=" + count + " where id=" + id;
com.executenonquery();
sqlcon.close();
this.gv_databind();
}
protected void gridview1_pageindexchanging(object sender, gridviewpageeventargs e)
{
this.gridview1.pageindex = e.newpageindex;
this.gv_databind();
}
protected void cball_checkedchanged(object sender, eventargs e)
{
for (int i = 0; i <= gridview1.rows.count - 1; i++)//遍历
{
checkbox cbox = (checkbox)gridview1.rows[i].findcontrol("cbsingleormore");
if (cball.checked == true)
{
cbox.checked = true;
}
else
{
cbox.checked = false;
}
}
}
protected void btnupdatetime_click(object sender, eventargs e)
{
stringbuilder builder = new stringbuilder();
int i = 0;
foreach (gridviewrow row in this.gridview1.rows)//循环遍历gridview控件中行，拼装in子句
{
checkbox cbox = row.findcontrol("cbsingleormore") as checkbox;
if (cbox.checked)//判断复选框是否被选中
{
//当数据行中的复选框被选中时，即将该行记录的主键值放入in子句中
builder.appendformat("'{0}',", this.gridview1.datakeys[row.rowindex].value.tostring());
i++;
continue;
}
continue;
}
if (builder.tostring().length == 0)//当in子句中没有任何数据行，则弹出提示
{
strhelper.alert("没有选中任何数据行，请重新选择！");
return;
}
//移除stringbuilder对象中的最后一个“,”
builder.remove(builder.tostring().lastindexof(","), 1);
//拼装sql语句
string sqlbuildercopy = string.format("update tb_inf set issuedate='{0}' where id in ({1})", datetime.now.tostring(), builder.tostring());
sqlcon = new sqlconnection(strcon);//创建数据库连接
sqlcommand sqlcom;//创建命令对象变量
int result = 0;
if (sqlcon.state.equals(connectionstate.closed))
sqlcon.open();//打开数据库连接
sqlcom = new sqlcommand(sqlbuildercopy, sqlcon);
sqltransaction tran = sqlcon.begintransaction();//实例化事务，注意实例化事务必须在数据库连接开启状态下
sqlcom.transaction = tran;//将命令对象与连接对象关联
try
{
result = sqlcom.executenonquery();//接收影响的行数
tran.commit();//提交事务
}
catch (sqlexception ex)
{
strhelper.alert(string.format("sql语句发生了异常，异常如下所示:\n{0}", ex.message));
tran.rollback();//出现异常，即回滚事务，防止出现脏数据
return;
}
finally
{
sqlcon.close();
}
if (result == i)//判断影响行数是否等于选中的数据行
{
strhelper.alert("数据更新成功！");
}
else
{
strhelper.alert("数据更新失败，事务已回滚！");
}
gv_databind();//重新绑定控件数据
return;
}
}
strhelper.cs
using system;
using system.data;
using system.configuration;
using system.linq;
using system.web;
using system.web.security;
using system.web.ui;
using system.web.ui.htmlcontrols;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.xml.linq;
//引入如下命名空间
using system.text.regularexpressions;
using system.text;
/// <summary>
///strhelper 的摘要说明
/// </summary>
public class strhelper
{
public strhelper(){}
/// <summary>
/// 截取字符串函数
/// </summary>
/// <param name="str">所要截取的字符串</param>
/// <param name="num">截取字符串的长度</param>
/// <returns></returns>
static public string getsubstring(string str, int num)
{
#region
return (str.length > num) ? str.substring(0, num) + "..." : str;
#endregion
}
/// <summary>
/// 截取字符串优化版
/// </summary>
/// <param name="stringtosub">所要截取的字符串</param>
/// <param name="length">截取字符串的长度</param>
/// <returns></returns>
public static string getfirststring(string stringtosub, int length)
{
#region
regex regex = new regex("[\u4e00-\u9fa5]+", regexoptions.compiled);
char[] stringchar = stringtosub.tochararray();
stringbuilder sb = new stringbuilder();
int nlength = 0;
bool iscut = false;
for (int i = 0; i < stringchar.length; i++)
{
if (regex.ismatch((stringchar[i]).tostring()))//regex.ismatch指示正则表达式在输入字符串中是否找到匹配项
{
sb.append(stringchar[i]);//将信息追加到当前 stringbuilder 的结尾
nlength += 2;
}
else
{
sb.append(stringchar[i]);
nlength = nlength + 1;
}
if (nlength > length)//替换字符串
{
iscut = true;
break;
}
}
if (iscut)
return sb.tostring() + "...";
else
return sb.tostring();
#endregion
}
/// 弹出javascript小窗口
/// </summary>
/// <param name="js">窗口信息</param>
public static void alert(string message)
{
#region
string js = @"<script language='javascript'>
alert('" + message + "');</script>";
httpcontext.current.response.write(js);
#endregion
}
public static void gridviewheader(gridview gdv)//显示表头并显示没有数据的提示信息
{
//表头的设置
gridviewrow row = new gridviewrow(-1, -1, datacontrolrowtype.emptydatarow, datacontrolrowstate.normal);
foreach (datacontrolfield field in gdv.columns)
{
tablecell cell = new tablecell();
cell.text = field.headertext;
cell.width = field.headerstyle.width;
cell.height = field.headerstyle.height;
cell.forecolor = field.headerstyle.forecolor;
cell.font.size = field.headerstyle.font.size;
cell.font.bold = field.headerstyle.font.bold;
cell.font.name = field.headerstyle.font.name;
cell.font.strikeout = field.headerstyle.font.strikeout;
cell.font.underline = field.headerstyle.font.underline;
cell.backcolor = field.headerstyle.backcolor;
cell.verticalalign = field.headerstyle.verticalalign;
cell.horizontalalign = field.headerstyle.horizontalalign;
cell.cssclass = field.headerstyle.cssclass;
cell.bordercolor = field.headerstyle.bordercolor;
cell.borderstyle = field.headerstyle.borderstyle;
cell.borderwidth = field.headerstyle.borderwidth;
row.cells.add(cell);
}
tableitemstyle headstyle = gdv.headerstyle;
tableitemstyle emptystyle = gdv.emptydatarowstyle;
emptystyle.width = headstyle.width;
emptystyle.height = headstyle.height;
emptystyle.forecolor = headstyle.forecolor;
emptystyle.font.size = headstyle.font.size;
emptystyle.font.bold = headstyle.font.bold;
emptystyle.font.name = headstyle.font.name;
emptystyle.font.strikeout = headstyle.font.strikeout;
emptystyle.font.underline = headstyle.font.underline;
emptystyle.backcolor = headstyle.backcolor;
emptystyle.verticalalign = headstyle.verticalalign;
emptystyle.horizontalalign = headstyle.horizontalalign;
emptystyle.cssclass = headstyle.cssclass;
emptystyle.bordercolor = headstyle.bordercolor;
emptystyle.borderstyle = headstyle.borderstyle;
emptystyle.borderwidth = headstyle.borderwidth;
//空白行的设置
gridviewrow row1 = new gridviewrow(0, -1, datacontrolrowtype.emptydatarow, datacontrolrowstate.normal);
tablecell cell1 = new tablecell();
cell1.text = "没有相关数据可以显示！";
cell1.backcolor = system.drawing.color.white;
row1.cells.add(cell1);
cell1.columnspan = 6;//合并列
if (gdv.controls.count == 0)
{
gdv.page.response.write("<script language='javascript'>alert('必须在初始化表格类之前执行databind方法并设置emptydatatext属性不为空!');</script>");
}
else
{
gdv.controls[0].controls.clear();
gdv.controls[0].controls.addat(0, row);
gdv.controls[0].controls.addat(1, row1);
}
}
}