c# - 从 SQL 存储过程 c# 加载输出参数

Question

我正在尝试从我的 SQL 存储过程加载输出参数。

USE [EdiMon_Beta]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetClients] 
-- Add the parameters for the stored procedure here
@sender nvarchar(max),
@subSender nvarchar(max),
@receiver nvarchar(max),
@subReceiver nvarchar(max),
@msgTypeID int,

@ErrorMsg nvarchar(max) = null OUTPUT,
@processId int = 0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
@senderId int,
@subSenderId int,
@receiverId int,
@subReceiverId int



SELECT
    @senderId = ID
FROM [dbo].[Party_Client]
WHERE
    [Number] LIKE @sender
IF(@senderId IS NULL)
BEGIN
    SET @ErrorMsg = 'Sender does not exist'
    RETURN ;
END


SELECT
    @subSenderId = ID
FROM [dbo].[Party_Client]
WHERE
    [Number] LIKE @subSender
    AND ParentId = @senderId
    AND IsSubClient = 1
IF(@subSenderId IS NULL)
BEGIN
    SET @ErrorMsg = 'SubSender does not exist'
    RETURN ;
END


SELECT
    @receiver = ID
FROM [dbo].[Party_Client]
WHERE
    [Number] LIKE @receiver
IF(@receiverId IS NULL)
BEGIN
    SET @ErrorMsg = 'Receiver does not exist'
    RETURN;
END


SELECT
    @subReceiverId = ID
FROM [dbo].[Party_Client]
WHERE
    [Number] LIKE @subReceiver
    AND ParentId = @receiverId
    AND IsSubClient = 1
IF(@subReceiverId IS NULL)
BEGIN
    SET @ErrorMsg = 'SubReceiver does not exist'
    RETURN ;
END


SELECT @processId = ID FROM [dbo].[Party_Processes]
WHERE MsgTypeId = @msgTypeID
AND SenderId = @senderId
AND ReceiverId = @receiverId

END

并使用 C# 代码读取它:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        SqlCommand command = new SqlCommand("GetClients", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@sender", sender));
        command.Parameters.Add(new SqlParameter("@subSender", subSender));
        command.Parameters.Add(new SqlParameter("@receiver", receiver));
        command.Parameters.Add(new SqlParameter("@subReceiver", subReceiver));
        command.Parameters.Add(new SqlParameter("@msgTypeID", msgTypeID));

        var errorMsgParam = command.Parameters.Add(new SqlParameter("@ErrorMsg", SqlDbType.NVarChar, -1));
        errorMsgParam.Direction = ParameterDirection.Output;

        var processIdParam = command.Parameters.Add(new SqlParameter("@processId", SqlDbType.Int, -1));
        processIdParam.Direction = ParameterDirection.Output;

        connection.Open();
        command.ExecuteNonQuery();

        var processIDResult = command.Parameters["@processId"].Value;
        var errorMsgResult = command.Parameters["@ErrorMsg"].Value;

        processID = (int)processIDResult;
        errorMsg = errorMsgResult.ToString();
    }
}

问题是，结果我总是得到 null。这是 biztalk 中 xsl 映射的帮助程序类。所有的输入参数都直接来自 map 。我想检查我们的数据库，是否存在这些客户端，以及是否存在使用它们的进程。

感谢您的帮助:)

Answer 1

好的，我解决了这个问题。问题出在 2 或 3 个地方。首先，我在我的 SQL 存储过程中犯了一个不通用的错误，所以我没有在此处发布它(它仅适用于我的项目)。另一个错误是从存储过程转换我的输出:

processID = command.Parameters["@processId"].Value as int?;
errorMsg = command.Parameters["@ErrorMsg"].Value == DBNull.Value ? string.Empty : command.Parameters["@ErrorMsg"].Value.ToString();

processID 在哪里声明为“int”？并且 errorMsg 声明为“字符串”。

感谢所有的帮助。