none
Filter not being applied - local data cache N-Tier WCF Synchronization RRS feed

  • Question

  • Hi

    I have tried to add filtering to the synchronization code generated by the Local Database Cache designer in Visual Studio, with a separate service using WCF server - side.

    I've used the following links for advice and help, but now I'm stuck, can anyone help me?

    http://social.microsoft.com/Forums/is/syncdevdiscussions/thread/9581dd14-41e1-4376-a2a5-e3d15cad3fe7

    http://jtabadero.wordpress.com/2010/03/17/adding-filter-to-local-database-cache-generated-sync/

    http://social.microsoft.com/Forums/uk/syncdevdiscussions/thread/0336184c-fdc1-48bb-9a14-1d523641a21b

    What I've done is added a separate method to the service - addFilter - which invokes the a sub on the server's LocalDataCacheServerSyncProvider. Then I add a new Sync Parameter to the syncAgent, before doing the sync:


    Dim r As WTSLocalDataCacheSyncContractClient = syncAgent.ContractClient r.AddFilter()

                syncAgent.Configuration.SyncParameters.Add(New SyncParameter("@ArbeitsStationID", modMain.ArbeitsstationID)) syncStats = syncAgent.Synchronize()

    Where "r " is a public property I created in my LocalDataCacheSyncAgent to have easy access to the remote proxy.

    Through logging I was able to determine that "AddFilter" is executed on the server, here's the sub:

            

        Public Sub AddFilter()

    m_objLogger.LogMessage("Adding Filter") Dim myFilter As String = " (CT.ArbeitsStationID=@ArbeitsStationID) AND " 'let’s take the command objects from the adapter Dim selectIncrementalInsertsCommand As IDbCommand = StempelSyncAdapter.SelectIncrementalInsertsCommand Dim selectIncrementalUpdatesCommand As IDbCommand = StempelSyncAdapter.SelectIncrementalUpdatesCommand Dim selectIncrementalDeletesCommand As IDbCommand = StempelSyncAdapter.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("@ArbeitsStationID", System.Data.SqlDbType.Int)) selectIncrementalDeletesCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ArbeitsStationID", System.Data.SqlDbType.Int)) selectIncrementalUpdatesCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ArbeitsStationID", System.Data.SqlDbType.Int)) 'now, let’s replace the designer generated command with our new command with filter StempelSyncAdapter.SelectIncrementalInsertsCommand = selectIncrementalInsertsCommand StempelSyncAdapter.SelectIncrementalDeletesCommand = selectIncrementalDeletesCommand StempelSyncAdapter.SelectIncrementalUpdatesCommand = selectIncrementalUpdatesCommand 'logging m_objLogger.LogMessage("Insert Command: " & StempelSyncAdapter.SelectIncrementalInsertsCommand.CommandText.ToString) m_objLogger.LogMessage("Delete Command: " & StempelSyncAdapter.SelectIncrementalDeletesCommand.CommandText.ToString) m_objLogger.LogMessage("Update Command: " & StempelSyncAdapter.SelectIncrementalUpdatesCommand.CommandText.ToString) m_objLogger.LogMessage("End Adding Filter")

    end sub

    As you can see, I have a custom logger which I use to monitor what's going on. The output seems as expected:

    08.05.2012 09:54:14 | Adding Filter 08.05.2012 09:54:14 | Insert Command: IF @sync_initialized = 0 SELECT dbo.Stempel.[StempelID], [UserID],

    [ArbeitsStationID], [DatumUhrzeit], [Eingang], [Ausgang], [StempelClusterID] FROM dbo.Stempel

    LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.Stempel, @sync_last_received_anchor) CT ON

    CT.[StempelID] = dbo.Stempel.[StempelID] WHERE (CT.ArbeitsStationID=@ArbeitsStationID) AND

    (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) ELSE

    BEGIN SELECT dbo.Stempel.[StempelID], [UserID], [ArbeitsStationID], [DatumUhrzeit], [Eingang], [Ausgang],

    [StempelClusterID] FROM dbo.Stempel JOIN CHANGETABLE(CHANGES dbo.Stempel, @sync_last_received_anchor)

    CT ON CT.[StempelID] = dbo.Stempel.[StempelID] WHERE (CT.ArbeitsStationID=@ArbeitsStationID) AND

    (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor AND

    (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));

    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Stempel')) > @sync_last_received_anchor

    RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''.

    To recover from this error, the client must reinitialize its local database and try again',16,3,

    N'dbo.Stempel') END 08.05.2012 09:54:14 | End Adding Filter

    With the Delete and Update Command Texts being similar (i.e. the WHERE clause including the @ArbeitsStationID clause), I just edited them out for clarity sake.

    However, when I look at my trace.config text file, these are the actual SQL statements that are being used:

    INFO   , w3wp, 6, 05/08/2012 07:54:23:432,    ----- Enumerating Updates for Table Stempel -----
    VERBOSE, w3wp, 6, 05/08/2012 07:54:23:432, Using Command: IF @sync_initialized > 0  BEGIN SELECT dbo.Stempel.[StempelID],    [UserID], [ArbeitsStationID], [DatumUhrzeit], [Eingang], [Ausgang], [StempelClusterID] FROM dbo.Stempel JOIN CHANGETABLE(CHANGES dbo.Stempel, @sync_last_received_anchor) CT ON CT.[StempelID] = dbo.Stempel.[StempelID] WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Stempel')) > @sync_last_received_anchor RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'dbo.Stempel')  END

    As you can see, the WHERE clause with @ArbeitsStationID is not to be seen.

    So this is the situation: I add the filter on the server, but it is not being executed, and all records are being downloaded instead of being filtered by "ArbeitsStationID".

    Please help me figure out what's going on. I'm quite new to syncing, so if there's any info I left out, please let me know!

    Many thanks

    Winks






    • Edited by CWinks Tuesday, May 8, 2012 9:47 AM
    Tuesday, May 8, 2012 8:24 AM

Answers

  • this is your constructor...you can add the filter clause here instead of having the client invoke the setfilter explicitly...

      Public Sub New()
           
    MyBase.New()
           
    Me._serverSyncProvider = New WTSLocalDataCacheServerSyncProvider

        End Sub

    here's a rough sample equivalent in C#, just translate it...

            public LocalDataCache1SyncService()
            {
                this._serverSyncProvider = new LocalDataCache1ServerSyncProvider();
                IDbCommand selectIncrementalInsertsCommand = this._serverSyncProvider.OfflineProviderTableSyncAdapter.SelectIncrementalInsertsCommand;
                IDbCommand selectIncrementalUpdatesCommand = this._serverSyncProvider.OfflineProviderTableSyncAdapter.SelectIncrementalUpdatesCommand;
                IDbCommand selectIncrementalDeletesCommand = this._serverSyncProvider.OfflineProviderTableSyncAdapter.SelectIncrementalDeletesCommand;
                //set the filter clause below....
            }

    • Marked as answer by CWinks Tuesday, May 8, 2012 4:54 PM
    Tuesday, May 8, 2012 12:56 PM
    Moderator
  • Yes, I figured it out!

    Your answer got me a lot closer, but I got an error stating "Column name ArbeitsStationID could not be found".

    So I changed "CT.ArbeitsStationID" to "dbo.Stempel.ArbeitsStationID" but I got the following error:

    ERROR  , w3wp, 6, 05/08/2012 16:38:23:870, Caught exception: System.Data.SqlClient.SqlException: The multi-part identifier "dbo.Stempel.ArbeitsStationID" could not be bound.

    I then remembered reading on the MSDN site on filtering that you should not sync deletes with a filter, so I took out all the statements dealing with "selectIncrementalDeletes" and then it worked! Finally!

    Question though - out of curiosity and for me to learn - How come setting the filter in the constructor worked, but setting it right after in a different method call didn't?

    Thanks again for your help, JuneT!

    • Marked as answer by CWinks Tuesday, May 8, 2012 4:54 PM
    Tuesday, May 8, 2012 4:54 PM

All replies

  • can you post your entire service class?

    also, does you filter clause vary from client to  client? why dont you just put the setting of the filter on the service constructor?

    Tuesday, May 8, 2012 10:44 AM
    Moderator
  • No, the filter clause does not vary from client to client (the value of the parameter does, obviously).

    What do you mean "put the setting of the filter on the service constructor"? I don't know what you're referring to. Can you give a specific example?

    the service class looks like this:

    Partial Public Class WTSLocalDataCacheSyncService
    
        Inherits Object
        Implements IWTSLocalDataCacheSyncContract
    
        Private _serverSyncProvider As WTSLocalDataCacheServerSyncProvider
        Public Sub New()
            MyBase.New()
            Me._serverSyncProvider = New WTSLocalDataCacheServerSyncProvider
        End Sub
    
        <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
        Public Overridable Function ApplyChanges(ByVal groupMetadata As SyncGroupMetadata, ByVal dataSet As DataSet, ByVal syncSession As SyncSession) As SyncContext Implements IWTSLocalDataCacheSyncContract.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 IWTSLocalDataCacheSyncContract.GetChanges
            'Return Me._serverSyncProvider.GetChanges(groupMetadata, syncSession)
            Try
                Dim tmp = Me._serverSyncProvider.GetChanges(groupMetadata, syncSession)
                Return tmp
    
            Catch se As SyncException
                If se.ErrorNumber = SyncErrorNumber.StoreException Then
                    Dim sqlExcept As SqlException = TryCast(se.InnerException, SqlException)
                    If sqlExcept IsNot Nothing AndAlso sqlExcept.[Class] = 16 AndAlso sqlExcept.State = 3 Then
                        Throw New ApplicationException("ChangeTrackingCleanedUp", se)
                    End If
                End If
                Throw se
            Catch ex As Exception
    
                Throw ex
            End Try
        End Function
    
        <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
        Public Overridable Function GetSchema(ByVal tableNames As Collection(Of String), ByVal syncSession As SyncSession) As SyncSchema Implements IWTSLocalDataCacheSyncContract.GetSchema
            Return Me._serverSyncProvider.GetSchema(tableNames, syncSession)
        End Function
    
        <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
        Public Overridable Function GetServerInfo(ByVal syncSession As SyncSession) As SyncServerInfo Implements IWTSLocalDataCacheSyncContract.GetServerInfo
            Return Me._serverSyncProvider.GetServerInfo(syncSession)
        End Function
    
        Public Function GetSyncConflictCount() As Integer Implements IWTSLocalDataCacheSyncContract.GetSyncConflictCount
            Return Me._serverSyncProvider.getConflictCount()
        End Function
        Public Function EndSyncLogFile() As Byte() Implements IWTSLocalDataCacheSyncContract.EndSyncLogFile
            Return (Me._serverSyncProvider.EndSyncLogFile())
        End Function
    
        Public Function TestDefaultConnectionString() As String Implements IWTSLocalDataCacheSyncContract.TestDefaultConnectionString
            Try
    
                Dim conn As New SqlClient.SqlConnection(My.Settings.ServerWTS_TestConnectionString)
                conn.Open()
                TestDefaultConnectionString = My.Settings.ServerWTS_TestConnectionString
                conn.Close()
            Catch ex As Exception
                TestDefaultConnectionString = ex.Message
                If Not IsNothing(ex.InnerException) Then
                    TestDefaultConnectionString += ex.InnerException.Message
                End If
            End Try
    
        End Function
    
        Public Function TestService() As String Implements IWTSLocalDataCacheSyncContract.TestService
            Return "Test Service at: " & Now()
        End Function
    
        Public Sub AddFilter() Implements IWTSLocalDataCacheSyncContract.AddFilter
            Me._serverSyncProvider.AddFilter()
        End Sub
    End Class
    
    <ServiceContractAttribute()> _
    Public Interface IWTSLocalDataCacheSyncContract
    
        <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
    
        <OperationContract()> _
        Function GetSyncConflictCount() As Integer
    
        <OperationContract()> _
        Function EndSyncLogFile() As Byte()
    
        <OperationContract()> _
        Function TestDefaultConnectionString() As String
    
        <OperationContract()> _
        Function TestService() As String
    
        <OperationContract()> _
        Sub AddFilter()
    
    End Interface
    

    Tuesday, May 8, 2012 12:21 PM
  • this is your constructor...you can add the filter clause here instead of having the client invoke the setfilter explicitly...

      Public Sub New()
           
    MyBase.New()
           
    Me._serverSyncProvider = New WTSLocalDataCacheServerSyncProvider

        End Sub

    here's a rough sample equivalent in C#, just translate it...

            public LocalDataCache1SyncService()
            {
                this._serverSyncProvider = new LocalDataCache1ServerSyncProvider();
                IDbCommand selectIncrementalInsertsCommand = this._serverSyncProvider.OfflineProviderTableSyncAdapter.SelectIncrementalInsertsCommand;
                IDbCommand selectIncrementalUpdatesCommand = this._serverSyncProvider.OfflineProviderTableSyncAdapter.SelectIncrementalUpdatesCommand;
                IDbCommand selectIncrementalDeletesCommand = this._serverSyncProvider.OfflineProviderTableSyncAdapter.SelectIncrementalDeletesCommand;
                //set the filter clause below....
            }

    • Marked as answer by CWinks Tuesday, May 8, 2012 4:54 PM
    Tuesday, May 8, 2012 12:56 PM
    Moderator
  • Yes, I figured it out!

    Your answer got me a lot closer, but I got an error stating "Column name ArbeitsStationID could not be found".

    So I changed "CT.ArbeitsStationID" to "dbo.Stempel.ArbeitsStationID" but I got the following error:

    ERROR  , w3wp, 6, 05/08/2012 16:38:23:870, Caught exception: System.Data.SqlClient.SqlException: The multi-part identifier "dbo.Stempel.ArbeitsStationID" could not be bound.

    I then remembered reading on the MSDN site on filtering that you should not sync deletes with a filter, so I took out all the statements dealing with "selectIncrementalDeletes" and then it worked! Finally!

    Question though - out of curiosity and for me to learn - How come setting the filter in the constructor worked, but setting it right after in a different method call didn't?

    Thanks again for your help, JuneT!

    • Marked as answer by CWinks Tuesday, May 8, 2012 4:54 PM
    Tuesday, May 8, 2012 4:54 PM