How to: Define a Logical Record Relationship Between Merge Table Articles (RMO Programming)
Megjegyzés
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Merge replication allows you to define a relationship between related rows in different tables. These rows can then be processed as a transactional unit during synchronization. A logical record can be defined between two articles whether or not they have a join filter relationship. For more information, see Grouping Changes to Related Rows with Logical Records.
You can programmatically specify logical record relationships between articles using Replication Management Objects (RMO).
Megjegyzés
Merge replication allows you to specify that conflicts be tracked and resolved at the logical record level, but these options cannot be set using RMO. For information about setting these options using replication stored procedures, see How to: Define a Logical Record Relationship Between Merge Table Articles (Replication Transact-SQL Programming).
To define a logical record relationship without an associated join filter
Create a connection to the Publisher by using the ServerConnection class.
Create an instance of the MergePublication class, set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.
Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.
If the PartitionGroupsOption property is set to False, set it to True.
If the articles that are to comprise the logical record do not exist, create an instance of the MergeArticle class, and set the following properties:
The name of the article for Name.
The name of the publication for PublicationName.
(Optional) If the article is horizontally filtered, specify the row filter clause for the FilterClause property. Use this property to specify a static or parameterized row filter. For more information, see Parameterized Row Filters.
For more information, see How to: Define an Article (RMO Programming).
Call the Create method.
Repeat steps 5 and 6 for each article comprising the logical record.
Create an instance of the MergeJoinFilter class to define the logical record relationship between articles. Then, set the following properties:
The name of the child article in the logical record relationship for the ArticleName property.
The name of the existing, parent article in the logical record relationship for the JoinArticleName property.
A name for the logical record relationship for the FilterName property.
The expression that defines the relationship for the JoinFilterClause property.
A value of LogicalRecordLink for the FilterTypes property. If the logical record relationship is also a join filter, specify a value of JoinFilterAndLogicalRecordLink for this property. For more information, see Grouping Changes to Related Rows with Logical Records.
Call the AddMergeJoinFilter method on the object that represents the child article in the relationship. Pass the MergeJoinFilter object from step 8 to define the relationship.
Repeat steps 8 and 9 for each remaining logical record relationship in the publication.
Example
This example creates a logical record comprising the two new articles for the SalesOrderHeader and SalesOrderDetail tables.
// Define the Publisher and publication names.
string publisherName = publisherInstance;
string publicationName = "AdvWorksSalesOrdersMerge";
string publicationDbName = "AdventureWorks2008R2";
// Specify article names.
string articleName1 = "SalesOrderHeader";
string articleName2 = "SalesOrderDetail";
// Specify logical record information.
string lrName = "SalesOrderHeader_SalesOrderDetail";
string lrClause = "[SalesOrderHeader].[SalesOrderID] = "
+ "[SalesOrderDetail].[SalesOrderID]";
string schema = "Sales";
MergeArticle article1 = new MergeArticle();
MergeArticle article2 = new MergeArticle();
MergeJoinFilter lr = new MergeJoinFilter();
MergePublication publication = new MergePublication();
// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);
try
{
// Connect to the Publisher.
conn.Connect();
// Verify that the publication uses precomputed partitions.
publication.Name = publicationName;
publication.DatabaseName = publicationDbName;
publication.ConnectionContext = conn;
// If we can't get the properties for this merge publication, then throw an application exception.
if (publication.LoadProperties())
{
// If precomputed partitions is disabled, enable it.
if (publication.PartitionGroupsOption == PartitionGroupsOption.False)
{
publication.PartitionGroupsOption = PartitionGroupsOption.True;
}
}
else
{
throw new ApplicationException(String.Format(
"Settings could not be retrieved for the publication. " +
"Ensure that the publication {0} exists on {1}.",
publicationName, publisherName));
}
// Set the required properties for the PurchaseOrderHeader article.
article1.ConnectionContext = conn;
article1.Name = articleName1;
article1.DatabaseName = publicationDbName;
article1.SourceObjectName = articleName1;
article1.SourceObjectOwner = schema;
article1.PublicationName = publicationName;
article1.Type = ArticleOptions.TableBased;
// Set the required properties for the SalesOrderDetail article.
article2.ConnectionContext = conn;
article2.Name = articleName2;
article2.DatabaseName = publicationDbName;
article2.SourceObjectName = articleName2;
article2.SourceObjectOwner = schema;
article2.PublicationName = publicationName;
article2.Type = ArticleOptions.TableBased;
if (!article1.IsExistingObject) article1.Create();
if (!article2.IsExistingObject) article2.Create();
// Define a logical record relationship between
// PurchaseOrderHeader and PurchaseOrderDetail.
// Parent article.
lr.JoinArticleName = articleName1;
// Child article.
lr.ArticleName = articleName2;
lr.FilterName = lrName;
lr.JoinUniqueKey = true;
lr.FilterTypes = FilterTypes.LogicalRecordLink;
lr.JoinFilterClause = lrClause;
// Add the logical record definition to the parent article.
article1.AddMergeJoinFilter(lr);
}
catch (Exception ex)
{
// Do error handling here and rollback the transaction.
throw new ApplicationException(
"The filtered articles could not be created", ex);
}
finally
{
conn.Disconnect();
}
' Define the Publisher and publication names.
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publicationDbName As String = "AdventureWorks2008R2"
' Specify article names.
Dim articleName1 As String = "SalesOrderHeader"
Dim articleName2 As String = "SalesOrderDetail"
' Specify logical record information.
Dim lrName As String = "SalesOrderHeader_SalesOrderDetail"
Dim lrClause As String = "[SalesOrderHeader].[SalesOrderID] = " _
& "[SalesOrderDetail].[SalesOrderID]"
Dim schema As String = "Sales"
Dim article1 As MergeArticle = New MergeArticle()
Dim article2 As MergeArticle = New MergeArticle()
Dim lr As MergeJoinFilter = New MergeJoinFilter()
Dim publication As MergePublication = New MergePublication()
' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)
Try
' Connect to the Publisher.
conn.Connect()
' Verify that the publication uses precomputed partitions.
publication.Name = publicationName
publication.DatabaseName = publicationDbName
publication.ConnectionContext = conn
' If we can't get the properties for this merge publication, then throw an application exception.
If publication.LoadProperties() Then
' If precomputed partitions is disabled, enable it.
If publication.PartitionGroupsOption = PartitionGroupsOption.False Then
publication.PartitionGroupsOption = PartitionGroupsOption.True
End If
Else
Throw New ApplicationException(String.Format( _
"Settings could not be retrieved for the publication. " _
& "Ensure that the publication {0} exists on {1}.", _
publicationName, publisherName))
End If
' Set the required properties for the SalesOrderHeader article.
article1.ConnectionContext = conn
article1.Name = articleName1
article1.DatabaseName = publicationDbName
article1.SourceObjectName = articleName1
article1.SourceObjectOwner = schema
article1.PublicationName = publicationName
article1.Type = ArticleOptions.TableBased
' Set the required properties for the SalesOrderDetail article.
article2.ConnectionContext = conn
article2.Name = articleName2
article2.DatabaseName = publicationDbName
article2.SourceObjectName = articleName2
article2.SourceObjectOwner = schema
article2.PublicationName = publicationName
article2.Type = ArticleOptions.TableBased
If Not article1.IsExistingObject Then
article1.Create()
End If
If Not article2.IsExistingObject Then
article2.Create()
End If
' Define a logical record relationship between
' SalesOrderHeader and SalesOrderDetail.
' Parent article.
lr.JoinArticleName = articleName1
' Child article.
lr.ArticleName = articleName2
lr.FilterName = lrName
lr.JoinUniqueKey = True
lr.FilterTypes = FilterTypes.LogicalRecordLink
lr.JoinFilterClause = lrClause
' Add the logical record definition to the parent article.
article1.AddMergeJoinFilter(lr)
Catch ex As Exception
' Do error handling here and rollback the transaction.
Throw New ApplicationException( _
"The filtered articles could not be created", ex)
Finally
conn.Disconnect()
End Try
See Also