How to call Stored Procedure that consists of Service Broker

c# entity-framework-6 service-broker sql-server stored-procedures

Question

I don't know if this question duplicate. Please give me the link of it if this question is duplicated.

My question is how to call 2 stored procedures that consists of BEGIN TRANSACTION & COMMIT TRANSACTION (Service Broker).

I have 2 stored procedures that use to do some action of Service Broker.

This is the stored procedure that contains BEGIN CONVERSATION:

USE [EventCloud]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SendingMessage_Group_Id]
    @reference_id UNIQUEIDENTIFIER
AS
BEGIN
    DECLARE @ch UNIQUEIDENTIFIER
    DECLARE @conversation_group_id UNIQUEIDENTIFIER
    DECLARE @msg NVARCHAR(MAX)

    SET @conversation_group_id = @reference_id

    BEGIN TRY
        BEGIN TRANSACTION

            BEGIN DIALOG CONVERSATION @ch
                FROM SERVICE [InitiatorService]
                TO SERVICE 'TargetService'
                ON CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
                WITH RELATED_CONVERSATION_GROUP = @conversation_group_id,
                    ENCRYPTION = OFF

            SET @msg = '<HelloWorldRequest>1234</HelloWorldRequest>'

            ;SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
            (
                @msg
            )

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
    END CATCH
END

Code below is an internal activation stored procedure of TargetQueue:

USE [EventCloud]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ProcessRequestMessage]
AS
BEGIN
    DECLARE @ch UNIQUEIDENTIFIER
    DECLARE @messagetypename NVARCHAR(256)
    DECLARE @messagebody XML
    DECLARE @responsemessage XML;
    DECLARE @errorcode INT
    DECLARE @errormessage NVARCHAR(3000);

    WHILE (1 = 1)
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
                WAITFOR(
                    RECEIVE TOP (1)
                        @ch = conversation_handle,
                        @messagetypename = message_type_name,
                        @messagebody = CAST(message_body AS XML)
                    FROM TargetQueue
                )

                IF (@@ROWCOUNT = 0)
                BEGIN
                    ROLLBACK TRANSACTION
                    BREAK
                END

                -- Process the requested message and send back to Initiator
                ELSE IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
                BEGIN
                    -- Store the received request message in a table
                    INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName, ProcessedDateTime)
                    VALUES (NEWID(), @messagebody, 'TargetService', GETDATE())

                    -- Construct the response message
                    SET @responsemessage =
                        '<HelloWorldResponse>' +
                            @messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') +
                        '</HelloWorldResponse>';

                    -- Send the response message back to the initiating service
                    SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
                    (
                        @responsemessage
                    );

                    -- END the conversation on the target's side
                    END CONVERSATION @ch;
                END

                -- End the conversation if meet the message type
                IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
                BEGIN
                    -- End the conversation
                    END CONVERSATION @ch;
                END

            COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION
            PRINT ERROR_MESSAGE()
        END CATCH
    END
END

And this code is used to receive the response message from InitiatorQueue:

ALTER PROCEDURE [dbo].[ProcessMessageWithTimeOut]
    @reference_id UNIQUEIDENTIFIER,
    @receive_timeout INT
