Save entire database offline

4 posts, 2 answers
  1. Brian Pratt
    Brian Pratt avatar
    28 posts
    Member since:
    Apr 2010

    Posted 18 Jan 2011 Link to this post

    I have a database that I want to "Save As" a file from my WPF program.  I also want to be able to work offline against this saved file (no database connection to live database).  The idea is to be able to design a system offline, and publish the system to the live database.  The client would be remote from the database (meaning I cannot do a DB backup, nor would that work)

    Some ideas I've thought about using are:  
    Serialize the entire database object tree (using EF or OA, or some other ORM) to a file, deserialize and work in memory against the objects.  To publish, just clear out the database, then let the ORM save the objects back.  The problem with this is that there will be a lot of work in creating serialized objects, keeping primary keys synced, and the size of the file created.

    Use SQL CE, and create a new db file, create database tables, and write all objects to the new SQL CE database.  For offline, just change the connection string to SQL CE.  To publish, ... dunno?  The problem with SQL CE is that EF doesnt like database generated keys, and has a very limited datatype selection which would force me to either translate (having two ORM objects one for SQL one for CE with different database types and keys), or re-design my live database to dumb it down for SQLCE (which I cannot do)

    So, my question is:  Does OA provide any semi-easy way to save all the data in its objects off to a file, and load them back in?

    Thanks,
    Brian
  2. Answer
    Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 21 Jan 2011 Link to this post

    Hello Brian Pratt,

    You have posed quite an intriguing question. Providing an automated way to save all your data in a file, work with it offline and later synchronize the changes to the database goes slightly beyond the functionality of an ORM tool. OpenAccess ORM is optimized for working with the database on the fly and offers a transparent way to handle connections efficiently. You can load objects in memory, perform some operations with them and later save the changes to the database by interacting with our special context and scope objects designed to encapsulate the database connection and transaction handling.
    Working with SQL CE is an alternative as you have mentioned. Since you cannot find a common data type base for the models of your existing and new databases, you can work with two conceptual models. However, the synchronization logic should be written manually. You can use two context objects and perform some merging operations while having the the two contexts active. Yet, this is not done automatically as an ORM tool concentrates on working with one database at a time. You can change the connections and connect to different backends but at one moment you interact with one database.

    Do not hesitate to contact us to further discuss scenario you have described.

    Regards,
    Petko_I
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  3. DevCraft banner
  4. Brian Pratt
    Brian Pratt avatar
    28 posts
    Member since:
    Apr 2010

    Posted 21 Jan 2011 Link to this post

    Thanks for the reply.

    In working thru this, I've downloaded many different apps (T4 editors, frameworks etc).  I think I have a solution.

    I am using the Microsoft Sync Framework, and modifying my ORM to match the CE database (SQL doesnt seem to have a problem downshifting to CE datatypes).  I can use one data model for both CE and SQL.

    I've got a demo where I save, load, "save as" SQL CE database files and synchronize them to the live database.  It seems to be working well, but as with all development, there will probably be something that bites me in the ....  later on.

    Anyway, thanks!
    Brian
  5. Answer
    Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 24 Jan 2011 Link to this post

    Hello Brian Pratt,

    You have invented an interesting solution. Microsoft Sync Framework seems to offer just the kind of synchronization that is needed to maintain a consistent state of your data. Since you will be working with a model that interacts with SQL CE, you should not hesitate to contact us if you need any assistance in the mapping scenarios.

    Kind regards,
    Petko_I
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
Back to Top