动态组合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);
        }
    }
}

时间： 2024-11-03 06:46:41

动态组合SQL语句方式实现批量更新的实例_实用技巧的相关文章

.Net core下直接执行SQL语句并生成DataTable的实现方法_实用技巧

.net core可以执行SQL语句,但是只能生成强类型的返回结果.例如var blogs = context.Blogs.FromSql("SELECT * FROM dbo.Blogs").ToList().而不允许返回DataSet.DataTable等弱类型.可能由于这个原因没有实现在.net core中DataTable,然而DataTable还是可能会用到的.我们这里就有一个数据仓库的需求,允许用户自行编写类似SQL语句,然后执行,以表格展示.因为语句是千变万化的,因此我也

Linq to SQL Delete时遇到问题的解决方法_实用技巧

1.1.   Code1: using (PubsDataContext pubsContent = new PubsDataContext()) {     pubsContent.Log = Console.Out;     Author author = pubsContent.Authors.Single(a => a.au_id == "111-11-1111");     pubsContent.Authors.DeleteOnSubmit(author);    

Repeater控件动态变更列(Header,Item和Foot)信息实现思路_实用技巧

需求开发一个小报表,显示最近五个月的summary的数量统计,报表会随月份的变化而变化,如下图.第一列[Department]固定,第二至第六列,也就是说Nov 2012 这列会在下月的时候消失,其后的列会向前移,最后一列Mar 2013 会变为Apr 2013. 下图中,最底一行是显示每一列的总数(除第一列外). 为了这个报表,Insus.NET决定使用Repeater控件来实现.难度在于动态显法第二列至第六列的列名,以及绑定数据.最后一行计算总计的,只要完成上面的动态绑定之后,也算不上问题,

asp.net自动更新组件分享_实用技巧

一. 摘要 前两天在博客上发布了一篇英文的自动更新组件文章Release a AutoUpdater tool,那么在这篇文章中,我们也对其功能进行一些简单说明,这个组件非常简单,所以大家可以下载进行一些改进.众所周知,对于一般的软件开发,在开始的时候都会有一个技术选型的阶段,最大的选型就是首先要确定是选择Client/Server模式还是Browser/Server模式.综合而论:两者各有优劣,在很多方面都不能被对方互相取代,如在适用Internet.维护工作量等方面,B/S比C/S要强很多:

动态指定任意类型的ObjectDataSource对象的查询参数_实用技巧

ObjectDataSource在使用时需要事先指定查询方法SelectMethod(其实还有InsertMethod,UpdateMethod和DeleteMethod),TypeName和DataObjectTypeName,然后我们只需要编写好SelectMethod方法的实现,如果需要分页,那么还要指定MaximumRwosParameterName属性和StartRowIndexParameterName,然后在SelectMethod方法中加上相应的参数,当然,SelectCount

Repeater控件动态变更列(Header,Item和Foot)信息(重构cs)_实用技巧

早上有分享<Repeater控件动态变更列(Header,Item和Foot)信息> ,是动态变更一些列的内容. 虽然它算不上是完全动态化,但它已经达到初期想要的效果.如果你稍有仔细,也能轻易把它弄出来.现另开一篇,Insus.NET不是重新另外写,而是想重构cs的代码,因为前一篇的代码虽然简单,但代码冗余过多. 重构开始:首先把这五个变量删除,因为在重构过程中,已经不需要这五个变量了. 复制代码 代码如下: //宣告5个变量,将用来存储那5个月份每个部分的数量 decimal c1, c2,

Extjs4.1.x 框架搭建 采用Application动态按需加载MVC各模块完美实现_实用技巧

中午的时候发了第一篇 Extjs4.1.x 框架搭建 采用Application动态按需加载MVC各模块 ,发现实现上还是有问题,有很多理解不到位的地方,晚上详细解决下了,终于实现MVC各模块按需加载了,哈皮. 上篇文章中,关于ExtJs这个玩意的评论就跟java和.Net那个好一样,既然上了贼船,就难下了,而且对于企业级的应用我个人觉得Extjs框架还是不错的,尤其是没有UI设计的团队(苦逼的程序员就兼UI吧),起码难得发现一个做的比较好的UI框架(国产的miniUI貌似看的过去,其他的就有点

asp.net实现批量删除实例_实用技巧

本文实例讲述了asp.net实现批量删除功能的方法.对于asp.net的学习有一定的参考价值.分享给大家供大家参考之用.具体实现方法入戏: .aspx文件代码如下: <asp:GridView ID="GridView1" runat="server" Width="100%" EmptyDataText="暂时无数据" BorderColor="White" OnRowDeleting="

将Access数据库中数据导入到SQL Server中的详细方法实例_实用技巧

Default.aspx 复制代码 代码如下: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="AccessToSQL.aspx.cs" Inherits="AccessToSQL" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "