none
Filter Data using SyncAdapter RRS feed

  • Question

  • I am developing an application which will run on Windows Mobile 6. I used the data cache wizard of Visual Studio 2008 SP1 to develop an application which syncs two databases. I need to filter data so that only a subset of data will be sync'ed to the mobile device. I know that SqlSyncAdapterBuilder can be used for filtering the rows, but the designer generated code is deriving from SyncAdapter which doesn't have the filter property. How do I go about filtering data with SyncAdapter without having the designer override changes each time?

    Thanks in advance
    Friday, March 12, 2010 9:42 AM

Answers

  • here you go:

    // Call SyncAgent.Synchronize() to initiate the synchronization process.
    // Synchronization only updates the local database, not your project's data source.
    LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
    
    //let's retrieve our sync provider so we can get access to the adapter
    LocalDataCache1ServerSyncProvider remoteProvidewr = (LocalDataCache1ServerSyncProvider)syncAgent.RemoteProvider;
                
    //let's take the command object for the SelectIncrementalInserts
    IDbCommand command = remoteProvidewr.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand;
                
    //let's build our filter
    string myFilter = " (SalesLT.Customer.CustomerId=@CustomerId) AND ";
                
    //there are two select statements in the generated command
    //the first is for @sync_initialized = 0, the other one for subsequent syncs
    //so we need to insert our filter on both statements
    command.CommandText = command.CommandText.Insert(command.CommandText.IndexOf("WHERE") + 5, myFilter);
    command.CommandText = command.CommandText.Insert(command.CommandText.LastIndexOf("WHERE") + 5, myFilter);
    
    //let's add our filter parameter
    command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.Int));
    
    //now, let's replace the designer generated command with our new command with filter
                remoteProvidewr.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand = command;
    
    //now, let's pass a value to our filter before calling the sync
    syncAgent.Configuration.SyncParameters.Add(new SyncParameter("@CustomerID", 1));
    
    Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
    
    // TODO: Reload your project data source from the local database (for example, call the TableAdapter.Fill method).
    

    The code above is just for the SelectIncrementalInserts, you will have to do the same for all the other incremental Selects.

    The adding of the filter is not on the designer generated code and is done prior to calling the sync.

    Also, if you notice, the filter string has a space as its first and last character to make sure its not concatenated with the existing statements.

    Btw, I used the AdventureWorksLT Customer database as a sample using SQL Change tracking and just took the default name assigned by the designer.

    you can find sample code here: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1203.entry

    let me know how it goes.

    cheers,

    JuneT
    Tuesday, March 16, 2010 3:33 PM
    Moderator

All replies

  • here you go:

    // Call SyncAgent.Synchronize() to initiate the synchronization process.
    // Synchronization only updates the local database, not your project's data source.
    LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
    
    //let's retrieve our sync provider so we can get access to the adapter
    LocalDataCache1ServerSyncProvider remoteProvidewr = (LocalDataCache1ServerSyncProvider)syncAgent.RemoteProvider;
                
    //let's take the command object for the SelectIncrementalInserts
    IDbCommand command = remoteProvidewr.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand;
                
    //let's build our filter
    string myFilter = " (SalesLT.Customer.CustomerId=@CustomerId) AND ";
                
    //there are two select statements in the generated command
    //the first is for @sync_initialized = 0, the other one for subsequent syncs
    //so we need to insert our filter on both statements
    command.CommandText = command.CommandText.Insert(command.CommandText.IndexOf("WHERE") + 5, myFilter);
    command.CommandText = command.CommandText.Insert(command.CommandText.LastIndexOf("WHERE") + 5, myFilter);
    
    //let's add our filter parameter
    command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.Int));
    
    //now, let's replace the designer generated command with our new command with filter
                remoteProvidewr.SalesLT_CustomerSyncAdapter.SelectIncrementalInsertsCommand = command;
    
    //now, let's pass a value to our filter before calling the sync
    syncAgent.Configuration.SyncParameters.Add(new SyncParameter("@CustomerID", 1));
    
    Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
    
    // TODO: Reload your project data source from the local database (for example, call the TableAdapter.Fill method).
    

    The code above is just for the SelectIncrementalInserts, you will have to do the same for all the other incremental Selects.

    The adding of the filter is not on the designer generated code and is done prior to calling the sync.

    Also, if you notice, the filter string has a space as its first and last character to make sure its not concatenated with the existing statements.

    Btw, I used the AdventureWorksLT Customer database as a sample using SQL Change tracking and just took the default name assigned by the designer.

    you can find sample code here: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1203.entry

    let me know how it goes.

    cheers,

    JuneT
    Tuesday, March 16, 2010 3:33 PM
    Moderator
  • Thank you, much appreciated. Hopefully in future the designer will allow you to add the filters.
    Monday, March 22, 2010 8:56 AM
  • The code above is just for the SelectIncrementalInserts, you will have to do the same for all the other incremental Selects.

    The adding of the filter is not on the designer generated code software and is done prior to calling the sync.

    Also, if you notice, the filter string has a space as its first and last character to make sure its not concatenated with the existing statements.

    Btw, I used the AdventureWorksLT Customer database as a sample using SQL Change tracking and just took the default name assigned by the designer.

    you can find sample code here: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1203.entry

    let me know how it goes.

    cheers,

    JuneT

    Many thanks for your extremely detailed answer giving me a clear idea to solve my problem. I'll also check out the link about sample code. Thanks again.
    Tuesday, June 29, 2010 8:05 AM
  • Hi

    I think this code only works for a desktop client.

    For me LocalDataCache1ServerSyncProvider is DeviceDataCacheServerSyncProvider.

    I can see the class DeviceDataCacheServerSyncProvider (which is derived from Microsoft.Synchronization.Data.Server.DbServerSyncProvider) in the Service Library (which is running as a WCF service on the server). This code is auto generated using the Sync Designer wizard for Local Database Cache.

    But I do not see this class available in the smartclient application in my device application. The reason according to me is that this sample code is only meant for desktop clients and will not work for a device based application running in .NET CF.

    The sample solution available in this link (http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1203.entry) is also a desktop console application.

    I will appreciate if someone can provide me with a .NET Compact Framework based sample source code/link.

    Thanks,
    Rajpreet

     

    Thursday, April 28, 2011 2:41 PM
  • for device application, what you have on the client is just a proxy to the WCF-based server provider. so to set the filter, you can either do it in the proxy or on the service itself. you can add an extra parameter to one of the method calls before synching or add a separate method/operation to explicitly set the filter on the server side.

    Thursday, April 28, 2011 11:21 PM
    Moderator