locked
Bi-Directional Sync Practices RRS feed

  • Question

  •  

    I apologize if this was discussed previously.  I am looking for some general direction in how to implement an N-tier bi-directional smart client application. 

     

    The Microsoft samples only go over downloadonly and as a result, they are free to delete the database at application startup.  Needless to say, with bi-directional synch, deleting the DB blindly at startup will cause a user to lose all changes if the data was never synched before their last logg off or if the user must go disconnected for an extended period.

     

    By not deleting the database, the client database will grow to a size over time which is not within requirements.  Has anyone else considered this problem and how did you overcome it?  I have my ideas, none of which I am completely happy with from a performance of ease of maintenance perspective.

     

    Finally, I need to filter the rows I am pulling from the server.  The MS example at http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.syncparameter(SQL.100).aspx is somewhat helpful.  What I am missing is how one reads the parameter specified at the client on the server by specifying the value in the stored procedure command.  In the example...

     

    customerIncrInserts.CommandText =
        "SELECT CustomerId, CustomerName, CustomerType " +
        "FROM Sales.Customer " +
        "WHERE SalesPerson = @SalesPerson " +
        "AND (InsertTimestamp > @sync_last_received_anchor " +
        "AND InsertTimestamp <= @sync_new_received_anchor " +
        "AND InsertId <> @sync_client_id)";

    they are using text and not a stored procedure. Even here, I do not see how the string WHERE SalesPerson = @SalesPerson magically sets the SalesPerson ID without accessing some kind of property of parameters I can not seem to find.

    • Moved by Max Wang_1983 Friday, April 22, 2011 7:15 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, June 5, 2008 4:14 PM

Answers

  •  

    It is not necessary to delete the client db. in fact, I believe, some real world scenarios, the client db could be kept for sometime on the client. surely the db will grow especially if there are a lot inserts happening on the server side. the way to control the local client db from growing too fast could vary from case to case: some of them can be resolved by using filtering; some of them can be handled to put the data to different databases.

     

    the filtering smaple actauly has the code for the parameters, this is the full copy from the link above in your post.

     

    SqlCommand customerIncrInserts = new SqlCommand();
    customerIncrInserts.CommandText =
        "SELECT CustomerId, CustomerName, CustomerType " +
        "FROM Sales.Customer " +
        "WHERE SalesPerson = @SalesPerson " +
        "AND (InsertTimestamp > @sync_last_received_anchor " +
        "AND InsertTimestamp <= @sync_new_received_anchor " +
        "AND InsertId <> @sync_client_id)";
    customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
    customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
    customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
    customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
    customerIncrInserts.Connection = serverConn;
    customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;

    thanks

    Yunwen

     

    Thursday, June 5, 2008 11:55 PM
    Moderator

All replies

  •  

    It is not necessary to delete the client db. in fact, I believe, some real world scenarios, the client db could be kept for sometime on the client. surely the db will grow especially if there are a lot inserts happening on the server side. the way to control the local client db from growing too fast could vary from case to case: some of them can be resolved by using filtering; some of them can be handled to put the data to different databases.

     

    the filtering smaple actauly has the code for the parameters, this is the full copy from the link above in your post.

     

    SqlCommand customerIncrInserts = new SqlCommand();
    customerIncrInserts.CommandText =
        "SELECT CustomerId, CustomerName, CustomerType " +
        "FROM Sales.Customer " +
        "WHERE SalesPerson = @SalesPerson " +
        "AND (InsertTimestamp > @sync_last_received_anchor " +
        "AND InsertTimestamp <= @sync_new_received_anchor " +
        "AND InsertId <> @sync_client_id)";
    customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);
    customerIncrInserts.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
    customerIncrInserts.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
    customerIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId, SqlDbType.UniqueIdentifier);
    customerIncrInserts.Connection = serverConn;
    customerSyncAdapter.SelectIncrementalInsertsCommand = customerIncrInserts;

    thanks

    Yunwen

     

    Thursday, June 5, 2008 11:55 PM
    Moderator
  • I saw the line...

    customerIncrInserts.Parameters.Add("@SalesPerson", SqlDbType.NVarChar);

    That is what confused me somewhat.  However it is making sense to me now, the synch framework must automatically assign the value of @SalesPerson for me and I don't have to specify anywhere the key/value pair which to map to this parameter.  I assume that if @SalesPerson was never set by the client, it would throw an error or pass a null value?

     

    I like the idea of creating seperate client databases.  If I applied this to each login account that would solve some issues. 

     

    Thank you for the help, as a veteran C# and VB.NET developer, I can't recall a more helpful resource as the MSDN forums. 

    Friday, June 6, 2008 2:01 PM
  • I think if the client doesn't set the value for the parameter, it will take whatever default vlaues is for the sqlparameter.

     

    Glad to help.

     

    thanks

    Yunwen

    Friday, June 6, 2008 11:10 PM
    Moderator
  • Take a look at our new Bi-Di samples posted to the blog:

     

    http://blogs.msdn.com/sync/archive/2008/06/16/extending-visual-studio-2008-sp1-sync-designer-to-support-bi-directional-synchronization.aspx

     

    We received a significant amount of feedback related to wanting bi-di samples that extend the designer generated code and, as a result, decided to add the section above to the VS 2008 doc set. Enjoy!

     

    Sean Kelley

    Program Manager

    Microsoft

     

    Friday, June 20, 2008 11:37 PM
    Moderator
  • Ok, correct me if I am wrong, but the ONLY thing you have to do in order to enable bi-directional sync is to change the SyncDirection to Bidirectional? Well if it is, it ain't working. The download scenario works, but the upload doesn't. It even OVERWRITES my client table, which I just can't believe. I changed the CreationOptions for the tables to be either UploadExistingOrCreateNewTable or UseExisting but still when I press the sync button (while having NO data in the server database and just one record in the client) my CE database gets deleted and nothing uploads to the server. I just can't see what is going on.

     

    Another strange thing is that the syncGroups have no effect at all. I have three tables, with a couple of Foreign Key Constraints, and EVEN if the tables are in a SINGLE syncGroup, it wouldn't download any changes from the server if I have put the syncAdapters for the tables in the wrong order (that is, the foreign key table first). Any idea why that's happening?

     

    I am using Visual Studio 2008 SP1 Beta.

    Saturday, June 21, 2008 10:46 AM
  •  

    Help anyone?
    Monday, June 23, 2008 4:16 PM
  •  

    well, changing the designer generated code to support bi-directional sync is definately not as simple as changing the syncDirection in the code. there are much more, such as change the server side queies, etc. please refer to the doc Sean posted early on how to enable bidir sync.

     

    the purpose of sync group is to sync tables in a partular group in one db transacion to ensure data consistency ( think of tables with RI ). so syncGroup won't help in this case.

     

    thanks

    Yunwen

    Wednesday, June 25, 2008 9:06 PM
    Moderator