locked
Developing Custom SyncAgent For Data Filtering (Error) RRS feed

  • Question

  • Howdy,
     
    I am woking on an application that will be syncing project information from a server to an occassionaly connected client.  However, I only need a subset of the project data.  Say of the 10,000 projects in the dB, I only require projects for a certain application.  Maybe 100 or so.  To do this, I began to write a custom SyncAgent that pulls data from the server by an @applicationID parameter.  I based my SyncAgent on  the Books Online example provided by MS.  I did a basic adaptation of the example to meet my needs.  I have a Project table on the client that was created when I ran the sync wizard from VS2008 and I have a Project and Project_Tombstone table on the server.  I consistently get an Cast Exception when I run the mysyncAgent.Synchronize()  method from my nUnit tests.  This is the error as it is output in VS2008:

    System.InvalidCastException: Object must implement IConvertible.
    at System.Convert.ChangeType(Object valueType conversionTypeIFormatProvider provider)
    at System.Data.SqlClient.SqlParameter.CoerceValue(Object valueMetaType destinationType)
    System.InvalidCastException: Failed to convert parameter value from a Byte[] to a DateTime.

    Thanks for all your help!

    I'm not quite sure how to begin to debug this.  Is it an issue with the dBs or is it a problem in code. 

    Here is the SyncAgent Code:

     

    1 using Microsoft.Synchronization;  
    2 using Microsoft.Synchronization.Data;  
    3 using Microsoft.Synchronization.Data.Server;  
    4 using Microsoft.Synchronization.Data.SqlServerCe;  
    5  
    6 namespace Org.Lakewood.Sync.PME.Inspections  
    7 {  
    8     public class SyncAgentPME : SyncAgent  
    9     {  
    10            public  SyncAgentPME()  
    11             {  
    12                 this.LocalProvider = new SyncProviderLocalPME();  
    13                 this.RemoteProvider = new SyncProviderRemotePME();  
    14  
    15                 //Create two SyncGroups so that changes to OrderHeader  
    16                 //and OrderDetail are made in one transaction. Depending on  
    17                 //application requirements, you might include Customer  
    18                 //in the same group.  
    19                 SyncGroup projectSyncGroup = new SyncGroup("Project");  
    20  
    21                 //Add each table: specify a synchronization direction of  
    22                 //DownloadOnly.  
    23                 SyncTable projectSyncTable = new SyncTable("Project");  
    24                projectSyncTable.CreationOption = TableCreationOption.UseExistingTableOrFail;  
    25                projectSyncTable.SyncDirection = SyncDirection.DownloadOnly;  
    26                 projectSyncTable.SyncGroup = projectSyncGroup;  
    27                 this.Configuration.SyncTables.Add(projectSyncTable);  
    28  
    29                 //Specify a value for the parameters that are added  
    30                 //in the server synchronization provider.  
    31                 this.Configuration.SyncParameters.Add(  
    32                     new SyncParameter("@ApplicationID", 7));  
    33                 //this.Configuration.SyncParameters.Add(  
    34                     // new SyncParameter("@Active", 1));  
    35  
    36             }  
    37     }  
    38 }  
    39  




    Here is the Remote Provider Code:

    1 using Microsoft.Synchronization.Data;  
    2 using Microsoft.Synchronization.Data.Server;  
    3 using System.Data;  
    4 using System.Data.SqlClient;  
    5  
    6 namespace Org.Lakewood.Sync.PME.Inspections  
    7 {  
    8     public class SyncProviderRemotePME : DbServerSyncProvider  
    9     {
    10         #region Properties and Datamembers  
    11           
    12          
    13         private const string _serverName = "MASSIVE\\SQL_TEST";  
    14         private const string _serverDbName = "LKWDProjectManagementSync";  
    15  
    16         //Return the server connection string.   
    17         public string ServerConnString  
    18         {  
    19  
    20             get { return "Data Source=" + _serverName + "; Initial Catalog=" + _serverDbName + "; Integrated Security=True"; }  
    21  
    22         }
    23
    24         #endregion  
    25  
    26         #region Constructor  
    27         /// <summary>  
    28         /// Constructor  
    29         /// </summary>  
    30         public SyncProviderRemotePME()  
    31         {  
    32             SqlConnection serverConn = new SqlConnection(ServerConnString);   
    33             this.Connection = serverConn;  
    34  
    35             //Create a command to retrieve a new anchor value from  
    36             //the server. In this case, we use a timestamp value  
    37             //that is retrieved and stored in the client database.  
    38             //During each synchronization, the new anchor value and  
    39             //the last anchor value from the previous synchronization  
    40             //are used: the set of changes between these upper and  
    41             //lower bounds is synchronized.  
    42             //  
    43             //SyncSession.SyncNewReceivedAnchor is a string constant;   
    44             //you could also use @sync_new_received_anchor directly in   
    45             //your queries.  
    46             SqlCommand selectNewAnchorCommand = new SqlCommand();  
    47             string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;  
    48             selectNewAnchorCommand.CommandText = "SELECT " + newAnchorVariable + " = min_active_rowversion() - 1";  
    49             selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp);  
    50             selectNewAnchorCommand.Parameters[newAnchorVariable].Direction = ParameterDirection.Output;  
    51             selectNewAnchorCommand.Connection = serverConn;  
    52             this.SelectNewAnchorCommand = selectNewAnchorCommand;   
    53  
    54             //Create a filter parameter that will be used in the filter clause for  
    55             //the inspections  
    56             SqlParameter filterParameterByApplication = new SqlParameter("@ApplicationID", SqlDbType.Int);  
    57             //SqlParameter filterParameterByActive = new SqlParameter("@Active", SqlDbType.Bit);  
    58  
    59             SqlSyncAdapterBuilder projectBuilder = new SqlSyncAdapterBuilder(serverConn);  
    60             projectBuilder.TableName = "Project";  
    61             projectBuilder.TombstoneTableName = projectBuilder.TableName + "_Tombstone";  
    62             projectBuilder.SyncDirection = SyncDirection.DownloadOnly;  
    63             projectBuilder.CreationTrackingColumn = "CreationDate";  
    64             projectBuilder.UpdateTrackingColumn = "LastEditDate";  
    65             projectBuilder.DeletionTrackingColumn = "DeletionDate";  
    66  
    67             //Specify a filter clause, which is an SQL WHERE clause   
    68             //without the WHERE keyword. Use the parameter that is   
    69             //created above. The value for the parameter is specified   
    70             //in the SyncAgent Configuration object.  
    71             string applicationFilterClause = "applicationFK=@ApplicationID";  
    72             projectBuilder.FilterClause = applicationFilterClause;  
    73             projectBuilder.FilterParameters.Add(filterParameterByApplication);  
    74             projectBuilder.TombstoneFilterClause = applicationFilterClause;  
    75             projectBuilder.TombstoneFilterParameters.Add(filterParameterByApplication);  
    76  
    77             //string activeFilterClause = "projectActive=@Active";  
    78             //projectBuilder.FilterClause = activeFilterClause;  
    79             //projectBuilder.FilterParameters.Add(filterParameterByActive);  
    80             //projectBuilder.TombstoneFilterClause = activeFilterClause;  
    81             //projectBuilder.TombstoneFilterParameters.Add(filterParameterByActive);  
    82  
    83             SyncAdapter projectSyncAdapter = projectBuilder.ToSyncAdapter();  
    84             projectSyncAdapter.TableName = "Project";  
    85             this.SyncAdapters.Add(projectSyncAdapter);  
    86  
    87              
    88  
    89         }
    90         #endregion  
    91         
    92           
    93     }  
    94
    • Moved by Hengzhe Li Friday, April 22, 2011 5:45 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, February 4, 2009 5:33 PM

All replies

  • Howdy again,

    I changed the SyncDirection to Snapshot, and now I can populate the Client Table with out error.  Here is the code  change (in red) I made to the SyncAgent:

                   //Add each table: specify a synchronization direction of  
                    //DownloadOnly.  
                    SyncTable projectSyncTable = new SyncTable("Project");  
                   projectSyncTable.CreationOption = TableCreationOption.UseExistingTableOrFail;  
                   projectSyncTable.SyncDirection = SyncDirection.Snapshot;  
                    projectSyncTable.SyncGroup = projectSyncGroup;  
                    this.Configuration.SyncTables.Add(projectSyncTable); 

     

    Anyone have thougths on why it would fail with a bi-directional sync?

    Thanks again folks!

    Thursday, February 5, 2009 3:40 PM