Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
How to filter rows from WP7/IPad using Sync Server RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    I am in the process of developing a mobile screen with WP7 for an existing web based application which uses a SQL 2005 database. I intend to use Sync framework 4.0 for offline update capabilities.

     

    The CRUD screen is oprating on one underlying database table that has many references to other tables. (A typical scenario - 'master-detail')

    Ex - CarCustomer which refers, CarID with car table, customerID with Customer table and LocationID with location table. etc..

     

    Requirements:

    - I would only update CarCustomer table ('details' table) while I am offline and be able to sync it back to server upon available connection.

    - Also, be able to search for records based on the references such as customerID, carID and LocationID.

    - User would search for data only while connected and update data while offline.

     

    Could you please help me with the following

     

    Questions :

    - Is it possible to filter the CarCustomer table which is synced to the phone based on the filter criteria that user searches ? If so how ? Or do we need to have the entire CarCustomer table on the phone ? this would make it very heavy as CarCustomer table could have 1000s of rows.

    - Dependency master tables. As the carCustomer table contains refernces to master tables such as Customer, Car and Location, should these tables be present as well on the phone ? or is there a work around without having to sync refered tables ?

     

    Hope to hear from you soon.

     

    Thank you,

    Srik

    Srik
    Thursday, October 28, 2010 7:20 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you Ganeshan,

    The first answer is very clear. I have a small question on the second proposed answer.

     

    Usually in a typical scenario, we never bind the detail table as it is to a display control(for ex- a grid). We always get the reference from the master table for the actual Name of the field based on ID present in detailed table and then bind the detailed table to the grid.

     

    My question is (based on the above CarCustomer scenario) -

    -Since the detailed table 'CarCustomer' is refering the master tables 'car', 'customer' and 'location' etc, how would I print details such as CustomerName, CarName and Location information on the grid when only the detailed table is synced on the phone ?

    I would like to know if we can get away from not syncing the master tables and still be able to get car Name, customer Name and Location specifics from the refered tables while I am only syncing the CarCustomer table which stores the IDs for the rest of the tables.

    I am thinking we need to sync all the referred tables. But I would like to know if there is an alternate solution. Usually in a client/server or Asp.Net WebApp I would write a SQL procedure which would do an inner join on the rest of the tables ! Need to find out if sync can fetch only related rows from the referred tables for a sync request.

    Thanks again ! You guys are really helping us out with a very short turn around time :)

    Srik

    Srikanth
    • Marked as answer by Srik HN Friday, October 29, 2010 6:30 PM
    Friday, October 29, 2010 6:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Srik,

    Based on your explanation of the scenario that you want to support, you really have 2 options:

    1. Include the related tables in sync: You can choose to include the related tables, also filtering them based on the same criteria that you use for the CarCustomer table. This will give you the necessary data. SyncFx cannot sync views of data (inner joins.. etc) so there is no way to get the master data without actually syncing the actual tables (you can choose to sync only certain columns from the master tables)

    2. Get the master data through some other way: You can choose to get this data outside of the sync logic. Say during application startup by directly quering the database. This way you can avoid including these tables in sync. But I think option 1 is a much better solution for you and including the master tables in sync is probably the way to go here.

    SDE, Sync Framework - http://www.giyer.com
    • Marked as answer by Srik HN Friday, October 29, 2010 6:30 PM
    Friday, October 29, 2010 5:41 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Srik,

    - Is it possible to filter the CarCustomer table which is synced to the phone based on the filter criteria that user searches ? If so how ? Or do we need to have the entire CarCustomer table on the phone ? this would make it very heavy as CarCustomer table could have 1000s of rows.

    [Ganeshan] - While we support filter parameters (for example: you can filter the CarCustomer table based on certain column values that are dynamic and sent from the client), I should point out that the filters that are submitted in the first sync request from a client are retained for subsequent sync requests from the same client. This means that you cannot change filters for a given client unless you choose to abandon the current data on the client and choose to start fresh with a new initial sync request.

    - Dependency master tables. As the carCustomer table contains refernces to master tables such as Customer, Car and Location, should these tables be present as well on the phone ? or is there a work around without having to sync refered tables ?

    [Ganeshan] - You can choose to provision just one table on the database, which in this case is CarCustomer. There is no requirement to provision related tables.

    Hope this helps.

    SDE, Sync Framework - http://msdn.com/sync/
    • Proposed as answer by Ganeshan Thursday, October 28, 2010 6:23 PM
    Thursday, October 28, 2010 6:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you Ganeshan,

    The first answer is very clear. I have a small question on the second proposed answer.

     

    Usually in a typical scenario, we never bind the detail table as it is to a display control(for ex- a grid). We always get the reference from the master table for the actual Name of the field based on ID present in detailed table and then bind the detailed table to the grid.

     

    My question is (based on the above CarCustomer scenario) -

    -Since the detailed table 'CarCustomer' is refering the master tables 'car', 'customer' and 'location' etc, how would I print details such as CustomerName, CarName and Location information on the grid when only the detailed table is synced on the phone ?

    I would like to know if we can get away from not syncing the master tables and still be able to get car Name, customer Name and Location specifics from the refered tables while I am only syncing the CarCustomer table which stores the IDs for the rest of the tables.

    I am thinking we need to sync all the referred tables. But I would like to know if there is an alternate solution. Usually in a client/server or Asp.Net WebApp I would write a SQL procedure which would do an inner join on the rest of the tables ! Need to find out if sync can fetch only related rows from the referred tables for a sync request.

    Thanks again ! You guys are really helping us out with a very short turn around time :)

    Srik

    Srikanth
    • Marked as answer by Srik HN Friday, October 29, 2010 6:30 PM
    Friday, October 29, 2010 6:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Srik,

    Based on your explanation of the scenario that you want to support, you really have 2 options:

    1. Include the related tables in sync: You can choose to include the related tables, also filtering them based on the same criteria that you use for the CarCustomer table. This will give you the necessary data. SyncFx cannot sync views of data (inner joins.. etc) so there is no way to get the master data without actually syncing the actual tables (you can choose to sync only certain columns from the master tables)

    2. Get the master data through some other way: You can choose to get this data outside of the sync logic. Say during application startup by directly quering the database. This way you can avoid including these tables in sync. But I think option 1 is a much better solution for you and including the master tables in sync is probably the way to go here.

    SDE, Sync Framework - http://www.giyer.com
    • Marked as answer by Srik HN Friday, October 29, 2010 6:30 PM
    Friday, October 29, 2010 5:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Ganeshan,

    We will go with option 1, if the performance seems to become an issue then we may cache the master data during app load on the phone as suggested by option 2.

     

    Thanks for your timely support !

    Srikanth
    Friday, October 29, 2010 6:30 PM