Dynamic Filtering in Sync Framework 2.1 / Filter by ANY instead of EQUALS

All replies

• 

  

  0

  Sign in to vote

  no, its not, i attempted it in my scenario, where i was looking at a Updatetime field in my table, i only wanted to sync rows that where x years old. the initial sync worked fine, but if an entry was updated, the result was its deletion from both the client and server... 

  Tuesday, October 5, 2010 4:56 PM
• 

  

  0

  Sign in to vote

  When the entry was updated, was it still in the sync scope ("rows that were x years old")? If not, this isn't supported scenario and no thorough testing was done.

  ---

  Ann Tang

  Tuesday, October 5, 2010 10:51 PM
• 

  

  0

  Sign in to vote

  A given sync scope has to be provisioned before sync session starts and passing in parameter from client during sycn time isn't supported scenario.

  In order to filter by ANY, you could define multiple filter parameters for the same filter column and use OR in the filter clause of the template.

  Here is some sample code:

  SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);

   

  serverTemplate.Tables["Sales.Customer"].AddFilterColumn("CustomerType");

  serverTemplate.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = @customertype or [side].[CustomerType] = @customertype2";

  SqlParameter param = new SqlParameter("@customertype", SqlDbType.NVarChar, 100);

  SqlParameter param2 = new SqlParameter("@customertype2", SqlDbType.NVarChar, 500);

   

  serverTemplate.Tables["Sales.Customer"].FilterParameters.Add(param);

  serverTemplate.Tables["Sales.Customer"].FilterParameters.Add(params);

   

  Thanks,

   

  ---

  Ann Tang

  • Proposed as answer by Ann Tang [MSFT]Microsoft employee Tuesday, October 5, 2010 11:15 PM

  Tuesday, October 5, 2010 11:15 PM
• 

  

  0

  Sign in to vote

  So what you are telling me is that parameter-based filters are not really supported then, right?  A template that supports parameters is different than a sync filter that supports parameters.

  Look, here is my own parameter-based template.

  String strMyParam = "value";
  String template = String.Format("[side].[CustomerType] = '{0}'",strMyParam);
  serverProvision.Tables["Sales"].FilterClause = template;
  

  Done, what do I need your fancy template engine for?  If I have to create a scope with the parameters hard coded into them then SF's fancy parameter-based filter is not worth any more than the above lines of code.

  Am I wrong?  Somebody please chime in and put me in my place, am I missing something here? 
  

  
  

  Wednesday, October 6, 2010 4:53 AM
• 

  

  0

  Sign in to vote

  I believe I have discovered a solution to this problem.  I do not have a working solution just yet, but I'm hopeful.  I believe dynamic filtering can be done by creating a custom sync provider that inherits from DbSyncProvider.  Pasted below is a snip of code.  Obviously there is a lot more to this, but I believe this is a step in the right direction.  This is a custom provider that will let me filter content by a UserId.  The trick is to avoid manually hard-coding the custom provider.  Within the provider an adapter must be created for each table in the scope.  In my case there are about a hundred tables spread across 10 scopes.  Manually hard-coding this would suck, not to mention the maintenance hassle when changes are made to the data model.  So what I'm trying to do here is auto-generate the adapters based on the provider scope configuration.

  I'm having some problems with the below code though.  The SqlSyncProviderAdapterConfiguration.BuildAdapter() function does not work from what I can tell.  I am passing it a good connection to the database server and it does nothing.  Watching server profiler I can see that it doesn't even connect to the server when BuildAdapter() is executed.  For all of the SQL commands an exception is thrown.  For example, "DeleteCommand = 'adapter.DeleteCommand' threw an exception of type 'System.InvalidOperationException'"

  Help anyone?

  public class MyProvider : DbSyncProvider
  

      {
          private string m_strConfigData = string.Empty;
  
          public MyProvider(string scopeName, SqlConnection conn, Guid UserId)
          {
              SqlSyncProvider sqlProvider = new SqlSyncProvider(scopeName, conn);
             
              //DbSyncProvider sampleDbProvider = new DbSyncProvider();
              this.Connection = conn;
              this.ScopeName = scopeName;
  
              //get scope data
              SqlConnection sqlconn = new SqlConnection(this.Connection.ConnectionString);
              sqlconn.Open();
              SqlCommand cmd = new SqlCommand(String.Format("SELECT sc.* FROM scope_config sc JOIN scope_info si ON sc.config_id=si.scope_config_id WHERE si.sync_scope_name='{0}'", ScopeName), sqlconn);
              SqlDataReader reader = cmd.ExecuteReader();
              if (reader.Read() == true)
              {
                  m_strConfigData = reader["config_data"].ToString();
              }
             
              //parse config data
              StringReader txtReader = new StringReader(m_strConfigData);
              XmlSerializer serializer = new XmlSerializer(typeof(SqlSyncProviderScopeConfiguration));
              SqlSyncProviderScopeConfiguration psc = (SqlSyncProviderScopeConfiguration)serializer.Deserialize(txtReader);
              txtReader.Dispose();
  
              foreach(SqlSyncProviderAdapterConfiguration ac in psc.AdapterConfigurations)
              {
                  DbSyncAdapter adapter = ac.BuildAdapter(new SqlConnection(this.Connection.ConnectionString));

  //add code to add filter paramters

  //adapter.SelectIncrementalChangeCommand.Parameters.Add
  
                  this.SyncAdapters.Add(adapter);
              }

   

  //add code to handle SelectNewTimestampCommand, SelectScopeInfoCommand, UpdateScopeInfoCommand, and SelectTableMaxTimestampCommand

  }

  }

  Wednesday, October 6, 2010 10:32 AM
