Persisting Dirty, New, Deleted in a local database.

pelinville posted on Monday, July 30, 2007

Currently we have a number of applications that must operate offline. To accomplish this I have created a scaled down version of the ObjectAdapter that will save the normal fields along with the Dirty, New, Deleted and Valid state of all loaded objects. The resultant dataset is encrypted and stored in a file.

We have chosen this route because we host data for our customers and there is no way we are going to allow db synchronization through the internet from desktops we have absolutly no control over.  By forcing the persisted data to be loaded from the local store into our business objects we can look for irregularities and bad/tampered with data.

 

There are some limitations with this design, of course.  After playing with the compact edition of SQL server I really like the small foot print combined with the power and ease of deployment.

 

The problem is I can't figure out an EASY way of creating the database schema and BO's that also includes the fields to persist the dirty, new etc state.  With the ObjectAdapter approach all I had to do was create a new attribute (ForObjectAdaptation) and add it to the various properties in CodeGen and in the base classes.  Since the file is created on every save the schema is automagically created.

 

Actually, even if I had to create a new db with schema with every update it would be fine. But how do I add the other fields such that I don't have to have two versions of every stored proc/sql statement to account for the other fields? (This is thousands of stored procs.) And how do I get the objects to know which stored proc to use?

 

Any ideas?

JonM replied on Tuesday, July 31, 2007

I like the idea you are going for.  The real problem I see with the compact version is that is does not support SPROCs at all (at least not the last time I played with it version 3.1).  Now if they do support SPROCs then you have a decisiion to make.  Do you add all of those extra fields to your main database tables so that the same SPROCs still work (but you simply ignore the values) or do you write a block of code that checks the table schema and inserts/updates those columns only if they exist.

pelinville replied on Tuesday, July 31, 2007

I can easily get over the lack of stored procedures.  In fact I only have about 10 stored procedures and they are not applicable to offline work.

You brought up the problem.  Adding the fields to the main DB just to support the offline operation seems plain wrong.

I thought of something that might work.  Take the object adapter concept and, instead of generating a DataSet/DataTable, generate the SQL DDL to generate the schema. 

Take it one step further and also generate the sql to insert and retrieve the data.  Since the local store is ONLY used to insert and retrieve.  And the object fields to persist are already marked with my attribute.

I think that will work.