VB.NET中CellValueChanged后DataTable表达式列不即时更新问题

问题说明 #

修改DataGridView单元格值后，关联的DataTable表达式列MATERIALCOLORNAME无法即时更新，操作过程如下：

相关代码 #

DataTable创建代码 #

Private Sub CreateDataTable()
    If m_DataTable IsNot Nothing Then Return
    m_DataTable = New DataTable
    'Columns
    m_DataTable.Columns.Add("CODEPRODUCT", GetType(String))
    m_DataTable.Columns.Add("DESCRIPTION", GetType(String))
    m_DataTable.Columns.Add("COLORCODE", GetType(String))
    m_DataTable.Columns.Add("COLORNAME", GetType(String))
    m_DataTable.Columns.Add("MATERIAL", GetType(String))
    m_DataTable.Columns.Add("MATERIALCOLORNAME", GetType(String), "TRIM(IIF(MATERIAL Is Null Or SUBSTRING(MATERIAL, 1, 1) = '-', '', MATERIAL) + IIF(COLORNAME Is Null Or SUBSTRING(COLORNAME, 1, 1) = '-', '', ' ' + COLORNAME))")
End Sub

其他核心代码 #

Public m_DataTable As DataTable

Public Function GetConnectionString2() As String
    Dim strCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIAL.accdb;Persist Security Info=False;"
    Return strCon
End Function

Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
    If e.RowIndex < 0 OrElse e.RowIndex < 0 Then Return
    Dim dgv = DirectCast(sender, DataGridView)
    'If Not dgv.IsCurrentCellDirty Then Return
    Dim row = dgv.Rows(e.RowIndex)
    Dim arrQueries() = {
        New With {
            .KeyCell = row.Cells("CODEPRODUCT"),
            .ValueCell = row.Cells("DESCRIPTION"),
            .Table = "PRODUCT"},
        New With {
            .KeyCell = row.Cells("SIZEPRODUCT"),
            .ValueCell = row.Cells("CATEGORY"),
            .Table = "SIZEPRODUCT"},
        New With {
            .KeyCell = row.Cells("COLORCODE"),
            .ValueCell = row.Cells("COLORNAME"),
            .Table = "COLORCODE"},
        New With {
            .KeyCell = row.Cells("COLORCODE"),
            .ValueCell = row.Cells("MATERIAL"),
            .Table = "COLORCODE"}
    }
    If Not arrQueries.Any(
        Function(q) q.KeyCell.OwningColumn Is dgv.Columns(e.ColumnIndex)) Then
        Return
    End If
    Using con = New OleDbConnection(GetConnectionString2()), cmd = con.CreateCommand()
        con.Open()
        For Each q In arrQueries
            If q.KeyCell.Value IsNot Nothing AndAlso
                q.KeyCell.Value IsNot DBNull.Value AndAlso
                Not String.IsNullOrEmpty(q.KeyCell.Value.ToString()) Then
                cmd.CommandText = String.Format(
                    "SELECT {0} FROM {1} WHERE {2} = ?",
                    q.ValueCell.OwningColumn.Name, q.Table, q.KeyCell.OwningColumn.Name)
                cmd.Parameters.Clear()
                cmd.Parameters.Add("?", OleDbType.VarWChar).Value = q.KeyCell.Value
                q.ValueCell.Value = cmd.ExecuteScalar()
            Else
                q.ValueCell.Value = Nothing
            End If
        Next
    End Using
End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    CreateDataTable()
    DataGridView1.DataSource = m_DataTable
End Sub

问题原因 #

直接修改DataGridView单元格的Value属性，不会触发底层DataTable对应DataRow的变更通知机制，导致表达式列无法自动重新计算值。

解决方法 #

方法一：操作DataTable的DataRow（推荐） #

放弃直接修改DataGridView单元格，转而操作绑定的DataRowView/DataRow，这样能触发DataTable的变更通知，表达式列会自动更新：

修改后的DataGridView1_CellValueChanged事件代码：

Private Sub DataGridView1_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
    If e.RowIndex < 0 Then Return
    Dim dgv = DirectCast(sender, DataGridView)
    Dim row = dgv.Rows(e.RowIndex)
    ' 获取绑定的DataRowView
    Dim dataRowView = DirectCast(row.DataBoundItem, DataRowView)
    If dataRowView Is Nothing Then Return

    Dim arrQueries() = {
        New With {
            .KeyColName = "CODEPRODUCT",
            .ValueColName = "DESCRIPTION",
            .Table = "PRODUCT"},
        New With {
            .KeyColName = "SIZEPRODUCT",
            .ValueColName = "CATEGORY",
            .Table = "SIZEPRODUCT"},
        New With {
            .KeyColName = "COLORCODE",
            .ValueColName = "COLORNAME",
            .Table = "COLORCODE"},
        New With {
            .KeyColName = "COLORCODE",
            .ValueColName = "MATERIAL",
            .Table = "COLORCODE"}
    }

    Dim changedColName = dgv.Columns(e.ColumnIndex).Name
    If Not arrQueries.Any(Function(q) q.KeyColName = changedColName) Then
        Return
    End If

    Using con = New OleDbConnection(GetConnectionString2()), cmd = con.CreateCommand()
        con.Open()
        For Each q In arrQueries
            Dim keyValue = dataRowView(q.KeyColName)
            If keyValue IsNot DBNull.Value AndAlso Not String.IsNullOrEmpty(keyValue.ToString()) Then
                cmd.CommandText = String.Format(
                    "SELECT {0} FROM {1} WHERE {2} = ?",
                    q.ValueColName, q.Table, q.KeyColName)
                cmd.Parameters.Clear()
                cmd.Parameters.Add("?", OleDbType.VarWChar).Value = keyValue
                ' 修改DataRowView的值，触发表达式列更新
                dataRowView(q.ValueColName) = cmd.ExecuteScalar()
            Else
                dataRowView(q.ValueColName) = DBNull.Value
            End If
        Next
    End Using
End Sub

方法二：强制触发DataRow编辑结束 #

如果坚持直接修改DataGridView单元格，可在修改完成后调用EndEdit()触发表达式列重新计算，再刷新DataGridView：

在原有事件代码的Using块结束后添加：

' 触发DataRow编辑结束，强制计算表达式列
DirectCast(row.DataBoundItem, DataRowView).EndEdit()
' 刷新DataGridView确保显示最新值
dgv.Refresh()

来源说明 #

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