locked
How to sync a SQL Server peer-to-peer scope of only filtered parent records and related child records RRS feed

  • Question

  • I have an ado 2.9 sync app that syncs 2 SQL Servers databases, and am not sure how to enforce syncing only related child records for child table(s) that I have created a custom adapter for and added to my custom DbSyncProvider providers. I set up my custom provider and adapters as follows:

     

    private DbSyncProvider CreateStandard2WayProvider( string DBConnString, string strScopeName )
    {
        // create a new provider
        DbSyncProvider provider = new DbSyncProvider();
        provider.Connection = new SqlConnection( DBConnString );
        provider.ScopeName = strScopeName;

        // bind standard 2-way provider commands
        BindProviderCommands( provider );

        // create custom sync adapters for base tables
        CreateAndBindSyncAdapters( provider, "customer", "customerid" );
        CreateAndBindSyncAdapters( provider, "orders", "orderid" );


        return provider;
    }


    private DbSyncProvider CreateAndBindSyncAdapters( DbSyncProvider provider, string strSyncTable, string strPKColumn )
    {
        // create adapter for base table
        DbSyncAdapter adapter = new DbSyncAdapter( strSyncTable );
        adapter.RowIdColumns.Add( strPKColumn );

        // bind source and destination adapter commands
        BindSourceAdapterCommands( adapter, strSyncTable );
        BindDestinationAdapterCommands( adapter, strSyncTable );

        // bind adapters to provider
        provider.SyncAdapters.Add( adapter );

        return provider;
    }

     

    The adapter commands are stored procs coded in the similar format as the auto-generated procs from the SqlSyncProvider.Everything is working fine with the exception that both tables have all records synched regardless of a FK relationship between the 2 tables. Customer is the parent and Orders is the child with a CustomerID FK. I have a custom filter on CustomerType on the Customer table and call the SelectIncrementalChanges command like:

     

    switch (strTable.ToLower())
    {
        case "customer":
            adapterCmd.CommandText = "Sync.Customer_SelectChanges";
            adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt );
            adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int );
            adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int );
            // custom filter by customer type
            adapterCmd.Parameters.Add( new SqlParameter( "@sync_filter_ctype", strCustomerType ) );
            break;

        case "orders":
            adapterCmd.CommandText = "Sync.Orders_SelectChanges";
            adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt );
            adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int );
            adapterCmd.Parameters.Add( "@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int );
            break;

        default:
            throw new ArgumentException( String.Format( "invalid entity name: {0}", strTable ) );
            break;
    }

     

    This filtering of Customer works fine except that *all* child Orders records are also synched - and I want only child Orders records synched that are linked to Customer records that get synched with respect to the custom filter passed.

     

    I'm sure there must be a very straightforward way to accomplsih this. Can someone please enlighten me with a sample code snippet?

     

    Thanks

    Tuesday, June 8, 2010 10:08 PM

Answers

  • Your way is correct and I have confirmed there is no easier way to configure the filtering.
    • Marked as answer by Glenn2654 Monday, June 14, 2010 9:55 PM
    Monday, June 14, 2010 9:17 PM
    Answerer

