如何使用SSB与数据库通信

数据库
要将正确的数据以XML格式发送到数据库中。需要考虑负载均衡,比如多台服务器部署。那么就可能会面临数据冲突的问题。本文给出几种解决方案。

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

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

  1. USE SSB3  
  2. GO  
  3.  
  4. CREATE TABLE [BatchInventoryQueue]  
  5. (  
  6.     TransactionNumber INT IDENTITY(1,1) NOT NULL,  
  7.     BatchFileID INT NOT NULL,  
  8.     RowIndex INT NOT NULL,  
  9.     ItemNumber INT NOT NULL,  
  10.     [FileName] NVARCHAR(256) NULL,  
  11.     HasCheck CHAR(1) NULL,  
  12.     CheckResult CHAR(1) NULL,  
  13.     CheckMemo NVARCHAR(2000) NULL,  
  14.     HasSendSSB CHAR(1) NULL--是否尝试发送过SSB  
  15.     SSBSendResult CHAR(1) NULL,--发送SSB是否成功  
  16.     SSBMemo NVARCHAR(2000) NULL,--SSB处理结果  
  17.     CONSTRAINT PK_TransactionNumber_BatchInventoryQueue PRIMARY KEY CLUSTERED  
  18.     (  
  19.         TransactionNumber ASC 
  20.     )  

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

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

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

优点:实现简单。

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

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

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

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

  1. UPDATE dbo.BatchInventoryQueue  
  2. SET HasSendSSB=NULL,  
  3. SSBSendResult=NULL 
  4. WHERE (SSBSendResult IS NULL OR SSBSendResult='N')  
  5. AND HasSendSSB='I' 

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

SSBMessageBase:

  1. public abstract class SSBMessageBase  
  2. {  
  3.     public string Subject { get; set; }  
  4.  
  5.     public string FromService { get; set; }  
  6.  
  7.     public string ToService { get; set; }  
  8.  
  9.     public SSBMessageHead Head { get; set; }  
  10. }  

SSBMessageHead:

  1. public class SSBMessageHead  
  2. {  
  3.     public string Action { get; set; }  
  4.  
  5.     public string TransactionCode { get; set; }  
  6. }  

SSBMessageFromFile:

  1. public class SSBMessageFromFile : SSBMessageBase  
  2. {  
  3.     public string FileName { get; set; }  
  4. }  

VTemplate:

  1. <vt:template>  
  2. <Publish>  
  3. <Subject>{$:ssb.Subject}/Subject>  
  4. <FromService>{$:ssb.FromService}</FromService>  
  5. <ToService>{$:ssb.ToService}</ToService>  
  6. <Message>  
  7.     <Head>  
  8.         <Action>{$:ssb.Action}</Action>  
  9.         <TransactionCode>{$:ssb.TransactionCode}</TransactionCode>  
  10.     </Head>  
  11.     <Body>  
  12.     <vt:output file="{$:ssb.FileName}" charset="utf-8" />  
  13.     </Body>  
  14. </Message>  
  15. </Publish>  
  16. </vt:template>  

SSBUtility:

  1. public class SSBUtility  
  2. {  
  3.     private string VtSSBMessage(SSBMessageBase ssb)  
  4.     {  
  5.         string fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,@"Templates\SSB.vt");  
  6.          
  7.         TemplateDocument doc =new TemplateDocument(fileName, Encoding.UTF8);  
  8.         doc.SetValue("ssb", ssb);  
  9.  
  10.         StringBuilder sb = new StringBuilder();  
  11.         StringWriter sw = new StringWriter(sb);  
  12.         doc.Render(sw);  
  13.         sw.Close();  
  14.  
  15.         return sb.ToString();  
  16.     }  
  17.  
  18.     public void SendSSB(SSBMessageBase ssb)  
  19.     {  
  20.         string msg = VtSSBMessage(ssb);  
  21.         using (SqlConnection conn = new SqlConnection(JobConfigs.SSBConnectionString))  
  22.         {  
  23.             using (SqlCommand cmd = new SqlCommand())  
  24.             {  
  25.                 cmd.Connection = conn;  
  26.                 cmd.CommandType = CommandType.StoredProcedure;  
  27.                 cmd.CommandText = JobConfigs.SSBSendProc;//dbo.[UP_Send_Inventory]  
  28.                 SqlParameter p = new SqlParameter("@Message", SqlDbType.Xml);  
  29.                 p.Value = msg;  
  30.                 cmd.Parameters.Add(p);  
  31.                 conn.Open();  
  32.                 cmd.ExecuteNonQuery();  
  33.                 conn.Close();  
  34.             }  
  35.         }  
  36.     }  
  37. }  

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

  1. USE [master]  
  2. GO  
  3.  
  4. IF EXISTS(SELECT * FROM sys.databases where [name]='SSB')  
  5.     DROP DATABASE SSB  
  6.  
  7. CREATE DATABASE SSB  
  8. GO  
  9.  
  10. --enable service broker on database  
  11. ALTER DATABASE SSB  
  12. SET TRUSTWORTHY ON 
  13. GO  
  14. USE SSB  
  15. GO  
  16.  
  17. --create a test table  
  18. CREATE TABLE dbo.Inventory  
  19. (  
  20.     [ItemNumber] INT IDENTITY(1,1) PRIMARY KEY,  
  21.     [Inventory] INT NOT NULL 
  22. )  
  23.  
  24. INSERT INTO dbo.Inventory([Inventory])  
  25. VALUES(1)  
  26.  
  27. GO  
  28. --create message type and contract  
  29. CREATE MESSAGE TYPE [Message_Type_Inventory]  
  30. VALIDATION = WELL_FORMED_XML;  
  31.  
  32. CREATE CONTRACT [Contract_Inventory]  
  33. (  
  34.     [Message_Type_Inventory] SENT BY INITIATOR  
  35. )  
  36. GO  
  37.  
  38. --create queue, send service and receive  
  39. CREATE QUEUE [Queue_Inventory]  
  40. WITH STATUS=ON,  
  41. RETENTION=OFF;  
  42.  
  43. CREATE SERVICE [Service_Send_Inventory]  
  44. ON QUEUE [Queue_Inventory]([Contract_Inventory])  
  45. GO  
  46.  
  47. CREATE SERVICE [Service_Receive_Inventory]  
  48. ON QUEUE [Queue_Inventory]([Contract_Inventory])  
  49. GO  
  50.  
  51. --receive procedure  
  52. CREATE PROCEDURE dbo.[UP_Receive_Inventory]  
  53. AS 
  54. BEGIN 
  55.     SET NOCOUNT ON;  
  56.     DECLARE @ConversionHandle UNIQUEIDENTIFIER,  
  57.         @MessageType SYSNAME,  
  58.         @Message XML,  
  59.         @ItemNumber CHAR(50),  
  60.         @Inventory INT 
  61.  
  62.     WHILE(1=1)  
  63.     BEGIN 
  64.         WAITFOR(     
  65.         RECEIVE TOP(1) @ConversionHandle=conversation_handle,  
  66.                 @MessageType=message_type_name,  
  67.                 @Message=CAST(message_body AS XML)  
  68.             FROM [Queue_Inventory]  
  69.         ),TIMEOUT 1000  
  70.         IF(@@ROWCOUNT=0)  
  71.             BREAK;  
  72.  
  73.         SELECT @ItemNumber=@Message.value('(/Publish/Message/Body/Inventory/ItemNumber/text())[1]','INT'),  
  74.             @Inventory=@Message.value('(/Publish/Message/Body/Inventory/Inventory/text())[1]','INT')  
  75.          
  76.         UPDATE dbo.Inventory  
  77.         SET Inventory=@Inventory  
  78.         WHERE ItemNumber=@ItemNumber  
  79.     END 
  80. END 
  81. GO  
  82.  
  83. --activate queue  
  84. ALTER QUEUE [Queue_Inventory]  
  85. WITH ACTIVATION  
  86. (  
  87.     PROCEDURE_NAME=[UP_Receive_Inventory],  
  88.     MAX_QUEUE_READERS=5,  
  89.     EXECUTE AS OWNER  
  90. )  
  91. GO  
  92.  
  93. --send procedure  
  94. CREATE PROCEDURE dbo.[UP_Send_Inventory]  
  95. (  
  96.     @Message XML  
  97. )  
  98. AS 
  99. BEGIN 
  100.     SET NOCOUNT ON;  
  101.     DECLARE @ConversationHandle UNIQUEIDENTIFIER  
  102.      
  103.     BEGIN DIALOG CONVERSATION @ConversationHandle  
  104.     FROM SERVICE [Service_Send_Inventory]  
  105.     TO SERVICE 'Service_Receive_Inventory' 
  106.     ON CONTRACT [Contract_Inventory]  
  107.     WITH ENCRYPTION = OFF;  
  108.  
  109.     SEND ON CONVERSATION @ConversationHandle  
  110.     MESSAGE TYPE [Message_Type_Inventory](@Message)  
  111.      
  112.     END CONVERSATION @ConversationHandle WITH CLEANUP;  
  113. END 
  114. GO  
  115.  
  116. --test data  
  117. DECLARE @Message XML  
  118. SELECT @Message='  
  119. <Publish>  
  120. <Subject>BatchInventory</Subject>  
  121. <FromService>Service_Send_Inventory</FromService>  
  122. <ToService>Service_Receive_Inventory</ToService>  
  123. <Message>  
  124.     <Head>  
  125.         <Action>UpdateInventory</Action>  
  126.         <TransactionCode>123</TransactionCode>  
  127.     </Head>  
  128.     <Body>  
  129.         <Inventory>  
  130.             <ItemNumber>1</ItemNumber>  
  131.             <Inventory>200</Inventory>  
  132.         </Inventory>  
  133.     </Body>  
  134. </Message>  
  135. </Publish>'  
  136.  
  137. SELECT * FROM dbo.[Inventory]  
  138. --send  
  139. EXEC dbo.[UP_Send_Inventory] @Message  
  140.  
  141. WAITFOR DELAY '00:00:30' 
  142. SELECT * FROM Inventory  

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

