如何排查VB.NET操作MySQL时偶发的System.NullReferenceException异常？

我编写了如下VB.NET子过程GetData用于从MySQL获取数据，并通过MySql_GetFhMain调用它查询tblfatora1表，但运行时偶尔抛出System.NullReferenceException: 'Object reference not set to an instance of an object.'，即使xDtMain已有多条数据，求排查方法。

相关代码 #

GetData子过程 #

'GetData
Public Sub GetData(ByVal SqlStr As String, ByVal xDt As DataTable, ByVal xPar() As MySqlParameter)
    Using xCmd As New MySqlCommand() With {
                .CommandType = CommandType.Text,
                .CommandText = SqlStr,
                .Connection = _ServerConnStr
                }

        If xPar IsNot Nothing Then
            For i As Integer = 0 To xPar.Length - 1
                xCmd.Parameters.Add(xPar(i))
            Next
        End If

        Using xDa As New MySqlDataAdapter(xCmd)
            xDa.Fill(xDt)
        End Using
        xDt.Dispose()
    End Using
End Sub

MySql_GetFhMain调用过程 #

Public Sub MySql_GetFhMain()
    xDtMain = New DataTable()

    Dim xPar(2) As MySqlParameter
    xPar(0) = New MySqlParameter("@Date1", MySqlDbType.Date) With {
                                .Value = Date1}
    xPar(1) = New MySqlParameter("@Date2", MySqlDbType.Date) With {
                                .Value = Date2}
    xPar(2) = New MySqlParameter("@FhReso", DbType.Int32) With {
                                .Value = FhReso}

        xSqlStr = "SELECT 
            FhID, FhRef, FhCode, FhDate, FhQuan, FhPurPrice,  
            FhPurTotal, FhCarNo, FhResoDetails, FhPalletQuan, 
            FhPalletPrice, accreso.AccName AS ResoName, 
            accdriv.AccName AS DriverName, CONCAT(CategoryName,'-',ProductName) AS ProductName,

            (SELECT GROUP_CONCAT(AccName) FROM tblaccounts 
                INNER JOIN tblfatora2 ON 
                    tblfatora2.FbCus = tblaccounts.AccID 
                            where FIND_IN_SET(AccID, FbCus) AND tblfatora2.FhRef = tblfatora1.FhRef)AS TheCustomers,

            (SELECT GROUP_CONCAT(PlaceName) FROM tblplaces 
                INNER JOIN tblfatora2 ON 
                    tblfatora2.FbCusPlace = tblplaces.PlaceID 
                            where FIND_IN_SET(PlaceID, FbCusPlace) AND tblfatora2.FhRef = tblfatora1.FhRef)AS ThePlaces , 

            (FhQuan -  (SELECT IFNULL(SUM(FbQuan), 0) FROM tblfatora2 WHERE FhRef = tblfatora1.FhRef)) AS TheRemain, 
ProductPallet, PlaceName, FhDriver, FhProduct, FhReso, FhResoPlace     
                                   
                                        FROM tblfatora1
                                    INNER JOIN tblproducts ON tblproducts.ProductID = tblfatora1.FhProduct
                                    INNER JOIN tblaccounts accreso ON accreso.AccID = tblfatora1.FhReso
                                    INNER JOIN tblaccounts accdriv ON accdriv.AccID = tblfatora1.FhDriver  
                                    INNER JOIN tblcategories cat ON cat.CategoryID = tblproducts.ProductCategory 
                                    INNER JOIN tblcurrencies curr ON accdriv.AccCurrID = curr.CurrencyID
                                    INNER JOIN tblplaces plc ON plc.PlaceID = tblfatora1.FhResoPlace 
                                    WHERE (FhDate Between @Date1 And @Date2)"
   
    xClsMySql.GetData(xSqlStr, xDtMain, xPar)

End Sub

排查方法 #

• 检查数据库连接对象_ServerConnStr的状态：GetData中直接将_ServerConnStr赋值给命令对象的连接属性，如果该连接对象偶发未初始化、连接断开且未重连，会触发空引用。建议在使用前增加连接有效性校验：

  If _ServerConnStr Is Nothing OrElse _ServerConnStr.State <> ConnectionState.Open Then
      ' 实现连接初始化/重连逻辑，确保连接可用
  End If
  

• 移除xDt.Dispose()调用：GetData在填充DataTable后立刻调用Dispose()，但调用方MySql_GetFhMain后续可能仍会访问xDtMain。偶发场景下，调用方访问已释放的DataTable会引发空引用，应将DataTable的释放权交给调用方。

• 校验参数数组的完整性：虽然代码中已初始化所有参数，但可增加参数非空校验，避免因参数对象异常导致的问题：

  If xPar IsNot Nothing Then
      For Each param In xPar
          If param IsNot Nothing Then
              xCmd.Parameters.Add(param)
          End If
      Next
  End If
  

• 处理SQL查询中的Null值：SQL中的CONCAT(CategoryName,'-',ProductName)如果任一字段为Null，会返回Null值，若后续绑定UI时未处理Null可能引发空引用。可修改为CONCAT(IFNULL(CategoryName,''),'-',IFNULL(ProductName,''))避免Null结果。

• 排查并发访问问题：如果xDtMain是全局变量，多线程同时访问或修改会导致偶发的空引用。建议将其改为局部变量，或通过锁机制保护全局变量的访问。

• 添加异常日志：在GetData和调用方增加异常捕获，记录异常堆栈、当前SQL语句、参数值、连接状态等信息，帮助定位偶发问题：

  Public Sub GetData(ByVal SqlStr As String, ByVal xDt As DataTable, ByVal xPar() As MySqlParameter)
      Try
          Using xCmd As New MySqlCommand() With {
                      .CommandType = CommandType.Text,
                      .CommandText = SqlStr,
                      .Connection = _ServerConnStr
                      }
  
              If xPar IsNot Nothing Then
                  For i As Integer = 0 To xPar.Length - 1
                      xCmd.Parameters.Add(xPar(i))
                  Next
              End If
  
              Using xDa As New MySqlDataAdapter(xCmd)
                  xDa.Fill(xDt)
              End Using
          End Using
      Catch ex As Exception
          ' 记录日志：包含ex.Message、ex.StackTrace、SqlStr、参数值等
          Throw ' 重新抛出异常，不掩盖错误
      End Try
  End Sub
  

内容的提问来源于stack exchange，提问作者M.J