AS
BEGIN
    DECLARE @ch UNIQUEIDENTIFIER
    DECLARE @conversation_group_id UNIQUEIDENTIFIER
    DECLARE @messagetypename NVARCHAR(256)
    DECLARE @messagebody XML
    DECLARE @responsemessage XML
    DECLARE @errorcode INT
    DECLARE @errormessage NVARCHAR(3000)
    DECLARE @queuing_order BIGINT
    DECLARE @timeout INT

    SET @conversation_group_id = @reference_id
    SET @timeout = @receive_timeout

    DECLARE @tableMessage TABLE
    (
        queuing_order BIGINT,
        conversation_handle UNIQUEIDENTIFIER,
        message_type_name NVARCHAR(256),
        message_body VARBINARY(MAX)
    )

    BEGIN TRY
        BEGIN TRANSACTION
            WAITFOR(
                RECEIVE
                    queuing_order,
                    conversation_handle,
                    message_type_name,
                    message_body
                FROM InitiatorQueue INTO @tableMessage
                WHERE conversation_group_id = @conversation_group_id
            ), TIMEOUT @timeout;

            DECLARE @count INT
            SET @count = (SELECT COUNT(*) FROM @tableMessage)
            IF (@count = 0)
            BEGIN;
                THROW 50001, 'No message response within 5 seconds.', 1
            END

            IF (@count <>2)
            BEGIN
                DECLARE @timeout2 INT
                SET @timeout2 = ABS(@timeout * 0.5)

                WAITFOR(
                    RECEIVE
                        queuing_order,
                        conversation_handle,
                        message_type_name,
                        message_body
                    FROM InitiatorQueue INTO @tableMessage
                    WHERE conversation_group_id = @conversation_group_id
                ), TIMEOUT 5000
                SET @count = (SELECT COUNT(*) FROM @tableMessage)
                IF (@count <> 2)
                BEGIN;
                    THROW 50002, 'End Dialog without Response Message', 1
                END
            END

            WHILE (@count <> 0)
            BEGIN
            SET @queuing_order = (SELECT TOP 1 queuing_order FROM @tableMessage)
            SET @ch = (SELECT conversation_handle FROM @tableMessage WHERE queuing_order = @queuing_order)
            SET @messagetypename = (SELECT message_type_name FROM @tableMessage WHERE queuing_order = @queuing_order)
            SET @messagebody = CAST((SELECT message_body FROM @tableMessage WHERE queuing_order = @queuing_order) AS XML)

                IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
                BEGIN
                    -- Store the received response message in a table
                    INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName, ProcessedDateTime)
                    VALUES (NEWID(), @messagebody, 'InitiatorService', GETDATE())
                END

                ELSE IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
                BEGIN
                    -- End the conversation on the initiator's side
                    END CONVERSATION @ch
                END
                DELETE FROM @tableMessage WHERE queuing_order = @queuing_order
                SET @count = (SELECT COUNT(*) FROM @tableMessage)
            END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 50001
        BEGIN
            ;THROW
        END

        IF ERROR_NUMBER() = 50002
        BEGIN
            ;THROW
        END
        ;THROW
        ROLLBACK TRANSACTION
        PRINT ERROR_MESSAGE()
    END CATCH
END

This is my C# code that is used to call the stored procedure:

public async Task<TestObject> Begin_Conversation_With_Group_Id(Guid ch)
{
    try
    {
        return await Context.Database.SqlQuery<TestObject>(
        "EXEC SendingMessage_Group_Id @ch",
        new SqlParameter("ch", ch))
            .SingleOrDefaultAsync();
    }
    catch (Exception e)
    {
        TestObject exception = new TestObject();
        exception.Data = "Cannot get the data due to: " + " " + e.Message;
        return exception;
    }
}

public async Task<TestObject> Process_Response_Message_With_TimeOut(Guid ch)
{
    var timeout = 5000;
    try
    {
        return await Context.Database.SqlQuery<TestObject>(
            "EXEC ProcessMessageWithTimeOut @ch, @timeout",
            new SqlParameter("ch", ch),
            new SqlParameter("timeout", timeout))
                .SingleOrDefaultAsync();
    }
    catch (SqlException ex)
    {
        TestObject exception = new TestObject();
        exception.Data = "Process is not finish yet due to: " + ex.Message + " " + ex.Number;
        return exception;
    }
}

The problem is when I run the code, the message is still in TargetQueue, by right it should be received and processed by the internal activation.

But it's working fine if only call 1 stored procedure (SendingMessage_Group_Id). The message is able to response back to InitiatorQueue instead of staying in TargetQueue.

From my guessing, is because of the transaction is not commit yet.

1
1
10/17/2017 8:56:03 AM

Accepted Answer

The issue has been solved.

The reason of this issue happened is because I use a framework that cause this situation happened. The framework is included a transaction at starting, so what I need to do is disable the transaction of the framework then the issue is solved.

The framework that I'm using is ASP.NET Zero

0
10/19/2017 7:59:17 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow