none
Sync Framework v2.1 Filtering (UploadandDownload direction) problem RRS feed

  • Question

  • Hi,

    I am not sure if what I am trying to do is achievable with the Sync Framework filter clause. 

    I have a resources table that contains 8 different clients.  I am trying to setup more than one scope (static filtering for now) that will allow users to Sync the resource tables bidirectionally.  I create both scopes using filterclause statements and it appears to download the appropriate data.  However it keeps thinking it has to upload the data on the subsequent sync. 

    Can we use the Sync Framework to sync two scopes (one for resource 1, and the other for resource 2) to a single table in our client database and do bidirectional syncing?  All the samples I found only since a single filtered scope.  Other samples show sync'ing to two different databases (db1.sdf and then db2.sdf), none have both bidirectional sync scopes going to a single database (using the same table).

    Do I need to write custom insert/delete/etc commands to achieve this?

     

    Wednesday, September 15, 2010 11:14 PM

Answers

  • yes, that's the problem, the updates of the other scope is throwing off the other scope.

    i dont think you  need to subscribe to the ChangesSelected on the remote provider, since its only the client (am assuming as your local provider) that you want to filter and you server already has the correct filters on the scope definition itself.

    the SQLExpress uses SqlSyncScopeProvisioning that can take a filter.

     

    • Marked as answer by sbhuller Thursday, September 16, 2010 10:35 PM
    Thursday, September 16, 2010 4:32 PM
    Moderator

