How to: Define a Logical Record Relationship Between Merge Table Articles (RMO Programming)

Article
12/03/2008

New: 5 December 2005

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).

Note

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 = "AdventureWorks";

// 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 = "AdventureWorks"

' 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

How to: Define a Logical Record Relationship Between Merge Table Articles (RMO Programming)

To define a logical record relationship without an associated join filter

Example

See Also

Tasks

Other Resources

Help and Information

Additional resources