• 

  

  0

  Sign in to vote

  I've got it working now.  I could never get BuildAdapter() to work so I just built the queries manually.  Here is the working code for a custom provider that will support passing filter parameters from the client.  This provider will pass the @UserId parameter to the SelectIncrementalChangeCommand stored procedure.  For my purposes this is the only stored procedure that I need modified.  It filters changes coming from the server but allows anything to be uploaded to the server.  All you need to do is us this set SyncOrchestrator.RemoteProvider = new MyProvider(ScopeName, conn, UserIdGuid);

  Server-side you just need to modify the SelectIncrementalChangeCommand SP to accept the @UserId (uniqueidentifier) parameter and then do whatever you want with it.  I have a simple forms app I developed that creates the scopes for me automatically, I just added some routines to edit the SP using SMO after creating a new scope.  Works great!

  To Ann, this is the sort of solution I was looking for.  Being that you work for MSFT I'm surprised you don't know about this.

  public class MyProvider : DbSyncProvider
  

      {
          private string m_strConfigData = string.Empty;
          static string[] FilteredTables = new string[] { "[Table1]", "[Table4]", "[Table9]" };
  
          public MyProvider(string scopeName, SqlConnection conn, Guid UserId)
          {
              SqlSyncProvider sqlProvider = new SqlSyncProvider(scopeName, conn);
             
              //DbSyncProvider sampleDbProvider = new DbSyncProvider();
              this.Connection = conn;
              this.ScopeName = scopeName;
  
              //get scope data
              SqlConnection sqlconn = new SqlConnection(this.Connection.ConnectionString);
              sqlconn.Open();
              SqlCommand cmd = new SqlCommand(String.Format("SELECT sc.* FROM scope_config sc JOIN scope_info si ON sc.config_id=si.scope_config_id WHERE si.sync_scope_name='{0}'", ScopeName), sqlconn);
              SqlDataReader reader = cmd.ExecuteReader();
              if (reader.Read() == true)
              {
                  m_strConfigData = reader["config_data"].ToString();
              }
             
              //parse config data
              StringReader txtReader = new StringReader(m_strConfigData);
              XmlSerializer xmlSerializer = new XmlSerializer(typeof(SqlSyncProviderScopeConfiguration));
              SqlSyncProviderScopeConfiguration psc = (SqlSyncProviderScopeConfiguration)xmlSerializer.Deserialize(txtReader);
              txtReader.Dispose();
  
              foreach(SqlSyncProviderAdapterConfiguration ac in psc.AdapterConfigurations)
              {
                  DbSyncAdapter adapter = ac.BuildAdapter(new SqlConnection(this.Connection.ConnectionString));
  
                  //SelectIncrementalChanges
                  SqlCommand cmdSelectIncrementalChanges = new SqlCommand();
                  cmdSelectIncrementalChanges.CommandType = CommandType.StoredProcedure;
                  cmdSelectIncrementalChanges.CommandText = ac.SelectChangesProcName;
                  cmdSelectIncrementalChanges.Parameters.Add("@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int);
                  cmdSelectIncrementalChanges.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                  cmdSelectIncrementalChanges.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                  cmdSelectIncrementalChanges.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
  
                  // Pass the filter parameter value here
                  if (FilteredTables.Contains(ac.TableName))
                  {
                      cmdSelectIncrementalChanges.Parameters.Add(new SqlParameter("@UserId", UserId));
                  }
  
                  adapter.SelectIncrementalChangesCommand = cmdSelectIncrementalChanges;
  
                  //InsertCommand (finished in table columns)
                  SqlCommand cmdInsert = new SqlCommand();
                  cmdInsert.CommandType = CommandType.StoredProcedure;
                  cmdInsert.CommandText = ac.InsertProcName;
                  cmdInsert.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  
                  adapter.InsertCommand = cmdInsert;
  
                  //UpdateCommand (finished in table columns)
                  SqlCommand cmdUpdate = new SqlCommand();
                  cmdUpdate.CommandType = CommandType.StoredProcedure;
                  cmdUpdate.CommandText = ac.UpdateProcName;
                  cmdUpdate.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                  cmdUpdate.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                  cmdUpdate.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
  
                  adapter.UpdateCommand = cmdUpdate;
  
  
                  //DeleteCommand (finished in table columns)
                  SqlCommand cmdDelete = new SqlCommand();
                  cmdDelete.CommandType = CommandType.StoredProcedure;
                  cmdDelete.CommandText = ac.DeleteProcName;
                  cmdDelete.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
                  cmdDelete.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
                  cmdDelete.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
  
                  adapter.DeleteCommand = cmdDelete;
  
                  //SelectRowCommand (finished in table columns)
                  SqlCommand cmdSelectRow = new SqlCommand();
                  cmdSelectRow.CommandType = CommandType.StoredProcedure;
                  cmdSelectRow.CommandText = ac.SelectRowProcName;
                  cmdSelectRow.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                  cmdSelectRow.Parameters.Add("@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int);
  
                  adapter.SelectRowCommand = cmdSelectRow;
  
  
                  //InsertMetadataCommand (finished in table columns)
                  SqlCommand cmdInsertMetadata = new SqlCommand();
                  cmdInsertMetadata.CommandType = CommandType.StoredProcedure;
                  cmdInsertMetadata.CommandText = ac.InsertMetadataProcName;
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                  cmdInsertMetadata.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  
                  adapter.InsertMetadataCommand = cmdInsertMetadata;
  
                  //UpdateMetadataCommand (finished in table columns)
                  SqlCommand cmdUpdateMetadata = new SqlCommand();
                  cmdUpdateMetadata.CommandType = CommandType.StoredProcedure;
                  cmdUpdateMetadata.CommandText = ac.UpdateMetadataProcName;
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                  cmdUpdateMetadata.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  
                  adapter.UpdateMetadataCommand = cmdUpdateMetadata;
  
                  //DeleteMetadataCommand (finished in table columns)
                  SqlCommand cmdDeleteMetadata = new SqlCommand();
                  cmdDeleteMetadata.CommandType = CommandType.StoredProcedure;
                  cmdDeleteMetadata.CommandText = ac.DeleteMetadataProcName;
                  cmdDeleteMetadata.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                  cmdDeleteMetadata.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
                  cmdDeleteMetadata.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  
                  adapter.DeleteMetadataCommand = cmdDeleteMetadata;
  
                  //table columns
                  foreach (DbSyncColumnDescription col in ac.Columns)
                  {
                      SqlDbType type = SqlDbType.Text;
                      int size = 0;
  
                      //type
                      switch (col.Type)
                      {
                          case "uniqueidentifier":
                              type = SqlDbType.UniqueIdentifier;
                              break;
                          case "int":
                              type = SqlDbType.Int;
                              break;
                          case "nvarchar":
                              type = SqlDbType.NVarChar;
                              break;
                          case "float":
                              type = SqlDbType.Float;
                              break;
                          case "datetime":
                              type = SqlDbType.DateTime;
                              break;
                          case "bit":
                              type = SqlDbType.Bit;
                              break;
                          case "varbinary":
                              type = SqlDbType.VarBinary;
                              break;
                      }
  
                      //size
                      if (col.Size != "max")
                      {
                          size = Convert.ToInt32(col.Size);
                      }
  
                      //primary key(s)
                      if (col.IsPrimaryKey)
                      {
                          //adapter.RowIdColumns.Add(ColName);
  
                          //delete command
                          ((SqlCommand)adapter.DeleteCommand).Parameters.Add(col.ParameterName, type, size, col.QuotedName);
  
                          //selectrow command
                          ((SqlCommand)adapter.SelectRowCommand).Parameters.Add(col.ParameterName, type, size, col.QuotedName);
  
                          //insertmetadata command
                          ((SqlCommand)adapter.InsertMetadataCommand).Parameters.Add(col.ParameterName, type, size, col.QuotedName);
  
                          //updatemetadata command
                          ((SqlCommand)adapter.UpdateMetadataCommand).Parameters.Add(col.ParameterName, type, size, col.QuotedName);
  
                          //deletemetadata command
                          ((SqlCommand)adapter.DeleteMetadataCommand).Parameters.Add(col.ParameterName, type, size, col.QuotedName);
                      }
  
                      //insert command
                      ((SqlCommand)adapter.InsertCommand).Parameters.Add(col.ParameterName, type, size, col.QuotedName);
  
                      //update command
                      ((SqlCommand)adapter.UpdateCommand).Parameters.Add(col.ParameterName, type, size, col.QuotedName);
                  }
  
                  this.SyncAdapters.Add(adapter);
              }
  
              //SelectNewTimestampCommand
              SqlCommand selectNewTimestampCommand = new SqlCommand();
              string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
              selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
              selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
              selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
  
              this.SelectNewTimestampCommand = selectNewTimestampCommand;
              //this.SelectNewTimestampCommand = this.get
  
              //SelectScopeInfoCommand
              SqlCommand selReplicaInfoCmd = new SqlCommand();
              selReplicaInfoCmd.CommandType = CommandType.Text;
              selReplicaInfoCmd.CommandText = "SELECT " +
                                              "scope_id, " +
                                              "scope_local_id, " +
                                              "scope_sync_knowledge, " +
                                              "scope_tombstone_cleanup_knowledge, " +
                                              "scope_timestamp " +
                                              "FROM scope_info " +
                                              "WHERE sync_scope_name = @" + DbSyncSession.SyncScopeName;
              selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
  
              this.SelectScopeInfoCommand = selReplicaInfoCmd;
  
              //UpdateScopeInfoCommand
              SqlCommand updReplicaInfoCmd = new SqlCommand();
              updReplicaInfoCmd.CommandType = CommandType.Text;
              updReplicaInfoCmd.CommandText = "UPDATE scope_info SET " +
                                              "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
                                              "scope_id = @" + DbSyncSession.SyncScopeId + ", " +
                                              "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
                                              "WHERE sync_scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
                                              " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
                                              "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
              updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  
              this.UpdateScopeInfoCommand = updReplicaInfoCmd;
  
              //SelectTableMaxTimestampsCommand
              SqlCommand selTableMaxTsCmd = new SqlCommand();
              selTableMaxTsCmd.CommandType = CommandType.Text;
              selTableMaxTsCmd.CommandText = "SELECT 'Customer' AS table_name, " +
                                             "MAX(local_update_peer_timestamp) AS max_timestamp " +
                                             "FROM Sync.Customer_Tracking";
  
              //this.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;
          }
      }

  • Marked as answer by AJ8829 Wednesday, October 6, 2010 3:25 PM

  Wednesday, October 6, 2010 3:24 PM
• 

  

  0

  Sign in to vote

  iPad to Mac Transfer is an magic iPad manager for Mac OS users which not only can transfer iPad music, photos, videos, movies, podcast and TV program to Mac but also can transfer iPad to iTunes, add mac files to iPad, etc.

  

  iPad to Mac Transfer not only supports iPad Wi-Fi+3G, iPad Wi-Fi, iPad 16GB, iPad 64GB and others iPads but also supports iPod 1G, iPod 2G, iPod 3G, iPod 4G, iPod Classic, iPod mini, iPod nano 1, iPod nano 2, iPod nano 3, iPod nano 4, iPod shuffle 1, iPod shuffle 2, iPod shuffle 3, iPod Touch, iPod Touch 2, iPod Video, iPhone, iPhone OS 3.0/3.1. iPhone4

  Then How to Transfer iPad to Mac? Just refer to the following.

  Firstly, just free download and install this iPad Manager for Mac and then run it.

  Secondly, connect your iPad to your Mac, and then the following interface will pop up.

  Finally, Select the iPad files that you want to transfer to Mac and then click on "ipad to mac", you will transfer iPad files to Mac.

   

  

  

  

  For More details of iPad to Mac Transfer, just visit here .

  For iPad to iTunes guide, just visit here

  This is not very relevant....

  Friday, October 8, 2010 12:40 PM