存储过程更新数据时出现System.DBNull转System.String错误排查求助

我的存储过程在出错时会返回JSON结果，但更新数据时出现以下错误：
Unable to cast object of type 'System.DBNull' to type 'System.String'

请帮忙排查问题出在C#代码还是SQL存储过程中？

SQL存储过程代码 #

ALTER PROCEDURE [dbo].[SP_UpdateCentralDeviceStatus]

    @pMacA NVARCHAR(30),
    @pCustCode NVARCHAR(15),
    @pStatus NVARCHAR(15),
    @pJsonResult NVARCHAR(MAX) out
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;
     DECLARE @p_CustId INT = 0;
     --DECLARE @pJsonResult NVARCHAR(MAX);
     DECLARE @p_Cust_Status NVARCHAR(20);

    BEGIN TRY

         -- To validate customer status
         SELECT @p_CustId = ID,
                @p_Cust_Status = [Status]
         FROM dbo.tblCustomer
         WHERE CustomerCode = @pCustCode;

        IF @p_CustId = 0
        BEGIN
            SELECT @pJsonResult =
            (
                SELECT 'ERROR' AS C_ERROR,
                       'Customer Not Registered' AS MSG
                    FOR JSON PATH, INCLUDE_NULL_VALUES
            );
            RAISERROR(@pJsonResult, 16, 1);
        END;

        IF @p_Cust_Status <> 'Active'
        BEGIN

            SELECT @pJsonResult =
            (
                SELECT 'ERROR' AS RESULT,
                       'Customer ' + @p_Cust_Status AS MSG
                FOR JSON PATH, INCLUDE_NULL_VALUES
            );
            RAISERROR(@pJsonResult, 16, 1);

        END;

        IF NOT EXISTS (SELECT * FROM dbo.tblCustomerCentralDevice WHERE refID_Customer = @p_CustId AND MACAddr = @pMacA)
        BEGIN

            SELECT @pJsonResult =
            (
                SELECT 'ERROR' AS C_ERROR,
                       'Device Not Registered' AS MSG
                FOR JSON PATH, INCLUDE_NULL_VALUES
            );
            RAISERROR(@pJsonResult, 16, 1);
        END;

    
        UPDATE MachineData
        SET MachineData.OnlineStatus = @pStatus
        FROM tblCustomerMachineData MachineData
        INNER JOIN tblCustomerMachine  Machine ON Machine.ID = MachineData.refID_CustomerMachine
        INNER JOIN tblCustomerCentralDevice CCtrlDevice ON CCtrlDevice.ID = Machine.refID_CentralDevice
        
        WHERE CCtrlDevice.MACAddr = @pMacA AND CCtrlDevice.refID_Customer=@p_CustId
     
   END TRY
    BEGIN CATCH
         PRINT 'catch';
        SELECT @pJsonResult;
    END CATCH;
END;

C#代码 #

public string CentralDeviceStatusUpdate(JObject MachineInfo, Socket client)
{
    string result = null;
    string connectionstatus = null;
    try
    {
        if (!IsConnected(client))
        {
            connectionstatus = "Offline";
        }
        else
        {
            connectionstatus = "Online";
        }

        SqlConnection sqlCon = new SqlConnection(SqlconString);
        sqlCon.Open();

        SqlCommand sql_cmnd = new SqlCommand("SP_UpdateCentralDeviceStatus", sqlCon);
        sql_cmnd.CommandType = CommandType.StoredProcedure;
        sql_cmnd.Parameters.AddWithValue("@pMacA", "C82B9690AEFC");//MachineInfo["MAC"].ToString());
        sql_cmnd.Parameters.AddWithValue("@pCustCode", "230006");// MachineInfo["Code"].ToString());
        sql_cmnd.Parameters.AddWithValue("@pStatus", "Online");//connectionstatus);
        sql_cmnd.Parameters.Add("@pJsonResult", SqlDbType.Char, 500);
        sql_cmnd.Parameters["@pJsonResult"].Direction = ParameterDirection.Output;
        sql_cmnd.ExecuteNonQuery();
        result = (string)sql_cmnd.Parameters["@pJsonResult"].Value;
       
        sqlCon.Close();

        IPHostEntry Entry = Dns.GetHostEntry(((IPEndPoint)client.RemoteEndPoint).Address.ToString());
        string HostName = Entry.HostName.ToString();
        //string HostName = "" ;

        AddToGrid(connectionstatus,clients.IndexOf(clientsocket), HostName, ((IPEndPoint)clientsocket.RemoteEndPoint).Address.ToString(),
            GetMacAddress(((IPEndPoint)clientsocket.RemoteEndPoint).Address.ToString()));
    }
    catch (Exception ex)
    {
        Log(ErrorMsg("Error receiving data: {0} " + ex.Message));
    }
    return result;
}

问题分析与修复 #

核心问题点 #

这个错误是SQL存储过程和C#代码的共同漏洞导致的：

1. SQL存储过程遗漏成功分支赋值：当所有校验通过、UPDATE执行完成后，@pJsonResult从未被赋值，输出参数值为NULL（对应C#中的DBNull）。
2. C#代码硬转换未做判空：直接用(string)强制转换输出参数值，遇到DBNull时触发类型转换异常。

修复步骤 #

1. 完善SQL存储过程的成功分支

在UPDATE语句执行完成后，给输出参数赋值成功状态的JSON：

-- 在UPDATE语句后添加以下代码
SELECT @pJsonResult =
(
    SELECT 'SUCCESS' AS RESULT,
           'Device status updated successfully' AS MSG
    FOR JSON PATH, INCLUDE_NULL_VALUES
);

同时删除CATCH块中的SELECT @pJsonResult（多余的结果集会干扰输出参数传递），修改后CATCH块：

BEGIN CATCH
     PRINT 'catch';
END CATCH;

2. 修复C#代码的类型转换与参数定义

• 先判断输出参数是否为DBNull再转换：

  // 替换原result赋值语句
  result = sql_cmnd.Parameters["@pJsonResult"].Value == DBNull.Value 
           ? null 
           : sql_cmnd.Parameters["@pJsonResult"].Value.ToString();
  

• 修正输出参数的类型定义（对应存储过程的NVARCHAR(MAX)）：

  // 替换原@pJsonResult参数添加语句
  sql_cmnd.Parameters.Add("@pJsonResult", SqlDbType.NVarChar, -1); // -1对应NVARCHAR(MAX)
  

额外优化建议 #

• SQL存储过程中，@p_Cust_Status可能为NULL，直接和'Active'比较会出错，添加ISNULL处理：

  IF ISNULL(@p_Cust_Status, '') <> 'Active'
  

• C#代码使用using自动管理数据库连接，避免资源泄漏：

  using (SqlConnection sqlCon = new SqlConnection(SqlconString))
  {
      sqlCon.Open();
      // 后续命令执行逻辑
  }
  // 无需手动调用sqlCon.Close()
  

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