動(dòng)態(tài)組合SQL語(yǔ)句方式實(shí)現(xiàn)批量更新的實(shí)例
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>供求信息網(wǎng)審核發(fā)布信息</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="沒(méi)有相關(guān)數(shù)據(jù)可以顯示！"
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="發(fā)布內(nèi)容" />
<asp:BoundField DataField="userName" HeaderText="發(fā)布人" />
<asp:BoundField DataField="lineMan" HeaderText="聯(lián)系人" />
<asp:BoundField DataField="issueDate" HeaderText="發(fā)布時(shí)間"
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="更新發(fā)布時(shí)間" />
</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;//有數(shù)據(jù)，不要處理
}
else//顯示表頭并顯示沒(méi)有數(shù)據(jù)的提示信息
{
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)//循環(huán)遍歷GridView控件中行，拼裝IN子句
{
CheckBox cbox = row.FindControl("cbSingleOrMore") as CheckBox;
if (cbox.Checked)//判斷復(fù)選框是否被選中
{
//當(dāng)數(shù)據(jù)行中的復(fù)選框被選中時(shí)，即將該行記錄的主鍵值放入IN子句中
builder.AppendFormat("'{0}',", this.GridView1.DataKeys[row.RowIndex].Value.ToString());
i++;
continue;
}
continue;
}
if (builder.ToString().Length == 0)//當(dāng)IN子句中沒(méi)有任何數(shù)據(jù)行，則彈出提示
{
StrHelper.Alert("沒(méi)有選中任何數(shù)據(jù)行，請(qǐng)重新選擇！");
return;
}
//移除StringBuilder對(duì)象中的最后一個(gè)“,”
builder.Remove(builder.ToString().LastIndexOf(","), 1);
//拼裝SQL語(yǔ)句
string SqlBuilderCopy = string.Format("Update tb_inf set issueDate='{0}' WHERE id IN ({1})", DateTime.Now.ToString(), builder.ToString());
sqlcon = new SqlConnection(strCon);//創(chuàng)建數(shù)據(jù)庫(kù)連接
SqlCommand sqlcom;//創(chuàng)建命令對(duì)象變量
int result = 0;
if (sqlcon.State.Equals(ConnectionState.Closed))
sqlcon.Open();//打開(kāi)數(shù)據(jù)庫(kù)連接
sqlcom = new SqlCommand(SqlBuilderCopy, sqlcon);
SqlTransaction tran = sqlcon.BeginTransaction();//實(shí)例化事務(wù)，注意實(shí)例化事務(wù)必須在數(shù)據(jù)庫(kù)連接開(kāi)啟狀態(tài)下
sqlcom.Transaction = tran;//將命令對(duì)象與連接對(duì)象關(guān)聯(lián)
try
{
result = sqlcom.ExecuteNonQuery();//接收影響的行數(shù)
tran.Commit();//提交事務(wù)
}
catch (SqlException ex)
{
StrHelper.Alert(string.Format("SQL語(yǔ)句發(fā)生了異常，異常如下所示:\n{0}", ex.Message));
tran.Rollback();//出現(xiàn)異常，即回滾事務(wù)，防止出現(xiàn)臟數(shù)據(jù)
return;
}
finally
{
sqlcon.Close();
}
if (result == i)//判斷影響行數(shù)是否等于選中的數(shù)據(jù)行
{
StrHelper.Alert("數(shù)據(jù)更新成功！");
}
else
{
StrHelper.Alert("數(shù)據(jù)更新失敗，事務(wù)已回滾！");
}
GV_DataBind();//重新綁定控件數(shù)據(jù)
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 的摘要說(shuō)明
/// </summary>
public class StrHelper
{
public StrHelper(){}
/// <summary>
/// 截取字符串函數(shù)
/// </summary>
/// <param name="str">所要截取的字符串</param>
/// <param name="num">截取字符串的長(zhǎng)度</param>
/// <returns></returns>
static public string GetSubString(string str, int num)
{
#region
return (str.Length > num) ? str.Substring(0, num) + "..." : str;
#endregion
}
/// <summary>
/// 截取字符串優(yōu)化版
/// </summary>
/// <param name="stringToSub">所要截取的字符串</param>
/// <param name="length">截取字符串的長(zhǎng)度</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指示正則表達(dá)式在輸入字符串中是否找到匹配項(xiàng)
{
sb.Append(stringChar[i]);//將信息追加到當(dāng)前 StringBuilder 的結(jié)尾
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)//顯示表頭并顯示沒(méi)有數(shù)據(jù)的提示信息
{
//表頭的設(shè)置
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;
//空白行的設(shè)置
GridViewRow row1 = new GridViewRow(0, -1, DataControlRowType.EmptyDataRow, DataControlRowState.Normal);
TableCell cell1 = new TableCell();
cell1.Text = "沒(méi)有相關(guān)數(shù)據(jù)可以顯示！";
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('必須在初始化表格類之前執(zhí)行DataBind方法并設(shè)置EmptyDataText屬性不為空!');</script>");
}
else
{
gdv.Controls[0].Controls.Clear();
gdv.Controls[0].Controls.AddAt(0, row);
gdv.Controls[0].Controls.AddAt(1, row1);
}
}
}
相關(guān)文章
Entity Framework Core對(duì)Web項(xiàng)目生成數(shù)據(jù)庫(kù)表
這篇文章介紹了Entity Framework Core對(duì)Web項(xiàng)目生成數(shù)據(jù)庫(kù)表的方法，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-03-03
ASP.NET Core自動(dòng)生成小寫(xiě)破折號(hào)路由的實(shí)現(xiàn)方法
這篇文章主要介紹了ASP.NET Core自動(dòng)生成小寫(xiě)破折號(hào)路由的實(shí)現(xiàn)方法，幫助大家更好的理解和學(xué)習(xí)使用ASP.NET Core，感興趣的朋友可以了解下2021-04-04
ASP.NET服務(wù)器控件開(kāi)發(fā)（1）封裝html
在我們的項(xiàng)目開(kāi)發(fā)中，由于ASP.NET的服務(wù)器控件功能有限，所以我們經(jīng)常會(huì)自己定義特定的服務(wù)器控件，來(lái)滿足開(kāi)發(fā)中特定的業(yè)務(wù)要求?？梢?jiàn)知道如何開(kāi)發(fā)ASP.NET服務(wù)器控件是非常有必要的2015-12-12
asp.net 安全的截取指定長(zhǎng)度的html或者ubb字符串
在將html代碼輸出到頁(yè)面時(shí)，有時(shí)候會(huì)需要截?cái)嘧址Ａ糁付ㄩL(zhǎng)度的字符串，由于html中有些標(biāo)簽必須成對(duì)出現(xiàn)，所以在截取html時(shí)需要特別注意，不能因?yàn)榻財(cái)鄦?wèn)題把頁(yè)面搞亂掉。2010-01-01
我們做開(kāi)發(fā)的一做基本都是一天，對(duì)著屏幕眼鏡肯定會(huì)累，下面教大家一個(gè)VS2013設(shè)置護(hù)眼背景色的方法。2016-04-04
這篇文章主要介紹了ASP.NET防止SQL注入的方法,結(jié)合具體實(shí)例形式分析了asp.net基于字符串過(guò)濾實(shí)現(xiàn)防止SQL注入的相關(guān)操作技巧,需要的朋友可以參考下2017-03-03
.Net中關(guān)于stirng轉(zhuǎn)System.Type的一種實(shí)現(xiàn)思路詳解
這篇文章主要給大家介紹了.Net中關(guān)于stirng轉(zhuǎn)System.Type的一種實(shí)現(xiàn)思路的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2018-05-05
asp.net下xml當(dāng)作導(dǎo)航數(shù)據(jù)源實(shí)現(xiàn)動(dòng)態(tài)權(quán)限
如果有權(quán)限的話 可以通過(guò)節(jié)點(diǎn)的Roles屬性判斷當(dāng)前登陸的賬號(hào)角色名是否符合然后判斷輸出這樣的話您就可以直接操作XML數(shù)據(jù) 而不用考慮別的。2009-12-12
VS2019下opencv4.1.2配置圖文教程（永久配置）
這篇文章主要介紹了VS2019下opencv4.1.2配置圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-11-11