locked
How to extend Wizard generated Code to support row filters? RRS feed

  • Question

  • I am developing an OCA with the following setup:

    Server: WCF (sync)-service hosted in IIS 7 on windows server 2008, I use the built-in change tracking in SQL Server 2008.
    Clients: Winform application that use SQLServerCE and can connect to the web service and sync the data.

    To create the application I used the LocalDataCache.sync Wizard supplied by Visual Studio 2010. Split the code in two projects, one for the wcf sync service, one for the winform client. I added another project to host the wcf service on IIS. I deployed the service to IIS.

    To fit my requirements I extended the LocalDataCache1SyncAgent Class with this VB code:

    Partial Public Class LocalDataCache1SyncAgent

        Private Sub OnInitialized()
            Me.Table1.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional
            Me.Table2.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.DownloadOnly
            ' and so on.....

            ' define remote provider and use the generated proxy class for the reference to IIS - sync
       
            Me.RemoteProvider = New Microsoft.Synchronization.Data.ServerSyncProviderProxy(New ServiceReferenceIIS.LocalDataCache1SyncContractClient("MY_SyncContract"))
        End Sub
    End Class

    All this seems to work really fine, but now I want to add rowfilter for some tables (e.g. only download rows for a certain salesperson, matching a criteria like @idSalesperson=SomeVal). I searched MSDN and the forums and found many useful information about rowfilter like:

    - http://msdn.microsoft.com/en-us/library/bb726003.aspx
    - http://code.msdn.microsoft.com/Database-Sync-SQL-Server-7e88adab

    But all the examples I found do not use the wizard generated code. The generated code in LocalDataCache1.Desinger.vb uses Partial Classes Subs so I think I should be able to hook on and add the filters somewhere, but no idea how to do this.

    Is there a way to extend the wizard generated code to support row filters? If so, how do I do this?

    Thanks in advance
    Christian
    Thursday, August 11, 2011 2:14 PM

Answers

  • as i have mentioned, you should try altering the commands in the GetChanges Implemenation:

     <System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Public Overridable Function GetChanges(ByVal groupMetadata As SyncGroupMetadata, ByVal syncSession As SyncSession) As SyncContext Implements ILocalDataCache1SyncContract.GetChanges

         Alter the commands here before the call below


            Return Me._serverSyncProvider.GetChanges(groupMetadata, syncSession)
        End Function

    The SyncAgent only exists in the client so you cant access it from the WCF service side. as i have mentioned, i dont think you have to do anything special with the SyncAgent. just pass the filter parameter value and that value will be in the SyncSession.

    so just try altering the commands as stated above.

    • Marked as answer by chl-h Wednesday, August 17, 2011 9:03 AM
    Tuesday, August 16, 2011 2:01 AM

