如何使用SSB與數據庫通信

作者：James.H.Fu 2011-04-18 16:03:28

要將正確的數據以XML格式發送到數據庫中。需要考慮負載均衡，比如多臺服務器部署。那么就可能會面臨數據沖突的問題。本文給出幾種解決方案。

當我們已經將數據從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 @ItemNumber=@Message.value('(/Publish/Message/Body/Inventory/ItemNumber/text())[1]','INT'),  
            @Inventory=@Message.value('(/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

原文鏈接：http://www.cnblogs.com/fuhongwei041/archive/2011/04/18/ssb.html

【編輯推薦】

如何通過注入SQL語句盜取網站管理權限
SQL Server 2008中的代碼安全
SQL Server 2005數據庫SA的相關安全性設置
SQL Server與Oracle數據庫在安全性上的異同
Sql server安全設置九大措施

責任編輯：艾婧來源：博客園

SSB 數據庫