动态组合SQL语句方式实现批量更新的实例
Default.aspx
<%@PageLanguage="C#"AutoEventWireup="true"CodeFile="Index.aspx.cs"Inherits="Index"%>
<!DOCTYPEhtmlPUBLIC"-//W3C//DTDXHTML1.0Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<htmlxmlns="http://www.w3.org/1999/xhtml">
<headrunat="server">
<title>供求信息网审核发布信息</title>
</head>
<bodyclass="Font">
<formid="form1"runat="server">
<divstyle="text-align:left"align="left"><asp:PanelID="Panel2"runat="server">
<asp:GridViewID="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:CheckBoxID="cbSingleOrMore"runat="server"/>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundFieldDataField="id"HeaderText="信息ID"/>
<asp:BoundFieldDataField="name"HeaderText="信息主题"/>
<asp:BoundFieldDataField="type"HeaderText="信息分类"/>
<asp:BoundFieldDataField="content"HeaderText="发布内容"/>
<asp:BoundFieldDataField="userName"HeaderText="发布人"/>
<asp:BoundFieldDataField="lineMan"HeaderText="联系人"/>
<asp:BoundFieldDataField="issueDate"HeaderText="发布时间"
DataFormatString="{0:d}"/>
</Columns>
<FooterStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/>
<RowStyleBackColor="#FFFBD6"ForeColor="#333333"/>
<SelectedRowStyleBackColor="#FFCC66"Font-Bold="True"ForeColor="Navy"/>
<PagerStyleBackColor="#FFCC66"ForeColor="#333333"HorizontalAlign="Right"/>
<HeaderStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/>
<AlternatingRowStyleBackColor="White"/>
</asp:GridView>
</asp:Panel>
<asp:CheckBoxID="cbAll"runat="server"AutoPostBack="True"
Font-Size="9pt"OnCheckedChanged="cbAll_CheckedChanged"
Text="全选/反选"/>
<asp:ButtonID="btnUpdateTime"runat="server"onclick="btnUpdateTime_Click"
Text="更新发布时间"/>
</div>
</form>
</body>
</html>
Default.aspx.cs
usingSystem;
usingSystem.Data;
usingSystem.Configuration;
usingSystem.Collections;
usingSystem.Web;
usingSystem.Web.Security;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Web.UI.WebControls.WebParts;
usingSystem.Web.UI.HtmlControls;
usingSystem.Text;
usingSystem.Data.SqlClient;
publicpartialclassIndex:System.Web.UI.Page
{
SqlConnectionsqlcon;
stringstrCon=ConfigurationManager.AppSettings["conStr"];
protectedvoidPage_Load(objectsender,EventArgse)
{
if(!IsPostBack)
{
this.GV_DataBind();
}
}
publicvoidGV_DataBind()
{
stringsqlstr="select*fromtb_inf";
sqlcon=newSqlConnection(strCon);
SqlDataAdapterda=newSqlDataAdapter(sqlstr,sqlcon);
DataSetds=newDataSet();
sqlcon.Open();
da.Fill(ds,"tb_inf");
sqlcon.Close();
this.GridView1.DataSource=ds;
this.GridView1.DataKeyNames=newstring[]{"id"};
this.GridView1.DataBind();
if(GridView1.Rows.Count>0)
{
return;//有数据，不要处理
}
else//显示表头并显示没有数据的提示信息
{
StrHelper.GridViewHeader(GridView1);
}
}
protectedvoidGridView1_RowDataBound(objectsender,GridViewRowEventArgse)
{
if(e.Row.RowType==DataControlRowType.DataRow)
{
stringgIntro=e.Row.Cells[4].Text;
e.Row.Cells[4].Text=StrHelper.GetFirstString(gIntro,12);
}
}
protectedvoidGridView1_SelectedIndexChanging(objectsender,GridViewSelectEventArgse)
{
stringid=this.GridView1.DataKeys[e.NewSelectedIndex].Value.ToString();
sqlcon=newSqlConnection(strCon);
SqlCommandcom=newSqlCommand("select[check]fromtb_infwhereid=""+id+""",sqlcon);
sqlcon.Open();
stringcount=Convert.ToString(com.ExecuteScalar());
if(count=="False")
{
count="1";
}
else
{
count="0";
}
com.CommandText="updatetb_infset[check]="+count+"whereid="+id;
com.ExecuteNonQuery();
sqlcon.Close();
this.GV_DataBind();
}
protectedvoidGridView1_PageIndexChanging(objectsender,GridViewPageEventArgse)
{
this.GridView1.PageIndex=e.NewPageIndex;
this.GV_DataBind();
}
protectedvoidcbAll_CheckedChanged(objectsender,EventArgse)
{
for(inti=0;i<=GridView1.Rows.Count-1;i++)//遍历
{
CheckBoxcbox=(CheckBox)GridView1.Rows[i].FindControl("cbSingleOrMore");
if(cbAll.Checked==true)
{
cbox.Checked=true;
}
else
{
cbox.Checked=false;
}
}
}
protectedvoidbtnUpdateTime_Click(objectsender,EventArgse)
{
StringBuilderbuilder=newStringBuilder();
inti=0;
foreach(GridViewRowrowinthis.GridView1.Rows)//循环遍历GridView控件中行，拼装IN子句
{
CheckBoxcbox=row.FindControl("cbSingleOrMore")asCheckBox;
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语句
stringSqlBuilderCopy=string.Format("Updatetb_infsetissueDate="{0}"WHEREidIN({1})",DateTime.Now.ToString(),builder.ToString());
sqlcon=newSqlConnection(strCon);//创建数据库连接
SqlCommandsqlcom;//创建命令对象变量
intresult=0;
if(sqlcon.State.Equals(ConnectionState.Closed))
sqlcon.Open();//打开数据库连接
sqlcom=newSqlCommand(SqlBuilderCopy,sqlcon);
SqlTransactiontran=sqlcon.BeginTransaction();//实例化事务，注意实例化事务必须在数据库连接开启状态下
sqlcom.Transaction=tran;//将命令对象与连接对象关联
try
{
result=sqlcom.ExecuteNonQuery();//接收影响的行数
tran.Commit();//提交事务
}
catch(SqlExceptionex)
{
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
usingSystem; ///<summary> #endregion
usingSystem.Data;
usingSystem.Configuration;
usingSystem.Linq;
usingSystem.Web;
usingSystem.Web.Security;
usingSystem.Web.UI;
usingSystem.Web.UI.HtmlControls;
usingSystem.Web.UI.WebControls;
usingSystem.Web.UI.WebControls.WebParts;
usingSystem.Xml.Linq;
//引入如下命名空间
usingSystem.Text.RegularExpressions;
usingSystem.Text;
///StrHelper的摘要说明
///</summary>
publicclassStrHelper
{
publicStrHelper(){}
///<summary>
///截取字符串函数
///</summary>
///<paramname="str">所要截取的字符串</param>
///<paramname="num">截取字符串的长度</param>
///<returns></returns>
staticpublicstringGetSubString(stringstr,intnum)
{
#region
return(str.Length>num)?str.Substring(0,num)+"...":str;
#endregion
}
///<summary>
///截取字符串优化版
///</summary>
///<paramname="stringToSub">所要截取的字符串</param>
///<paramname="length">截取字符串的长度</param>
///<returns></returns>
publicstaticstringGetFirstString(stringstringToSub,intlength)
{
#region
Regexregex=newRegex("[\u4e00-\u9fa5]+",RegexOptions.Compiled);
char[]stringChar=stringToSub.ToCharArray();
StringBuildersb=newStringBuilder();
intnLength=0;
boolisCut=false;
for(inti=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)
returnsb.ToString()+"...";
else
returnsb.ToString();
#endregion
}
///弹出JavaScript小窗口
///</summary>
///<paramname="js">窗口信息</param>
publicstaticvoidAlert(stringmessage)
{
#region
stringjs=@"<Scriptlanguage="JavaScript">
alert(""+message+"");</Script>";
HttpContext.Current.Response.Write(js);
}
publicstaticvoidGridViewHeader(GridViewgdv)//显示表头并显示没有数据的提示信息
{
//表头的设置
GridViewRowrow=newGridViewRow(-1,-1,DataControlRowType.EmptyDataRow,DataControlRowState.Normal);
foreach(DataControlFieldfieldingdv.Columns)
{
TableCellcell=newTableCell();
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);
}
TableItemStyleheadStyle=gdv.HeaderStyle;
TableItemStyleemptyStyle=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;
//空白行的设置
GridViewRowrow1=newGridViewRow(0,-1,DataControlRowType.EmptyDataRow,DataControlRowState.Normal);
TableCellcell1=newTableCell();
cell1.Text="没有相关数据可以显示！";
cell1.BackColor=System.Drawing.Color.White;
row1.Cells.Add(cell1);
cell1.ColumnSpan=6;//合并列
if(gdv.Controls.Count==0)
{
gdv.Page.Response.Write("<scriptlanguage="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);
}
}
}
相关文章