locked
Using Sync Services for ADO.NET on a per user basis RRS feed

  • Question

  • Hi all --

    I was hoping to get some design direction here for an application I am working on.  I have a mobile application that runs on my device.  This application has the ability to sync data with a SQL Server 2008 database through a WCF service (much like the walk through here).  I have this working in limited capacity.  Most of the data is bidirectional.  One table is UploadOnly. 

    What I would like to do is sync the bidirectional tables on a per user basis.  The one SQL Server 2008 DB will hold data for all users who use the mobile application.  On startup, the mobile application should only sync data for that user, not all data for all users.  How would I accomplish this?

    My initial thought was to add username/password credentials to use the WCF service as a starting point. , passed via logging in to the mobile app.   I would then thinking to use these credential, or a corresponding userid, for the sync.  Is there also a way to pass a user id in the sync so that only the user's data is pulled?  dO I just need to change the SQL under the hood?

    Any help or thoughts here would be appreciated.


    Greg Robinson
    • Moved by Hengzhe Li Friday, April 22, 2011 5:21 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, February 25, 2009 3:09 AM

Answers

  • May I assume that each user should use his/her own device?
    If so there could be a solution.
    Assume the mobile app can get the login user name and it can pass this user name information as the session level variable value to the SQL server (through WCF or web serivce) during sync.
    And on the SQL server table definition, you would have a column indicating which row can be accessed by which user give the user name from the mobile device.
    By using this row-filtering technique, you can specify a matching condition in the where-clause for the

    Select Incremental Insert/Update/Delete commnand.

    We can go through more details if you like this approach.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 26, 2009 7:10 PM
    Answerer

All replies

  • May I assume that each user should use his/her own device?
    If so there could be a solution.
    Assume the mobile app can get the login user name and it can pass this user name information as the session level variable value to the SQL server (through WCF or web serivce) during sync.
    And on the SQL server table definition, you would have a column indicating which row can be accessed by which user give the user name from the mobile device.
    By using this row-filtering technique, you can specify a matching condition in the where-clause for the

    Select Incremental Insert/Update/Delete commnand.

    We can go through more details if you like this approach.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, February 26, 2009 7:10 PM
    Answerer
  • Hi Leo --

    Thank you kindly for your reply.  I appreciate you taking the time. 

    Your assumption is correct; each user has their own device.  Your suggestion makes sense at a high level.  Sync Services for ADO.NET is relatively new to me so I was not aware of this approach.  I did find this article as an example.  I will go through this article more thoroughly and work on integrating something similar into my project.

    I will post any follow up questions, should I have any.

    Thanks,

    Greg


    Thursday, February 26, 2009 10:57 PM
  •  Hi Leo --

    It has taken me some time to first understand then implement the filtering as you suggest.  I do have one question that I do need further clarification on.  Here is how I have my environment setup.

    First, assume each user has his/her own device.  Each user has his/her own username/password, that when authenticated correctly will map to a token.  This token is a GUID so it will be globally unique. 

    A WCF service has been setup (from the example listed in my first post).  In addition to the sync functionality, I have also added an authentication service.  This will validate the username/passwordand return the token when correct.

    In the local database class (.sync) file that is created in my middle tier, I have added a constructor to my ServerSyncProvider object (which extends from Microsoft.Synchronization.Data.Server.DbServerSyncProvider) that takes the token as an argument.  There is now added functionality in the constructor which updates the SelectIncrementalInsertsCommand command text on SynAdapter object (which extends from Microsoft.Synchronization.Data.Server.SyncAdapter) to filter off the token.

    Are you following me?

    Right now, that token is being passed to the service code by a service call before I sync and stored in a property in the service code.  However, I don't think this is good design.  If multiple devices use this service, each with their own service proxy, can I store the token in this manner?  Or, is there some sort of sync session id I can pass around to map to the token?  This way I could write the session id and tokens to a DB for retrieval when needed.

    If you would like to take this conversation offline, I can be reached at robinson at rdacorp.com.

    Thanks you for your time.


    Greg

    Wednesday, March 18, 2009 2:32 AM