適用於：
SQL Server
本文說明如何在 SQL Server 中使用複寫程式設計或複寫管理物件（Replication Management Objects，RMO），為合併文章實作商業邏輯處理常式。
Microsoft.SqlServer.Replication.BusinessLogicSupport 命名空間實作了一個介面，供您用來撰寫複雜的商務邏輯，以處理在合併複寫同步處理期間發生的事件。 複寫程序會在同步期間，針對其所複寫的每個已變更資料列，呼叫商業邏輯處理常式中的方法。
實作商務邏輯處理常式的一般程序如下：
建立商業邏輯處理元件組件。
請在經銷商處註冊該組件。
在合併代理程式執行所在的伺服器上部署此組件。 對於提取訂閱，代理程式會在訂閱者端執行。 推送訂閱時，代理程式會運行在 Distributor 上。 使用網頁同步時，代理程式會在網頁伺服器上執行。
建立使用商務邏輯處理常式的發行項，或是修改現有的發行項來使用商務邏輯處理常式。
指定的商務邏輯處理常式會針對要同步處理的每一個資料列執行。 複雜的邏輯以及呼叫其他應用程式或網路服務會影響效能。 如需商務邏輯處理常式的詳細資訊，請參閱在合併同步處理期間執行商務邏輯。
使用複寫程式設計
建立及部署商務邏輯處理常式
在 Microsoft Visual Studio 中，針對包含此商務邏輯處理常式實作程式碼的 .NET 組件建立新專案。
針對下列命名空間加入此專案的參考。
組件參考 位置 Microsoft.SqlServer.Replication.BusinessLogicSupport < drive>：\Program Files\Microsoft SQL Server\nnn\COM （默認安裝） System.Data GAC（.NET 框架的元件） System.Data.Common GAC（.NET 框架的元件） 加入會覆寫 BusinessLogicModule 類別的類別。
實作 HandledChangeStates 屬性，以指出處理常式所管理的變更類型。
覆寫 BusinessLogicModule 類別的下列其中一個或多個方法：
CommitHandler - 在同步處理期間認可資料變更時叫用。
DeleteErrorHandler - 當上傳或下載 DELETE 陳述式時發生錯誤時呼叫。
DeleteHandler - 在上傳或下載 DELETE 陳述式時被呼叫。
InsertErrorHandler - 當正在上傳或下載 INSERT 陳述式時發生錯誤，便會被呼叫。
InsertHandler - 在上傳或下載 INSERT 陳述式時被呼叫。
UpdateConflictsHandler - 當 Publisher 和 Subscriber 端出現衝突的 UPDATE 陳述時，會被叫用。
UpdateDeleteConflictHandler- 當UPDATE聲明與 DELETE Publisher 和 Subscriber 的聲明相衝突時會被啟動。
UpdateErrorHandler - 當正在上傳或下載 UPDATE 陳述式時發生錯誤，便會被呼叫。
UpdateHandler - 在上傳或下載 UPDATE 陳述式時被呼叫。
建立專案來建立商務邏輯處理常式組件。
在包含合併代理程式可執行檔 （replmerg.exe） 的目錄中部署元件，預設安裝為 <drive>：\Program Files\Microsoft SQL Server\nnn\COM，或將其安裝在 .NET 全域程式集緩存 （GAC） 中。 只有當非 合併代理程式 的應用程式需要存取該組裝時，才在 GAC 中安裝該組件。 使用 .NET Framework SDK 提供的全域組合快取工具（Gacutil.exe）將組合語言安裝到 GAC。
注意
你必須在每台執行 合併代理程式 的伺服器上部署商業邏輯處理器。 此要求包括在使用網頁同步時承載 replisapi.dll 的 IIS 伺服器。
註冊業務邏輯處理程序
在發行者端，執行 sp_enumcustomresolvers (Transact-SQL)，以確認該組件尚未註冊為商務邏輯處理常式。
在 Distributor 執行 sp_registercustomresolver (Transact-SQL)，為 @article_resolver 指定商業邏輯處理常式的易記名稱，將 @is_dotnet_assembly 設為 true，為 @dotnet_assembly_name 指定組件名稱，並為 @dotnet_class_name 指定覆寫 BusinessLogicModule 的類別完整名稱。
注意
如果你沒有將組合語言部署在與合併代理程式執行檔相同的目錄、與同步啟動合併代理程式的應用程式相同的目錄，或是在全域組合語言快取（GAC）中，你需要指定完整的路徑並附上組合語言名稱@dotnet_assembly_name。 使用網頁同步時，必須指定組裝裝置在網頁伺服器的位置。
要在新的表格條目中使用商務邏輯處理常式
- 執行 sp_addmergearticle (Transact-SQL) 以定義發行項，並為 @article_resolver 指定商務邏輯處理常式的易記名稱。 如需詳細資訊，請參閱 定義發行項。
搭配現有的資料表項目使用商業邏輯處理程式
- 執行 sp_changemergearticle （Transact-SQL），並指定 @publication、 @article、 article_resolver 作為 @property，以及商業邏輯處理器的友善名稱作為 @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\120\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
使用 Replication Management Objects (RMO)
建立業務邏輯處理函式
在 Microsoft Visual Studio 中，針對包含此商務邏輯處理常式實作程式碼的 .NET 組件建立新專案。
針對下列命名空間加入此專案的參考。
組件參考 位置 Microsoft.SqlServer.Replication.BusinessLogicSupport < drive>：\Program Files\Microsoft SQL Server\nnn\COM （默認安裝） System.Data GAC（.NET 框架的元件） System.Data.Common GAC（.NET 框架的元件） 加入會覆寫 BusinessLogicModule 類別的類別。
實作 HandledChangeStates 屬性，以指出處理常式所管理的變更類型。
覆寫 BusinessLogicModule 類別的下列其中一個或多個方法：
CommitHandler - 在同步處理期間認可資料變更時叫用。
DeleteErrorHandler - 如果在上傳或下載 DELETE 陳述式時發生錯誤，則會被呼叫。
DeleteHandler - 在上傳或下載 DELETE 陳述式時被呼叫。
InsertErrorHandler - 當語句上傳或下載時發生 INSERT 錯誤時，會被呼叫。
InsertHandler - 在上傳或下載 INSERT 陳述式時被呼叫。
UpdateConflictsHandler - 當 Publisher 和 Subscriber 端出現衝突的 UPDATE 陳述時，會被叫用。
UpdateDeleteConflictHandler- 當UPDATE聲明與 DELETE Publisher 和 Subscriber 的聲明相衝突時會被啟動。
UpdateErrorHandler - 當語句上傳或下載時發生 UPDATE 錯誤時，會被呼叫。
UpdateHandler - 在上傳或下載 UPDATE 陳述式時被呼叫。
注意
文章的預設解析程式會處理任何你自訂的商業邏輯未明確處理的文章衝突。
建立專案來建立商務邏輯處理常式組件。
註冊業務邏輯處理程序
使用 ServerConnection 類別建立與散發者的連接。
建立 ReplicationServer 類別的執行個體。 傳遞步驟 1 的 ServerConnection 。
呼叫 EnumBusinessLogicHandlers 並檢查傳回的 ArrayList 物件，以確保該組件尚未註冊為商務邏輯處理常式。
建立 BusinessLogicHandler 類別的執行個體。 指定下列屬性：
DotNetAssemblyName - .NET 組件的名稱。 如果你沒有將 assembly 部署在與 合併代理程式 執行檔相同的目錄、與同步啟動 合併代理程式 的應用程式相同的目錄，或是在 GAC 中，請包含完整的路徑與組合名稱。 當您使用 Web 同步處理搭配商業邏輯處理器時，必須包含完整路徑以及組件名稱。
DotNetClassName - 覆寫 BusinessLogicModule 並實作商業邏輯處理常式之類別的完整限定名稱。
FriendlyName - 當您存取商務邏輯處理常式時所要使用的易記名稱。
IsDotNetAssembly - \@is_dotnet_assembly＞。
部署商務邏輯處理器
- 將該組件部署到執行 合併代理程式 的伺服器上，並放置於您在散發者端註冊商業邏輯處理常式時所指定的檔案位置。 對於拉取訂閱，代理程式會在訂閱者端執行。 推送訂閱時，代理程式會運行在 Distributor 上。 使用網頁同步時，代理程式會在網頁伺服器上執行。 如果您在註冊商業邏輯處理常式時，未包含組件名稱的完整路徑，請將該組件部署在與 合併代理程式 可執行檔相同的目錄中，或部署在同步啟動 合併代理程式 的應用程式所在的相同目錄中。 如果多個應用程式使用同一個組件，請將該組件安裝在 GAC 中。
要在新的表格條目中使用商務邏輯處理常式
使用 ServerConnection 類別建立與發行者的連接。
建立 MergeArticle 類別的執行個體。 設定下列屬性：
將 Name設定為發行項名稱。
將 PublicationName設定為發行集名稱。
刊物資料庫的名稱是DatabaseName。
將FriendlyName設定為商務邏輯處理常式的易記名稱 ( ArticleResolver)。
搭配現有的資料表項目使用商業邏輯處理程式
使用 ServerConnection 類別建立與發行者的連接。
建立 MergeArticle 類別的執行個體。
設定 Name、 PublicationName和 DatabaseName 屬性。
針對 ConnectionContext 屬性設定步驟 1 中的連接。
呼叫 LoadProperties 方法以取得物件的屬性。 如果這個方法回 傳 false，可能是你在步驟 3 中定義錯了文章屬性，或者文章根本不存在。 如需詳細資訊，請參閱 View and Modify Article Properties。
將 ArticleResolver設定為商務邏輯處理常式的易記名稱。 這個值就是 FriendlyName 你在註冊商業邏輯處理器時所指定的屬性。
範例 (RMO)
這個範例是一個商務邏輯處理常式，它會記錄有關在訂閱者上插入、更新和刪除的資訊。
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
這個例子會在發佈伺服器上註冊商務邏輯處理常式。
// Specify the Distributor name and business logic properties.
string distributorName = publisherInstance;
string assemblyName = @"C:\Program Files\Microsoft SQL Server\110\COM\CustomLogic.dll";
string className = "Microsoft.Samples.SqlServer.BusinessLogicHandler.OrderEntryBusinessLogicHandler";
string friendlyName = "OrderEntryLogic";
ReplicationServer distributor;
BusinessLogicHandler customLogic;
// Create a connection to the Distributor.
ServerConnection distributorConn = new ServerConnection(distributorName);
try
{
// Connect to the Distributor.
distributorConn.Connect();
// Set the Distributor properties.
distributor = new ReplicationServer(distributorConn);
// Set the business logic handler properties.
customLogic = new BusinessLogicHandler();
customLogic.DotNetAssemblyName = assemblyName;
customLogic.DotNetClassName = className;
customLogic.FriendlyName = friendlyName;
customLogic.IsDotNetAssembly = true;
Boolean isRegistered = false;
// Check if the business logic handler is already registered at the Distributor.
foreach (BusinessLogicHandler registeredLogic
in distributor.EnumBusinessLogicHandlers())
{
if (registeredLogic == customLogic)
{
isRegistered = true;
}
}
// Register the custom logic.
if (!isRegistered)
{
distributor.RegisterBusinessLogicHandler(customLogic);
}
}
catch (Exception ex)
{
// Do error handling here.
throw new ApplicationException(string.Format(
"The {0} assembly could not be registered.",
assemblyName), ex);
}
finally
{
distributorConn.Disconnect();
}
' Specify the Distributor name and business logic properties.
Dim distributorName As String = publisherInstance
Dim assemblyName As String = "C:\Program Files\Microsoft SQL Server\110\COM\CustomLogic.dll"
Dim className As String = "Microsoft.Samples.SqlServer.BusinessLogicHandler.OrderEntryBusinessLogicHandler"
Dim friendlyName As String = "OrderEntryLogic"
Dim distributor As ReplicationServer
Dim customLogic As BusinessLogicHandler
' Create a connection to the Distributor.
Dim distributorConn As ServerConnection = New ServerConnection(distributorName)
Try
' Connect to the Distributor.
distributorConn.Connect()
' Set the Distributor properties.
distributor = New ReplicationServer(distributorConn)
' Set the business logic handler properties.
customLogic = New BusinessLogicHandler()
customLogic.DotNetAssemblyName = assemblyName
customLogic.DotNetClassName = className
customLogic.FriendlyName = friendlyName
customLogic.IsDotNetAssembly = True
Dim isRegistered As Boolean = False
' Check if the business logic handler is already registered at the Distributor.
For Each registeredLogic As BusinessLogicHandler _
In distributor.EnumBusinessLogicHandlers
If registeredLogic Is customLogic Then
isRegistered = True
End If
Next
' Register the custom logic.
If Not isRegistered Then
distributor.RegisterBusinessLogicHandler(customLogic)
End If
Catch ex As Exception
' Do error handling here.
Throw New ApplicationException(String.Format( _
"The {0} assembly could not be registered.", _
assemblyName), ex)
Finally
distributorConn.Disconnect()
End Try
此範例會變更現有的發行項來使用商務邏輯處理常式。
// Define the Publisher, publication, and article names.
string publisherName = publisherInstance;
string publicationName = "AdvWorksSalesOrdersMerge";
string publicationDbName = "AdventureWorks2022";
string articleName = "SalesOrderHeader";
// Set the friendly name of the business logic handler.
string customLogic = "OrderEntryLogic";
MergeArticle article = new MergeArticle();
// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);
try
{
// Connect to the Publisher.
conn.Connect();
// Set the required properties for the article.
article.ConnectionContext = conn;
article.Name = articleName;
article.DatabaseName = publicationDbName;
article.PublicationName = publicationName;
// Load the article properties.
if (article.LoadProperties())
{
article.ArticleResolver = customLogic;
}
else
{
// Throw an exception of the article does not exist.
throw new ApplicationException(String.Format(
"{0} is not published in {1}", articleName, publicationName));
}
}
catch (Exception ex)
{
// Do error handling here and rollback the transaction.
throw new ApplicationException(String.Format(
"The business logic handler {0} could not be associated with " +
" the {1} article.",customLogic,articleName), ex);
}
finally
{
conn.Disconnect();
}
' Define the Publisher, publication, and article names.
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publicationDbName As String = "AdventureWorks2022"
Dim articleName As String = "SalesOrderHeader"
' Set the friendly name of the business logic handler.
Dim customLogic As String = "OrderEntryLogic"
Dim article As MergeArticle = New MergeArticle()
' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)
Try
' Connect to the Publisher.
conn.Connect()
' Set the required properties for the article.
article.ConnectionContext = conn
article.Name = articleName
article.DatabaseName = publicationDbName
article.PublicationName = publicationName
' Load the article properties.
If article.LoadProperties() Then
article.ArticleResolver = customLogic
Else
' Throw an exception of the article does not exist.
Throw New ApplicationException(String.Format( _
"{0} is not published in {1}", articleName, publicationName))
End If
Catch ex As Exception
' Do error handling here and rollback the transaction.
Throw New ApplicationException(String.Format( _
"The business logic handler {0} could not be associated with " + _
" the {1} article.", customLogic, articleName), ex)
Finally
conn.Disconnect()
End Try