All replies

  • Hey,

    You might want to check out a related thread of the "cross table" filter scenario.

     

    http://social.msdn.microsoft.com/Forums/en-US/syncdevdiscussions/thread/82d0f37f-aea4-4a35-b6a7-5dd4b90c1210 

     

     

    Thanks,


    Ann Tang
    Wednesday, June 9, 2010 12:51 AM
  • I reviewed the referenced post and it seems the author is using simple providers, SqlSyncProviders I assume. I don’t see anything in the post that you referred to that answers my question. The simple providers do a lot of magic under the sheets and what I am wondering is if some of that magic that is encapsulated in the generated adapters/SQL code is something I need to specifically code for in my custom DBSyncProvider and custom DBSyncAdapters.

    I’m pretty sure I ran through a simple provider sample app a while back that incuded 2 tables with a parent child FK relationship defined and I added a filter to the parent table and only the child records linked to parent records that met the filter condition were synched. This is exactly the behavior I want to accomplish with my custom DBSyncProvider and DBSyncAdapters.

    Last night I altered my above code as follows:

     

    switch (strTable.ToLower())

    {

        case "customer" :

            adapterCmd.CommandText = "Sync.Customer_SelectChanges" ;

            adapterCmd.Parameters.Add( "@" + DbSyncSession .SyncMinTimestamp, SqlDbType .BigInt );

            adapterCmd.Parameters.Add( "@" + DbSyncSession .SyncScopeLocalId, SqlDbType .Int );

            adapterCmd.Parameters.Add( "@" + DbSyncSession .SyncScopeRestoreCount, SqlDbType .Int );

            // custom filter by customer type

            adapterCmd.Parameters.Add( new SqlParameter ( "@sync_filter_ctype" , strCustomerType ) );

            break ;

     

        case "orders" :

            adapterCmd.CommandText = "Sync.Orders_SelectChanges" ;

            adapterCmd.Parameters.Add( "@" + DbSyncSession .SyncMinTimestamp, SqlDbType .BigInt );

            adapterCmd.Parameters.Add( "@" + DbSyncSession .SyncScopeLocalId, SqlDbType .Int );

            adapterCmd.Parameters.Add( "@" + DbSyncSession .SyncScopeRestoreCount, SqlDbType .Int );

            // custom filter by customer type

            adapterCmd.Parameters.Add( new SqlParameter ( "@sync_filter_ctype" , strCustomerType ) );

            break ;

     

        default :

            throw new ArgumentException ( String .Format( "invalid entity name: {0}" , strTable ) );

            break ;

    }

    such that now I am passing the filter condition to both the parent table’s SelectIncrementalChanges stored proc and also the child table’s SelectIncrementalChanges stored proc. And in the child table’s stored proc, of course, I am joining to the parent table and applying the filter to restrict any possible child updateable records to only those matching the parent’s filter condition. The parent and child queries now look like:

     

    Customer (parent) table query:

     

    SELECT

          BLAH BLAH BLAH La Di Da La Di Da

    FROM

          [Customer] [base]

          RIGHT JOIN [Sync]. [Customer_tracking] [side] ON [base]. [CustomerID] = [side]. [Custome

    WHERE

          [side]. [local_update_peer_timestamp] > @sync_min_timestamp

          and [base]. [CustomerType] = @sync_filter_ctype

     

    Orders (child) table query:

     

    SELECT

          BLAH BLAH BLAH La Di Da La Di Da

    FROM

          [Orders] [base]

          RIGHT JOIN [Sync]. [Orders_tracking] [side] ON [base]. [OrderID] = [side]. [OrderID]

          JOIN Customer c ON c. CustomerId = [base]. CustomerId
     

    WHERE

          [side]. [local_update_peer_timestamp] > @sync_min_timestamp

          AND c. CustomerType = @sync_filter_ctype

     

    So, as you can see, I have accomplished what I want to do – but is there an easier way to do this? It seems as though the simple providers handle this condition, but I haven’t examined their generated code closely to see how it is accomplished.

     

    Are there any ADO.NET sync services API call(s) I can utilize to configure my sync process to *recognize* the parent child FK relationships and act only on the child records linked to the filtered parent records using custom providers and DBSyncAdapters or do I have to code specifically for it within the SQL code as I have demonstrated above? What I'd like to have is the ability to add a filter condition to my parent table adapter/SQL and any FK relationships with child tables should be automatically recognized instead of me having to code for it. I also saw some FK relationship objects and related collections in the sync API, but couldn't tell how they are used due to lacking documentation ... which makes me wonder if these objects have anything to do with establishing necessary FK relationship info the sync providers may need to accomplish this?

     

    Glenn

    Wednesday, June 9, 2010 6:00 PM
  • Your way is correct and I have confirmed there is no easier way to configure the filtering.
    • Marked as answer by Glenn2654 Monday, June 14, 2010 9:55 PM
    Monday, June 14, 2010 9:17 PM
    Answerer