【编辑推荐】

  1. 如何通过注入SQL语句盗取网站管理权限
  2. SQL Server 2008中的代码安全
  3. SQL Server 2005数据库SA的相关安全性设置
  4. SQL Server与Oracle数据库在安全性上的异同
  5. Sql server安全设置九大措施

 

责任编辑:艾婧 来源: 博客园
相关推荐

2011-09-01 15:10:22

Qt数据库SQL

2010-05-05 14:44:50

Oracle数据库

2011-08-10 15:46:29

数据库

2021-08-02 10:53:28

PythonMySQL数据库

2010-06-09 10:09:39

MySQL 数据库导入

2022-11-14 18:23:06

亚马逊

2009-12-25 17:28:01

ADO Data 控件

2012-05-31 15:56:23

Hadoop大数据

2011-04-08 09:42:19

Access数据库压缩文件

2009-01-19 09:14:31

.NETMySQLMySql驱动包

2023-10-10 11:18:42

Spring数据库

2009-12-03 16:45:59

PHP保存sessio

2011-03-04 13:07:06

SQL Server数

2011-08-05 09:15:27

MySQL数据库索引

2022-04-18 09:00:00

数据库向量机器学习

2011-07-28 17:02:59

MYSQL数据库跨表更新数据并合

2011-07-28 14:31:47

SQL Server数存储过程

2010-07-15 17:28:50

SQL Server

2010-05-26 09:44:41

MySQL数据库使用

2019-08-15 07:00:54

SQLite数据库内存数据库
点赞
收藏

51CTO技术栈公众号