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

  • Question

  • Is dynamic filtering possible in SF 2.1?  I have seen posts regarding 2.0 but not 2.1  I know you can create a parameter-based template, but this seems to only be useful for creating a static filter from the template using parameters...  honestly I can't help but wonder what the point of this is??  I still have to provision a separate scope with the parameters hard-coded into the scope right?  Really it seems to make even more work out of provisioning...??

    I need to be able to pass the parameter in from the client at sync time... is there an easy way to do this in SF 2.1?

    I have a very large database and I need to filter dynamically by more than just a single column value.  Is there a way to filter by ANY rather than =, and can I populate ANY with the results of another stored procedure?  That stored procedure would then receive the parameter passed in by the client.

    I'm hoping to avoid making huge changes manually to all the SF stored procedures, but of course will if there is no other way.

    Thanks!

     

     

    Tuesday, October 5, 2010 4:03 PM

Answers

  • 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

All replies

  • 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
  • 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
  • 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
    Tuesday, October 5, 2010 11:15 PM
  • 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
  • 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
  • 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
  • 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