如何调用由服务代理组成的存储过程
问题描述:
我不知道这个问题是否重复。如果这个问题重复,请给我链接。如何调用由服务代理组成的存储过程
我的问题是如何调用由BEGIN TRANSACTION & COMMIT TRANSACTION
(Service Broker)组成的2个存储过程。
我有2个存储过程用于执行Service Broker的某些操作。
这是包含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
代码是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
而这个代码,用于接收从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
这是我的C是,用于调用存储过程#代码:
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;
}
}
问题是,当我运行的代码,该消息还处于TargetQueue
,由右应该由内部激活来接收和处理。
但它工作正常,如果只调用1个存储过程(SendingMessage_Group_Id
)。该消息能够回应到InitiatorQueue
而不是停留在TargetQueue
。
从我的猜测,是因为交易还没有提交。
C#与该问题无关。尝试通过手动执行这些过程使其在没有任何C#代码的情况下工作。 –
它没有C#,这意味着当我在MSSQL上执行它工作正常 –
当你通过SSMS或C#执行代码应该没有什么区别。如果你有差异,那么某些东西不会以相同的方式执行。尝试使用SQL事件探查器来检查使用C#代码时执行的查询,并查看差异 –