All replies

  • I forgot to mention that I am using SQL Server 2008 and SQL CE.  Does the problem exist because I am using SQL CE and it is unable to create stored procedures/triggers (which would contain the hard-coded filter parameter)?

     

    Wednesday, September 15, 2010 11:22 PM
  • are there overlaps in the filter clauses of your scope? any data that is synchronized between a pair of client and server  can belong only to one scope.

    afaik, the sync knowledge is maintained at the scope level and not at the table level.

    In your case, if you have overlapping filters, the changes downloaded by the first scope and applied to the client will be detected as changes to be uploaded by the second scope. When the second scope is sync, it will look for changes since the last sync and will see that there were changes as a result of the first scope applying changes.

    e.g.,  first scope filter: State=WA ,  second scope filter: State=WA OR State=CA.

    When you sync the first scope, it will bring down rows for WA. When you sync second scope, it will detect the downloaded rows from the first scope and will upload them.

    Thursday, September 16, 2010 1:26 AM
    Moderator
  • June, thanks.  That is exactly what I thought was happening.  What can you recommend as a solution to this type of sync issue?  I would think alot of people run into this in alot of line of business applications.  Thanks for your response it saved me from trying to test my application with SQL Server Express.

    I can image having a large database with alot of states and a single user needs only two or three of them.  How can they effectively sync the data? Should they use seperate tables, or can they use some sort of table mapping to keep them different/isolated? 

     

    Thursday, September 16, 2010 3:39 AM
  • can you elaborate more on the overlapping filter that you have? care to share the scenario?

    if a user needs more than one state, can you not do an IN clause for your filter? ie., State IN ('WA','CA','NY')

    note though that not only does Sync Fx does not support overlapping scopes, it also doesnt support rows going out of scope. for example in the above scenario, if the filter is WA, CA and NY and the client has downloaded it already, if you take out CA from teh filter, the client will not automatically remove all previously downloaded CA rows.

    Thursday, September 16, 2010 4:15 AM
    Moderator
  • June, I re-read your initial comment and we do not have any overlapping records in each scope.  I have two two tables, resource and resourceIdentifier

     

    Resource = CompanyA, CompanyB

    Resource Identifier = Person 1 in Company A, Person 2 in Company A, Person 3 in CompanyB

    I create a GUID for each entry in Resource and for reach entry in Resource Identifier.  I also have a column in Resource Identifier which I use to store the Resource GUID.

    I create a scope for CompanyA, and another scope for CompanyB.  However when I sync them I notice that the SYNCs always say updating for each of the records after the first SYNC.  I notice that the sync for CompanyA goes fast (bulk), but the second scope sync goes slow 1,2,3, (insert, selectrow, updatemetadata, etc for each record).

    That is why I was wonderingif I could have two scopes (using different static filters that do not overlap) store data to a single table in SQL CE.  I cannot see where SQL CE stores the static filterclause to use in selecting changes locally to upload.

     

    Thursday, September 16, 2010 4:58 AM
  • if you're seeing selectrow being sent to the server for the second sync, i suspect it's detecting conflicts.

    can you confirm how many selectchanges sp do you have against the table?

    are you using Sync v2.1? when you provisioned the second scope, did you specify SetCreateProceduresForAdditionalScopeDefault?

    if your SQL CE db was provisioned based on an existing scope from the server (using GetDescriptionForScope), the filters are not created on the client.

    Thursday, September 16, 2010 5:51 AM
    Moderator
  • Here are my functions for creating the static filters:

     

        private void Provision_Server_Static_Filter(SqlConnection serverConn, string scopeName, Guid Id, string[] tableNames, bool isFiltered, bool isClient, bool isAdditional)
        {
          try
          {
            DbSyncScopeDescription scope = new DbSyncScopeDescription("scope_" + scopeName);
            foreach (string tableName in tableNames)
            {
              DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, serverConn);
              scope.Tables.Add(tableDesc);
            }
    
            SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(serverConn, scope);
    
            serverConfig.CommandTimeout = 1800;
            serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
            if (isFiltered)
            {
              foreach (string tableName in tableNames)
              {
                if (isClient)
                {
                    serverConfig.Tables[tableName].AddFilterColumn("SyncClientsId");
                    serverConfig.Tables[tableName].FilterClause = "[side].[SyncClientsId] = '" + Id.ToString() + "'";
                }
                else
                {
                    serverConfig.Tables[tableName].AddFilterColumn("SyncProjectsId");
                    serverConfig.Tables[tableName].FilterClause = "[side].[SyncProjectsId] = '" + Id.ToString() + "'";
                }
              }
            }
    
            if (isAdditional)
            {
              serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
              serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip); //skip creating the stored procedures 
              serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip); //skip creating the tracking table 
              serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Skip);//skip creating the triggers 
            }
    
            serverConfig.Apply(); // Configure the scope and change-tracking infrastructure.
    
          }
          catch (Exception ex)
          {
          }
        }
    
    

    Here is my method for provisioning the client:

        private bool ProvisionScopeAtClient(SqlConnection serverConn, SqlCeConnection clientSqlConn, string ScopeName)
        {
          try
          {
            if (!DatabaseHelper.CheckClienScopeExists("scope_" + ScopeName))
            {
              //make sure all scopes exist, if not then create it 
              DbSyncScopeDescription clientSqlCeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("scope_" + ScopeName, null, /*"Sync"*/ null, serverConn);
              SqlCeSyncScopeProvisioning clientSqlCeConfig = new SqlCeSyncScopeProvisioning(clientSqlConn, clientSqlCeDesc);
    
              if (!DatabaseHelper.CheckIfTableExistsInCE(clientSqlConn, clientSqlCeDesc.Tables[0].LocalName.TrimEnd(']').TrimStart('[')))
                clientSqlCeConfig.SetCreateTableDefault(DbSyncCreationOption.Create);
              else
                clientSqlCeConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
              clientSqlCeConfig.Apply();
              return true;
            }
          }
          catch (Exception)
          {
          }
    
          return false;
        }
    
    
    Do you see anything wrong with this? 
    
    Thursday, September 16, 2010 6:14 AM
  • June, I am not using SP1 (Sync v1.0), if that is what you are referring to.  I am using Sync Framework v2.1.

    Can you clarify what you meant by "if your SQL CE db was provisioned based on an existing scope from the server (using GetDescriptionForScope), the filters are not created on the client."?  If you look at my second method with regards to how I provision the SQL CE database, this is in fact what I am doing 'GetDescriptionForScope' from the static filter scope I created on the server.  Is this not the right way to do this ?

    Thanks!

    Thursday, September 16, 2010 6:24 AM
  • ok, i think i get it now.

    the filter is not applied on the client. the GetDescriptionForScope doesnt include the filters.

    so when you're client downloads changes for scope1, these changes are detected by scope2 and is being uploaded since there is no filter.

    try this (i remember testing this before, but am not sure what was the outcome :) )

    after making a call to GetDescriptionForScope, add the filters to your client config:

    ie.,

    clientSqlCeConfig.Tables[tableName].AddFilterColumn("SyncClientsId");
    clientSqlCeConfig.Tables[tableName].FilterClause = "[side].[SyncClientsId] = '" + Id.ToString() + "'";

     

     

    Thursday, September 16, 2010 6:28 AM
    Moderator
  • June,

     I don't see those methods/properties for the SQL CE Table object "SqlCeSyncTableProvisioning" or even "DbSyncTableProvisioning".  Am I missing something?

     

     

    Thursday, September 16, 2010 6:58 AM
  • I meant 'DbSyncTableDescription' instead of 'DbSyncTableProvisioning', I cannot see how to add the filter column/clause to my SQL CE database.
    Thursday, September 16, 2010 7:00 AM
  • sorry, scratch that, i just checked the sample i did, the client is SQL Server too.

    adding filter is not available on the SqlCe provisioning.

    btw, any particular reason why you can't have multiple SQL CE Dbs instead on the client?

    Thursday, September 16, 2010 7:19 AM
    Moderator
  • Yeah, so my original assumption was correct.  This is not possible under SQL CE, instead I need to go with SQL Server Express for my clients.

    Maybe someone else will chime in on how they achieved this, I find it hard to believe I am only one in this scenerio....

    Thursday, September 16, 2010 7:23 AM
  • Not entirely sure if it would work, but you can try intercepting the changes in the ChangesSelected event of the client provider. You can intercept the dataset and filter it. (assuming you really have to work on a single db, same table scenario.)
    Thursday, September 16, 2010 7:37 AM
    Moderator
  • I tried that and it didn't work either.  I think this is just a lack of functionality in the Sync Framework.

    Thursday, September 16, 2010 3:04 PM
  • how and where did you do the filtering on the dataset?
    Thursday, September 16, 2010 3:12 PM
    Moderator
  • I just did a quick test using the following code (I set the event on the localprovider changes selected), is this wrong?

     

        void localProvider_ChangesSelected(object sender, DbChangesSelectedEventArgs e)
        {
          if (FilterId.HasValue)
          {
            SqlCeSyncProvider sqlCESyncProvider = sender as SqlCeSyncProvider;
    
            if (sqlCESyncProvider.ScopeName.Equals("scope_resone") ||
              sqlCESyncProvider.ScopeName.Equals("scope_restwo"))
            {
    
    
              dataRowCol = e.Context.DataSet.Tables["dbo.Resources"].Rows;
              for (int i = 0; i < dataRowCol.Count; i++)
              {
                Guid Id = (Guid)dataRowCol[i]["SyncClientsId"];
                if (Id != (Guid)FilterId)
                {
                  dataRowCol[i].Delete();
                  i--;
                }
              }
    
              dataRowCol = e.Context.DataSet.Tables["dbo.ResourceIdentifiers"].Rows;
              for (int i = 0; i < dataRowCol.Count; i++)
              {
                Guid Id = (Guid)dataRowCol[i]["SyncClientsId"];
                if (Id != (Guid)FilterId)
                {
                  dataRowCol[i].Delete();
                  i--;
                }
              }
            }
          }
        }
    
    
    Should I try the looking at the even on the remote changesselected event?
    
    Have you done this on SQL Server? I would like to know that it can be on on SQL Server Express before I try it.
    
    Thursday, September 16, 2010 3:18 PM
  • actually, dont call delete. it will be marked as deleted in the dataset and when it reaches the server, the row will be deleted. try using Remove instead.
    Thursday, September 16, 2010 3:34 PM
    Moderator
  • I changed the code and added it to both the ChangesSelected on the remote and local provider. It appears to work on the initial sync, but if I try to sync a second time, it does the same thing where it tries to re-insert the record by calling 'selectrow','update','updatemetadata',then 'insert'.
    
            if (sqlCESyncProvider.ScopeName.Equals("scope_resone") ||
              sqlCESyncProvider.ScopeName.Equals("scope_restwo"))
            {
              for (int i = 0; i < e.Context.DataSet.Tables.Count; i++)
              {
                DataTable table = e.Context.DataSet.Tables[i];
    
                for (int j = 0; j < table.Rows.Count; j++)
                {
                  Guid Id = (Guid)table.Rows[j]["SyncClientsId"];
                  if (Id != (Guid)FilterId)
                  {
                    table.Rows.RemoveAt(j);
                    j--;
                  }
                }
              }
            }
    
    
    Thursday, September 16, 2010 4:02 PM
  • I think the problem may lie in some of the metadata being updated during each sync (which throws the other sync out). 

    Is what I am trying to do possible with SQL Server Express?

    Thursday, September 16, 2010 4:11 PM
  • yes, that's the problem, the updates of the other scope is throwing off the other scope.

    i dont think you  need to subscribe to the ChangesSelected on the remote provider, since its only the client (am assuming as your local provider) that you want to filter and you server already has the correct filters on the scope definition itself.

    the SQLExpress uses SqlSyncScopeProvisioning that can take a filter.

     

    • Marked as answer by sbhuller Thursday, September 16, 2010 10:35 PM
    Thursday, September 16, 2010 4:32 PM
    Moderator
  • June,  Thanks for your help. When we get a chance we will look into switching to SQL Express.

    Thursday, September 16, 2010 10:35 PM