GridView中点击CheckBox/Button时更新Oracle数据库的技术问询

场景说明 #

在GridView中新增一列并添加CheckBox/Button控件，需求为：用户勾选CheckBox或点击Button时，获取GridView第二列的发票号，将Oracle数据库对应发票记录的selected_invoice字段更新为1。已实现以下C#代码，针对该场景梳理技术问题与优化方案：

已实现代码 #

protected void GridViewRemittance_RowCommand(object sender, GridViewCommandEventArgs e) {
  if (e.CommandName == "UpdateRow") {
    ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + e.CommandArgument + "');", true);
    string invoiceID = Convert.ToString(e.CommandArgument);
    UpdateDatabase(invoiceID);
  }
}

protected void UpdateDatabase(string i) {
  string invoiceNumber = i;
  using (OracleConnection Conn = new OracleConnection(strCon)) {
    using (OracleCommand cmd = new OracleCommand()) {
      Conn.Open(); cmd.Connection = Conn;
      cmd.CommandText = "Update ap_lineitem_staging SET selected_invoice = 1 where pre_invoice_num='" + invoiceNumber + "'";
      cmd.ExecuteNonQuery();
    }
  }
}

技术优化与问题处理建议 #

1. 修复SQL注入风险 #

当前代码直接拼接SQL语句，存在严重安全隐患，必须改用参数化查询：

protected void UpdateDatabase(string invoiceNumber) {
  using (OracleConnection Conn = new OracleConnection(strCon)) {
    using (OracleCommand cmd = new OracleCommand()) {
      Conn.Open();
      cmd.Connection = Conn;
      cmd.CommandText = "Update ap_lineitem_staging SET selected_invoice = 1 where pre_invoice_num = :InvoiceNum";
      cmd.Parameters.Add(":InvoiceNum", OracleDbType.Varchar2).Value = invoiceNumber;
      cmd.ExecuteNonQuery();
    }
  }
}

2. CheckBox事件绑定处理 #

若使用CheckBox控件，需在RowDataBound事件中配置触发逻辑，确保能获取对应发票号：

protected void GridViewRemittance_RowDataBound(object sender, GridViewRowEventArgs e) {
  if (e.Row.RowType == DataControlRowType.DataRow) {
    CheckBox cb = (CheckBox)e.Row.FindControl("CheckBox1");
    if (cb != null) {
      // 获取第二列发票号（索引从0开始）
      string invoiceNum = e.Row.Cells[1].Text;
      cb.AutoPostBack = true;
      cb.CheckedChanged += CheckBox_CheckedChanged;
      // 存储发票号到控件ViewState
      cb.ViewState["InvoiceNum"] = invoiceNum;
    }
  }
}

protected void CheckBox_CheckedChanged(object sender, EventArgs e) {
  CheckBox cb = (CheckBox)sender;
  string invoiceNum = cb.ViewState["InvoiceNum"].ToString();
  UpdateDatabase(invoiceNum);
}

3. 确保Button的CommandArgument绑定正确 #

在GridView的ItemTemplate中，需将Button的CommandArgument绑定到发票号：

<asp:Button ID="btnUpdate" runat="server" Text="选择" CommandName="UpdateRow" 
            CommandArgument='<%# Eval("pre_invoice_num") %>' />

若需通过列索引获取，可改为：

<asp:Button ID="btnUpdate" runat="server" Text="选择" CommandName="UpdateRow" 
            CommandArgument='<%# ((GridViewRow)Container).Cells[1].Text %>' />

4. 添加异常处理与结果反馈 #

增加异常捕获逻辑，避免数据库操作失败导致页面崩溃，同时给用户明确反馈：

protected void UpdateDatabase(string invoiceNumber) {
  try {
    using (OracleConnection Conn = new OracleConnection(strCon)) {
      using (OracleCommand cmd = new OracleCommand()) {
        Conn.Open();
        cmd.Connection = Conn;
        cmd.CommandText = "Update ap_lineitem_staging SET selected_invoice = 1 where pre_invoice_num = :InvoiceNum";
        cmd.Parameters.Add(":InvoiceNum", OracleDbType.Varchar2).Value = invoiceNumber;
        int affectedRows = cmd.ExecuteNonQuery();
        if (affectedRows == 0) {
          ScriptManager.RegisterStartupScript(this, GetType(), "alert", "alert('未找到对应发票记录');", true);
        }
      }
    }
  } catch (Exception ex) {
    ScriptManager.RegisterStartupScript(this, GetType(), "alert", $"alert('更新失败：{ex.Message}');", true);
    // 此处可添加日志记录逻辑
  }
}

5. 优化回传提示脚本 #

若页面使用UpdatePanel，建议用ScriptManager.RegisterStartupScript替代ClientScript.RegisterStartupScript，兼容局部回传场景：

ScriptManager.RegisterStartupScript(this, GetType(), "alertMsg", $"alert('{e.CommandArgument}');", true);

内容的提问来源于stack exchange，提问作者Rakshitha R