locked
Synching a Dataset to a Database RRS feed

  • Question

  • I am redesigning an application that was originally designed about 5-6 years ago and trying to take advantage of the capabilities of the ADO.Net Synchronization Framework.  The application is a Visio Add-in.  It pulls data out of a Visio file, puts it into a local dataset, and then merges it with data in a SQL Server database.

    The application has about 15-20 tables with relationships among the tables and foreign key constraints to enforce referential integrity.  The amount of data is relatively small and it also typically shared with a very small number of other users.

    The application as it was originally designed used ADO.Net with Table Adapters to do all of the synchronization betweeen the dataset and the database.  Every time the application does an update on the data in the dataset, it also updates the data in the database.  I would like to find a better alternative that does the synchronization of the dataset with the database more automatically and does it more as a background synchronization task.

    I've looked at ADO.Net synchronization services and that looks like the kind of thing I want to use, but it appears to be designed to synchronize a SQL Server Compact Edition database with a larger SQL Server database.  It seems to leave open the question of how the data gets from the dataset into the SQL Sever Compact Edition database.  Is there some kind of equivalent functionality to easily synchronize a dataset with a SQL Server database?  Does sync services do that?

    Ideally, what I would like to happen is for my application to just put the data in the dataset and have another process synchronize the dataset in the background with both SQL Server Compact Edition and a larger SQL Server Express database.

    Any thoughts on the best way to do this?  Is it still the best practice to use individual table adapters or data adapters on each table?  If so, is there a good way to synchronize those changes to account for referential integrity rules?

    Thanks,


    Chuck
    • Moved by Max Wang_1983 Thursday, April 21, 2011 10:25 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, December 4, 2009 3:00 PM

Answers

  • You should look at the Sync Services for ADO.NET and Sync Framework. Using that you can synchronize your backend SQL Server with a local SQL Server compact database. Then you can always load the local SQL Server Compact data into a dataset and do further manipulation. Thereafter you can save the data back to the local Compact database which can then be synchronized with your backend.

    The msdn docs have a lot of info on these.
    This posting is provided AS IS with no warranties, and confers no rights
    Saturday, December 5, 2009 4:28 AM

All replies

  • I just wanted to clarify one additional point on this - at the current tiem there is no data store other than a Dataset on the client system...any changes to the data are persisted directly to Visio.

    In the original implementation of this application which was designed about 5-6 years ago, the information was also persisted to an Access database in addition to being stored in Visio.  The way that worked used DataAdapters and whenever the application made changes to the data in the dataset it wrote it to both the Access database and back to Visio.

    The architecture I’m trying to develop right now will use a centralized SQL Server Express Server to consolidate data from multiple client systems.  On the client machines, I have SQL Server Custom Edition set up now, but I really don’t need that since the data doesn’t need to be persisted locally at the client level at all...I should be able to sync the dataset directly to SQL Server Express…SQL Server Custom Edition doesn’t buy me too much.

    I hope that makes sense…any thoughts and recommendations would be appreciated.


    Chuck
    Friday, December 4, 2009 5:38 PM
  • You should look at the Sync Services for ADO.NET and Sync Framework. Using that you can synchronize your backend SQL Server with a local SQL Server compact database. Then you can always load the local SQL Server Compact data into a dataset and do further manipulation. Thereafter you can save the data back to the local Compact database which can then be synchronized with your backend.

    The msdn docs have a lot of info on these.
    This posting is provided AS IS with no warranties, and confers no rights
    Saturday, December 5, 2009 4:28 AM