All replies

  • Thursday, August 11, 2011 2:29 PM
  • Hi JuneT,

    thanks for that useful link, looks exactly what I need. But as my remoteProvider is a ServerSyncProviderProxy I changed your code to

        Private Shared Sub AddFilters(ByVal syncAgent As LocalDataCache1SyncAgent)
            'let’s retrieve our sync provider so we can get access to the adapter
            Dim remoteProvider As ServerSyncProviderProxy = DirectCast(syncAgent.RemoteProvider, ServerSyncProviderProxy)

    .....

    but now I can not take the command objects from the adapter.

            'let’s take the command objects from the adapter
            Dim selectIncrementalInsertsCommand As IDbCommand = remoteProvider.SyncAdapter.SelectIncrementalInsertsCommand
            Dim selectIncrementalUpdatesCommand As IDbCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalUpdatesCommand
            Dim selectIncrementalDeletesCommand As IDbCommand = remoteProvider.SalesLT_CustomerSyncAdapter.SelectIncrementalDeletesCommand

     

    I suppose in your example you are not connecting to a WCF-Service. WHat do I have to modify to get this running?

     

    Thanks in advance, christian

     

     

     

     

    Thursday, August 11, 2011 3:19 PM
  • try this, on the WCF service side, locate the GetChanges (public virtual SyncContext GetChanges) implementation and access the provider's SyncAdapter to modify the commands and add the filter clause before calling the service calls the provider's GetChanges.

    you dont have to do anything on the client side other than adding the filter value. the filter value will be passed as part of the SyncSession. so just make sure you add the filter clause to the commands as mentioned above.

    Friday, August 12, 2011 3:02 AM
  • Hi JuneT,

    sounds very logical to add the code on the server side. But I am not sure how to do this.

    On the WCF-side I have the LocalDataCache1.Server.SyncContract.vb that implemets the GetChanges:

    Partial Public Class LocalDataCache1SyncService
        Inherits Object
        Implements ILocalDataCache1SyncContract
       
        Private _serverSyncProvider As LocalDataCache1ServerSyncProvider
       
        Public Sub New()
            MyBase.New
            Me._serverSyncProvider = New LocalDataCache1ServerSyncProvider()
        End Sub
       
        <System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Public Overridable Function ApplyChanges(ByVal groupMetadata As SyncGroupMetadata, ByVal dataSet As DataSet, ByVal syncSession As SyncSession) As SyncContext Implements ILocalDataCache1SyncContract.ApplyChanges
            Return Me._serverSyncProvider.ApplyChanges(groupMetadata, dataSet, syncSession)
        End Function
       
        <System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Public Overridable Function GetChanges(ByVal groupMetadata As SyncGroupMetadata, ByVal syncSession As SyncSession) As SyncContext Implements ILocalDataCache1SyncContract.GetChanges
            Return Me._serverSyncProvider.GetChanges(groupMetadata, syncSession)
        End Function
       
        <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
        Public Overridable Function GetSchema(ByVal tableNames As Collection(Of String), ByVal syncSession As SyncSession) As SyncSchema Implements ILocalDataCache1SyncContract.GetSchema
            Return Me._serverSyncProvider.GetSchema(tableNames, syncSession)
        End Function
       
        <System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Public Overridable Function GetServerInfo(ByVal syncSession As SyncSession) As SyncServerInfo Implements ILocalDataCache1SyncContract.GetServerInfo
            Return Me._serverSyncProvider.GetServerInfo(syncSession)
        End Function
    End Class

    <ServiceContractAttribute()>  _
    Public Interface ILocalDataCache1SyncContract
       
        <OperationContract()>  _
        Function ApplyChanges(ByVal groupMetadata As SyncGroupMetadata, ByVal dataSet As DataSet, ByVal syncSession As SyncSession) As SyncContext
       
        <OperationContract()>  _
        Function GetChanges(ByVal groupMetadata As SyncGroupMetadata, ByVal syncSession As SyncSession) As SyncContext
       
        <OperationContract()>  _
        Function GetSchema(ByVal tableNames As Collection(Of String), ByVal syncSession As SyncSession) As SyncSchema
       
        <OperationContract()>  _
        Function GetServerInfo(ByVal syncSession As SyncSession) As SyncServerInfo
    End Interface

     

    Where do I add the code?

    How to get access to the syncAgent?

    I tried to do this in

    Partial Public Class LocalDataCache1ServerSyncProvider
        Private Sub OnInitialized()
         ' tried it here.....
        End Sub
    End Class

    thanks

    Christian

     

     

    Monday, August 15, 2011 1:40 PM
  • as i have mentioned, you should try altering the commands in the GetChanges Implemenation:

     <System.Diagnostics.DebuggerNonUserCodeAttribute()>  _
        Public Overridable Function GetChanges(ByVal groupMetadata As SyncGroupMetadata, ByVal syncSession As SyncSession) As SyncContext Implements ILocalDataCache1SyncContract.GetChanges

         Alter the commands here before the call below


            Return Me._serverSyncProvider.GetChanges(groupMetadata, syncSession)
        End Function

    The SyncAgent only exists in the client so you cant access it from the WCF service side. as i have mentioned, i dont think you have to do anything special with the SyncAgent. just pass the filter parameter value and that value will be in the SyncSession.

    so just try altering the commands as stated above.

    • Marked as answer by chl-h Wednesday, August 17, 2011 9:03 AM
    Tuesday, August 16, 2011 2:01 AM
  • Hi JuneT,

    thanks again for pointing that out. I came up  with this code (Table: MyTable, column to filter: TabKey (NChar)):

    <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
        Public Overridable Function GetChanges(ByVal groupMetadata As SyncGroupMetadata, ByVal syncSession As SyncSession) As SyncContext Implements ILocalDataCache1SyncContract.GetChanges

            'let’s retrieve our sync provider so we can get access to the adapter
            Dim remoteProvider As LocalDataCache1ServerSyncProvider = Me._serverSyncProvider
            'let’s build our filter
            'filter on the change table instead of the table being synched
            Dim myFilter As String = " (CT.TabKey=@TabKey) AND "
            'let’s take the command objects from the adapter
            Dim selectIncrementalInsertsCommand As IDbCommand = remoteProvider.MyTableSyncAdapter.SelectIncrementalInsertsCommand
            Dim selectIncrementalUpdatesCommand As IDbCommand = remoteProvider.MyTableSyncAdapter.SelectIncrementalUpdatesCommand
            Dim selectIncrementalDeletesCommand As IDbCommand = remoteProvider.MyTableSyncAdapter.SelectIncrementalDeletesCommand

            'there are two select statements in the generated command for SelectingInserts
            'the first is for @sync_initialized = 0, the other one for subsequent syncs
            'so we need to insert our filter on both statements
            selectIncrementalInsertsCommand.CommandText = selectIncrementalInsertsCommand.CommandText.Insert(selectIncrementalInsertsCommand.CommandText.IndexOf("WHERE") + 5, myFilter)
            selectIncrementalInsertsCommand.CommandText = selectIncrementalInsertsCommand.CommandText.Insert(selectIncrementalInsertsCommand.CommandText.LastIndexOf("WHERE") + 5, myFilter)

            'let’s add the filter for the select deletes and select updates command
            selectIncrementalDeletesCommand.CommandText = selectIncrementalDeletesCommand.CommandText.Insert(selectIncrementalDeletesCommand.CommandText.IndexOf("WHERE") + 5, myFilter)
            selectIncrementalUpdatesCommand.CommandText = selectIncrementalUpdatesCommand.CommandText.Insert(selectIncrementalUpdatesCommand.CommandText.IndexOf("WHERE") + 5, myFilter)

            'let’s add our filter parameter
            selectIncrementalInsertsCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TabKey", System.Data.SqlDbType.NChar))
            selectIncrementalDeletesCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TabKey", System.Data.SqlDbType.NChar))
            selectIncrementalUpdatesCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TabKey", System.Data.SqlDbType.NChar))

            'now, let’s replace the designer generated command with our new command with filter
            remoteProvider.MyTableSyncAdapter.SelectIncrementalInsertsCommand = selectIncrementalInsertsCommand
            remoteProvider.MyTableSyncAdapter.SelectIncrementalDeletesCommand = selectIncrementalDeletesCommand
            remoteProvider.MyTableSyncAdapter.SelectIncrementalUpdatesCommand = selectIncrementalUpdatesCommand

            Return Me._serverSyncProvider.GetChanges(groupMetadata, syncSession)
        End Function
       
       
    and on the client side I added the filter like:

         syncAgent.Configuration.SyncParameters.Add(New SyncParameter("@TabKey", "someValue"))
         
            
    This changed the behaviour of the application in the following way:

    changes on the server are only downloaded to client for records that match the criteria (Great!)

    on the client side there are records that does not match the criteria (old records, from before I added the filter) changes at this records are still uploaded, but I guess this records should not be there in the first place.
    syncdirection for this table is Bidirectional

    Thanks
    Christian
    Tuesday, August 16, 2011 4:08 PM
  • the client has no filters and any change on the client side will be picked up for upload. i suggest you start with a blank client db instead.
    Tuesday, August 16, 2011 11:45 PM