如何：為合併發行項實作商務邏輯處理常式 (複寫程式設計)
Microsoft.SqlServer.Replication.BusinessLogicSupport 命名空間會實作一個介面，此介面可讓您撰寫複雜商務邏輯來處理合併複寫同步處理程序期間所發生的事件。在同步處理期間複寫之每一個變更資料列的複寫程序可以叫用商務邏輯處理常式中的方法。
實作商務邏輯處理常式的一般程序如下：
建立商務邏輯處理常式組件。
在散發者上註冊此組件。
在合併代理程式執行所在的伺服器上部署此組件。若為提取訂閱，代理程式會在訂閱者端執行；若為發送訂閱，代理程式會在散發者端執行。當您正在使用 Web 同步處理時，此代理程式會在 Web 伺服器上執行。
建立使用商務邏輯處理常式的發行項，或是修改現有的發行項來使用商務邏輯處理常式。
指定的商務邏輯處理常式會針對要同步處理的每一個資料列執行。對其他應用程式或網路服務的複雜邏輯與呼叫可能會影響效能。如需有關商務邏輯處理常式的詳細資訊，請參閱＜在合併同步處理期間執行商務邏輯＞。
建立及部署商務邏輯處理常式
在 Microsoft Visual Studio 中，請針對包含可實作商務邏輯處理常式之程式碼的 .NET 組件來建立新的專案。
針對下列命名空間加入此專案的參考。
組件參考
位置
C:\Program Files\Microsoft SQL Server\100\COM (預設安裝)
GAC (.NET Framework 的元件)
GAC (.NET Framework 的元件)
加入會覆寫 BusinessLogicModule 類別的類別。
實作 HandledChangeStates 屬性來指示要處理的變更類型。
覆寫 BusinessLogicModule 類別的下列其中一個或多個方法：
CommitHandler - 在同步處理期間認可資料變更時叫用。
DeleteErrorHandler - 在上載或下載 DELETE 陳述式期間發生錯誤時叫用。
DeleteHandler - 在上載或下載 DELETE 陳述式時叫用。
InsertErrorHandler - 在上載或下載 INSERT 陳述式期間發生錯誤時叫用。
InsertHandler - 在上載或下載 INSERT 陳述式時叫用。
UpdateConflictsHandler - 當發行者和訂閱者上發生衝突的 UPDATE 陳述式時叫用。
UpdateDeleteConflictHandler - 當發行者和訂閱者上發生與 DELETE 陳述式衝突的 UPDATE 陳述式時叫用。
UpdateErrorHandler - 在上載或下載 UPDATE 陳述式期間發生錯誤時叫用。
UpdateHandler - 在上載或下載 UPDATE 陳述式時叫用。
建立專案來建立商務邏輯處理常式組件。
將此組件部署在包含合併代理程式可執行檔 (replmerg.exe) 的目錄中 (它的預設安裝位置為 C:\Program Files\Microsoft SQL Server\100\COM)，或是將它安裝在 .NET 全域組件快取 (GAC) 中。只有當合併代理程式以外的應用程式需要存取此組件時，您才應該將此組件安裝在 GAC 中。您可使用 .NET Framework SDK 中提供的全域組件快取工具 (Gacutil.exe)，將此組件安裝在 GAC 中。
[!附註]
您必須在合併代理程式執行所在的每一部伺服器上部署商務邏輯處理常式，其中包括使用 Web 同步處理時，主控 replisapi.dll 的 IIS 伺服器。
註冊商務邏輯處理常式
在發行者上，執行 sp_enumcustomresolvers (Transact-SQL)，以確定此組件尚未註冊為商務邏輯處理常式。
在散發者上，執行 sp_registercustomresolver (Transact-SQL)，針對 @article_resolver 指定商務邏輯處理常式的易記名稱、針對 @is_dotnet_assembly 指定 true 的值、針對 @dotnet_assembly_name 指定組件的名稱，並將 @dotnet_class_name 指定為覆寫 BusinessLogicModule 之類別的完整名稱。
[!附註]
如果此組件未部署在與合併代理程式可執行檔相同的目錄中、與同步啟動合併代理程式之應用程式相同的目錄中，或是全域組件快取 (GAC) 中，您就必須將 @dotnet_assembly_name 指定為包含組件名稱的完整路徑。當您正在使用 Web 同步處理時，必須指定組件在 Web 伺服器上的位置。
搭配新的資料表發行項使用商務邏輯處理常式
- 執行 sp_addmergearticle (Transact-SQL) 來定義發行項，針對 @article_resolver 指定商務邏輯處理常式的易記名稱。如需詳細資訊，請參閱＜如何：定義發行項 (複寫 Transact-SQL 程式設計)＞。
搭配現有的資料表發行項使用商務邏輯處理常式
- 執行 sp_changemergearticle (Transact-SQL)，指定 @publication、@article，並針對 @property 指定 article_resolver 值及針對 @value 指定商務邏輯處理常式的易記名稱。
範例
這個範例會示範建立稽核記錄的商務邏輯處理常式。
using System;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.SqlServer.Replication.BusinessLogicSupport;
using Microsoft.Samples.SqlServer.BusinessLogicHandler;
namespace Microsoft.Samples.SqlServer.BusinessLogicHandler
{
public class OrderEntryBusinessLogicHandler :
Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule
{
// Variables to hold server names.
private string publisherName;
private string subscriberName;
public OrderEntryBusinessLogicHandler()
{
}
// Implement the Initialize method to get publication
// and subscription information.
public override void Initialize(
string publisher,
string subscriber,
string distributor,
string publisherDB,
string subscriberDB,
string articleName)
{
// Set the Publisher and Subscriber names.
publisherName = publisher;
subscriberName = subscriber;
}
// Declare what types of row changes, conflicts, or errors to handle.
override public ChangeStates HandledChangeStates
{
get
{
// Handle Subscriber inserts, updates and deletes.
return ChangeStates.SubscriberInserts |
ChangeStates.SubscriberUpdates | ChangeStates.SubscriberDeletes;
}
}
public override ActionOnDataChange InsertHandler(SourceIdentifier insertSource,
DataSet insertedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
ref string historyLogMessage)
{
if (insertSource == SourceIdentifier.SourceIsSubscriber)
{
// Build a line item in the audit message to log the Subscriber insert.
StringBuilder AuditMessage = new StringBuilder();
AuditMessage.Append(String.Format("A new order was entered at {0}. " +
"The SalesOrderID for the order is :", subscriberName));
AuditMessage.Append(insertedDataSet.Tables[0].Rows[0]["SalesOrderID"].ToString());
AuditMessage.Append("The order must be shipped by :");
AuditMessage.Append(insertedDataSet.Tables[0].Rows[0]["DueDate"].ToString());
// Set the reference parameter to write the line to the log file.
historyLogMessage = AuditMessage.ToString();
// Set the history log level to the default verbose level.
historyLogLevel = 1;
// Accept the inserted data in the Subscriber's data set and
// apply it to the Publisher.
return ActionOnDataChange.AcceptData;
}
else
{
return base.InsertHandler(insertSource, insertedDataSet, ref customDataSet,
ref historyLogLevel, ref historyLogMessage);
}
}
public override ActionOnDataChange UpdateHandler(SourceIdentifier updateSource,
DataSet updatedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
ref string historyLogMessage)
{
if (updateSource == SourceIdentifier.SourceIsPublisher)
{
// Build a line item in the audit message to log the Subscriber update.
StringBuilder AuditMessage = new StringBuilder();
AuditMessage.Append(String.Format("An existing order was updated at {0}. " +
"The SalesOrderID for the order is ", subscriberName));
AuditMessage.Append(updatedDataSet.Tables[0].Rows[0]["SalesOrderID"].ToString());
AuditMessage.Append("The order must now be shipped by :");
AuditMessage.Append(updatedDataSet.Tables[0].Rows[0]["DueDate"].ToString());
// Set the reference parameter to write the line to the log file.
historyLogMessage = AuditMessage.ToString();
// Set the history log level to the default verbose level.
historyLogLevel = 1;
// Accept the updated data in the Subscriber's data set and apply it to the Publisher.
return ActionOnDataChange.AcceptData;
}
else
{
return base.UpdateHandler(updateSource, updatedDataSet,
ref customDataSet, ref historyLogLevel, ref historyLogMessage);
}
}
public override ActionOnDataDelete DeleteHandler(SourceIdentifier deleteSource,
DataSet deletedDataSet, ref int historyLogLevel, ref string historyLogMessage)
{
if (deleteSource == SourceIdentifier.SourceIsSubscriber)
{
// Build a line item in the audit message to log the Subscriber deletes.
// Note that the rowguid is the only information that is
// available in the dataset.
StringBuilder AuditMessage = new StringBuilder();
AuditMessage.Append(String.Format("An existing order was deleted at {0}. " +
"The rowguid for the order is ", subscriberName));
AuditMessage.Append(deletedDataSet.Tables[0].Rows[0]["rowguid"].ToString());
// Set the reference parameter to write the line to the log file.
historyLogMessage = AuditMessage.ToString();
// Set the history log level to the default verbose level.
historyLogLevel = 1;
// Accept the delete and apply it to the Publisher.
return ActionOnDataDelete.AcceptDelete;
}
else
{
return base.DeleteHandler(deleteSource, deletedDataSet,
ref historyLogLevel, ref historyLogMessage);
}
}
}
}
Imports System
Imports System.Text
Imports System.Data
Imports System.Data.Common
Imports Microsoft.SqlServer.Replication.BusinessLogicSupport
Namespace Microsoft.Samples.SqlServer.BusinessLogicHandler
Public Class OrderEntryBusinessLogicHandler
Inherits BusinessLogicModule
' Variables to hold server names.
Private publisherName As String
Private subscriberName As String
' Implement the Initialize method to get publication
' and subscription information.
Public Overrides Sub Initialize( _
ByVal publisher As String, _
ByVal subscriber As String, _
ByVal distributor As String, _
ByVal publisherDB As String, _
ByVal subscriberDB As String, _
ByVal articleName As String _
)
' Set the Publisher and Subscriber names.
publisherName = publisher
subscriberName = subscriber
End Sub
' Declare what types of row changes, conflicts, or errors to handle.
Public Overrides ReadOnly Property HandledChangeStates() As ChangeStates
Get
' Handle Subscriber inserts, updates and deletes.
Return (ChangeStates.SubscriberInserts Or _
ChangeStates.SubscriberUpdates Or ChangeStates.SubscriberDeletes)
End Get
End Property
Public Overrides Function InsertHandler(ByVal insertSource As SourceIdentifier, _
ByVal insertedDataSet As DataSet, ByRef customDataSet As DataSet, _
ByRef historyLogLevel As Integer, ByRef historyLogMessage As String) _
As ActionOnDataChange
If insertSource = SourceIdentifier.SourceIsSubscriber Then
' Build a line item in the audit message to log the Subscriber insert.
Dim AuditMessage As StringBuilder = New StringBuilder()
AuditMessage.Append(String.Format("A new order was entered at {0}. " + _
"The SalesOrderID for the order is :", subscriberName))
AuditMessage.Append(insertedDataSet.Tables(0).Rows(0)("SalesOrderID").ToString())
AuditMessage.Append("The order must be shipped by :")
AuditMessage.Append(insertedDataSet.Tables(0).Rows(0)("DueDate").ToString())
' Set the reference parameter to write the line to the log file.
historyLogMessage = AuditMessage.ToString()
' Set the history log level to the default verbose level.
historyLogLevel = 1
' Accept the inserted data in the Subscriber's data set and
' apply it to the Publisher.
Return ActionOnDataChange.AcceptData
Else
Return MyBase.InsertHandler(insertSource, insertedDataSet, customDataSet, _
historyLogLevel, historyLogMessage)
End If
End Function
Public Overrides Function UpdateHandler(ByVal updateSource As SourceIdentifier, _
ByVal updatedDataSet As DataSet, ByRef customDataSet As DataSet, _
ByRef historyLogLevel As Integer, ByRef historyLogMessage As String) _
As ActionOnDataChange
If updateSource = SourceIdentifier.SourceIsPublisher Then
' Build a line item in the audit message to log the Subscriber update.
Dim AuditMessage As StringBuilder = New StringBuilder()
AuditMessage.Append(String.Format("An existing order was updated at {0}. " + _
"The SalesOrderID for the order is ", subscriberName))
AuditMessage.Append(updatedDataSet.Tables(0).Rows(0)("SalesOrderID").ToString())
AuditMessage.Append("The order must now be shipped by :")
AuditMessage.Append(updatedDataSet.Tables(0).Rows(0)("DueDate").ToString())
' Set the reference parameter to write the line to the log file.
historyLogMessage = AuditMessage.ToString()
' Set the history log level to the default verbose level.
historyLogLevel = 1
' Accept the updated data in the Subscriber's data set and apply it to the Publisher.
Return ActionOnDataChange.AcceptData
Else
Return MyBase.UpdateHandler(updateSource, updatedDataSet, _
customDataSet, historyLogLevel, historyLogMessage)
End If
End Function
Public Overrides Function DeleteHandler(ByVal deleteSource As SourceIdentifier, _
ByVal deletedDataSet As DataSet, ByRef historyLogLevel As Integer, _
ByRef historyLogMessage As String) As ActionOnDataDelete
If deleteSource = SourceIdentifier.SourceIsSubscriber Then
' Build a line item in the audit message to log the Subscriber deletes.
' Note that the rowguid is the only information that is
' available in the dataset.
Dim AuditMessage As StringBuilder = New StringBuilder()
AuditMessage.Append(String.Format("An existing order was deleted at {0}. " + _
"The rowguid for the order is ", subscriberName))
AuditMessage.Append(deletedDataSet.Tables(0).Rows(0)("rowguid").ToString())
' Set the reference parameter to write the line to the log file.
historyLogMessage = AuditMessage.ToString()
' Set the history log level to the default verbose level.
historyLogLevel = 1
' Accept the delete and apply it to the Publisher.
Return ActionOnDataDelete.AcceptDelete
Else
Return MyBase.DeleteHandler(deleteSource, deletedDataSet, _
historyLogLevel, historyLogMessage)
End If
End Function
End Class
End Namespace
下列範例會在散發者上註冊商務邏輯處理常式組件，並將現有的合併發行項變更為使用這個自訂商務邏輯。
DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @friendlyname AS sysname;
DECLARE @assembly AS nvarchar(500);
DECLARE @class AS sysname;
SET @publication = N'AdvWorksCustomers';
SET @article = N'Customers';
SET @friendlyname = N'OrderEntryLogic';
SET @assembly = N'C:\Program Files\Microsoft SQL Server\100\COM\CustomLogic.dll';
SET @class = N'Microsoft.Samples.SqlServer.BusinessLogicHandler.OrderEntryBusinessLogicHandler';
-- Register the business logic handler at the Distributor.
EXEC sys.sp_registercustomresolver
@article_resolver = @friendlyname,
@resolver_clsid = NULL,
@is_dotnet_assembly = N'true',
@dotnet_assembly_name = @assembly,
@dotnet_class_name = @class;
-- Add an article that uses the business logic handler
-- at the Publisher.
EXEC sp_changemergearticle
@publication = @publication,
@article = @article,
@property = N'article_resolver',
@value = @friendlyname,
@force_invalidate_snapshot = 0,
@force_reinit_subscription = 0;
GO