动态组合SQL语句方式实现批量更新的实例(3)

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

复制代码 代码如下: