如何使用SSB与数据库通信

当我们已经将数据从xls,csv,txt等文件中的解析出来，进行必须的数据验证，然后将正确的数据以XML格式保存到磁盘，并将事务型数据更新到DB，比如生成的磁盘文件名，CheckResult,CheckMemo等。

我们再一起回顾下数据库表设计：

USE SSB3  
GO  
 
CREATE TABLE [BatchInventoryQueue]  
(  
    TransactionNumber INT IDENTITY(1,1) NOT NULL,  
    BatchFileID INT NOT NULL,  
    RowIndex INT NOT NULL,  
    ItemNumber INT NOT NULL,  
    [FileName] NVARCHAR(256) NULL,  
    HasCheck CHAR(1) NULL,  
    CheckResult CHAR(1) NULL,  
    CheckMemo NVARCHAR(2000) NULL,  
    HasSendSSB CHAR(1) NULL, --是否尝试发送过SSB  
    SSBSendResult CHAR(1) NULL,--发送SSB是否成功  
    SSBMemo NVARCHAR(2000) NULL,--SSB处理结果  
    CONSTRAINT PK_TransactionNumber_BatchInventoryQueue PRIMARY KEY CLUSTERED  
    (  
        TransactionNumber ASC 
    )  
)

下面我们要将正确的数据以XML格式发送到数据库中。因为我们面临的数据量非常大，所以我们需要考虑负载均衡，比如多台服务器部署。那么就可能会面临数据冲突的问题。我这里的情况是要求多台服务器部署，那么如何给服务器分数据，又不造成冲突呢？

方案一：用表的主键TransactionNumber与服务器数据取模

SELECT TOP(@BatchSize) TransactionNumber,  
    [FileName]  
FROM dbo.BatchInventoryQueue  
WHERE HasCheck='Y' AND CheckResult='S' 
AND [FileName] IS NOT NULL 
AND HasSendSSB IS NULL AND TransactionNumber%@Throtting=@TrottingMod

优点：实现简单。

缺点：如果其中一台服务器失败，那么应该属于它处理的数据将一直得不到处理。

方案二：结合SQL Server的锁特性，在查询数据时避免冲突如下：

UPDATE TOP(@BatchSize) dbo.BatchInventoryQueue  
SET HasSendSSB='I'--inprocessing  
OUTPUT DELETED.TransactionNumber,  
    DELETED.[FileName]  
WHERE HasCheck='Y' AND CheckResult='S' 
AND [FileName] IS NOT NULL 
AND HasSendSSB IS NULL

将HasSendSSB更新为I,标示正在发送。由于在更新数据时，进程会获取UPDLOCK,那么下一个服务器再查询数据时就必须等待直到第一个进程更新完毕。并发执行情况下，难免会有问题，因此需要考虑容错机制。即用另一个Job定时监视(SSBSendResult IS NULL OR SSBSendResult=’N’) AND HasSendSSB=’I’的数据，并将状态清空(SET HasSendSSB=NULL,SSBSendResult=NULL),等待程序下次再次处理。不过这种情况应该非常少。

UPDATE dbo.BatchInventoryQueue  
SET HasSendSSB=NULL,  
SSBSendResult=NULL 
WHERE (SSBSendResult IS NULL OR SSBSendResult='N')  
AND HasSendSSB='I'

接着我们要生成SSBMessage,这里我使用VTemplate模版引擎来生成.代码如下：

SSBMessageBase:

public abstract class SSBMessageBase  
{  
    public string Subject { get; set; }  
 
    public string FromService { get; set; }  
 
    public string ToService { get; set; }  
 
    public SSBMessageHead Head { get; set; }  
}

SSBMessageHead:

public class SSBMessageHead  
{  
    public string Action { get; set; }  
 
    public string TransactionCode { get; set; }  
}

SSBMessageFromFile:

public class SSBMessageFromFile : SSBMessageBase  
{  
    public string FileName { get; set; }  
}

VTemplate:

<vt:template>  
<Publish>  
<Subject>{$:ssb.Subject}/Subject>  
<FromService>{$:ssb.FromService}</FromService>  
<ToService>{$:ssb.ToService}</ToService>  
<Message>  
    <Head>  
        <Action>{$:ssb.Action}</Action>  
        <TransactionCode>{$:ssb.TransactionCode}</TransactionCode>  
    </Head>  
    <Body>  
    <vt:output file="{$:ssb.FileName}" charset="utf-8" />  
    </Body>  
</Message>  
</Publish>  
</vt:template>

SSBUtility:

public class SSBUtility  
{  
    private string VtSSBMessage(SSBMessageBase ssb)  
    {  
        string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,@"Templates\SSB.vt");  
         
        TemplateDocument doc =new TemplateDocument(fileName, Encoding.UTF8);  
        doc.SetValue("ssb", ssb);  
 
        StringBuilder sb = new StringBuilder();  
        StringWriter sw = new StringWriter(sb);  
        doc.Render(sw);  
        sw.Close();  
 
        return sb.ToString();  
    }  
 
    public void SendSSB(SSBMessageBase ssb)  
    {  
        string msg = VtSSBMessage(ssb);  
        using (SqlConnection conn = new SqlConnection(JobConfigs.SSBConnectionString))  
        {  
            using (SqlCommand cmd = new SqlCommand())  
            {  
                cmd.Connection = conn;  
                cmd.CommandType = CommandType.StoredProcedure;  
                cmd.CommandText = JobConfigs.SSBSendProc;//dbo.[UP_Send_Inventory]  
                SqlParameter p = new SqlParameter("@Message", SqlDbType.Xml);  
                p.Value = msg;  
                cmd.Parameters.Add(p);  
                conn.Open();  
                cmd.ExecuteNonQuery();  
                conn.Close();  
            }  
        }  
    }  
}

再接着就是SSB创建部分，下面列举代码示例：

USE [master]  
GO  
 
IF EXISTS(SELECT * FROM sys.databases where [name]='SSB')  
    DROP DATABASE SSB  
 
CREATE DATABASE SSB  
GO  
 
--enable service broker on database  
ALTER DATABASE SSB  
SET TRUSTWORTHY ON 
GO  
USE SSB  
GO  
 
--create a test table  
CREATE TABLE dbo.Inventory  
(  
    [ItemNumber] INT IDENTITY(1,1) PRIMARY KEY,  
    [Inventory] INT NOT NULL 
)  
 
INSERT INTO dbo.Inventory([Inventory])  
VALUES(1)  
 
GO  
--create message type and contract  
CREATE MESSAGE TYPE [Message_Type_Inventory]  
VALIDATION = WELL_FORMED_XML;  
 
CREATE CONTRACT [Contract_Inventory]  
(  
    [Message_Type_Inventory] SENT BY INITIATOR  
)  
GO  
 
--create queue, send service and receive  
CREATE QUEUE [Queue_Inventory]  
WITH STATUS=ON,  
RETENTION=OFF;  
 
CREATE SERVICE [Service_Send_Inventory]  
ON QUEUE [Queue_Inventory]([Contract_Inventory])  
GO  
 
CREATE SERVICE [Service_Receive_Inventory]  
ON QUEUE [Queue_Inventory]([Contract_Inventory])  
GO  
 
--receive procedure  
CREATE PROCEDURE dbo.[UP_Receive_Inventory]  
AS 
BEGIN 
    SET NOCOUNT ON;  
    DECLARE @ConversionHandle UNIQUEIDENTIFIER,  
        @MessageType SYSNAME,  
        @Message XML,  
        @ItemNumber CHAR(50),  
        @Inventory INT 
 
    WHILE(1=1)  
    BEGIN 
        WAITFOR(     
        RECEIVE TOP(1) @ConversionHandle=conversation_handle,  
                @MessageType=message_type_name,  
                @Message=CAST(message_body AS XML)  
            FROM [Queue_Inventory]  
        ),TIMEOUT 1000  
        IF(@@ROWCOUNT=0)  
            BREAK;  
 
        SELECT @[email protected]('(/Publish/Message/Body/Inventory/ItemNumber/text())[1]','INT'),  
            @[email protected]('(/Publish/Message/Body/Inventory/Inventory/text())[1]','INT')  
         
        UPDATE dbo.Inventory  
        SET Inventory=@Inventory  
        WHERE ItemNumber=@ItemNumber  
    END 
END 
GO  
 
--activate queue  
ALTER QUEUE [Queue_Inventory]  
WITH ACTIVATION  
(  
    PROCEDURE_NAME=[UP_Receive_Inventory],  
    MAX_QUEUE_READERS=5,  
    EXECUTE AS OWNER  
)  
GO  
 
--send procedure  
CREATE PROCEDURE dbo.[UP_Send_Inventory]  
(  
    @Message XML  
)  
AS 
BEGIN 
    SET NOCOUNT ON;  
    DECLARE @ConversationHandle UNIQUEIDENTIFIER  
     
    BEGIN DIALOG CONVERSATION @ConversationHandle  
    FROM SERVICE [Service_Send_Inventory]  
    TO SERVICE 'Service_Receive_Inventory' 
    ON CONTRACT [Contract_Inventory]  
    WITH ENCRYPTION = OFF;  
 
    SEND ON CONVERSATION @ConversationHandle  
    MESSAGE TYPE [Message_Type_Inventory](@Message)  
     
    END CONVERSATION @ConversationHandle WITH CLEANUP;  
END 
GO  
 
--test data  
DECLARE @Message XML  
SELECT @Message='  
<Publish>  
<Subject>BatchInventory</Subject>  
<FromService>Service_Send_Inventory</FromService>  
<ToService>Service_Receive_Inventory</ToService>  
<Message>  
    <Head>  
        <Action>UpdateInventory</Action>  
        <TransactionCode>123</TransactionCode>  
    </Head>  
    <Body>  
        <Inventory>  
            <ItemNumber>1</ItemNumber>  
            <Inventory>200</Inventory>  
        </Inventory>  
    </Body>  
</Message>  
</Publish>'  
 
SELECT * FROM dbo.[Inventory]  
--send  
EXEC dbo.[UP_Send_Inventory] @Message  
 
WAITFOR DELAY '00:00:30' 
SELECT * FROM Inventory