I'm using a tree structure in an application I'm writing, and have written recursive methods that will iterate down the tree and populate a number of new DataTables with values. I want to be able to export these tables to a data source, and then later update that data source when the tree or members of the tree change.
There are a few caveats to my situation though:
Caveat 1) The methods I use to generate the DataTables are too complex to figure out what exactly changed between updates. So for each update I will have to to generate an entirely new DataTable and then some how merge it into/replace the data source with the new version. The primary keys, however, will remain the same for related rows, and could be used to figure out what rows need to be added/deleted/modified.
Caveat 2) The schemas of my DataTables are variable. Each table will have some set of fields that will always be included, such as ID and Name. But other fields may be removed or added after each new update. (See my explanation below for why this is the case).
I'd like to keep as much of my code as possible generic from any specific type of data source. I've been looking into using DbProviderFactories rather then a specific provider type to keep my code generic. Yet caveats #1 and #2 above are making it very difficult to base anything I write off of the examples I find for using DbProviderFactories. The whole "variable schema" issue is really complicating things.
I can get this working by just writing out a comma delimited text file, because then I'm not restricted by a preexisting schema . But I was hoping to implement something a bit more robust then CSV files.
I was also thinking of just forgetting about using all the ADO.NET jazz and just exporting my generated DataSet directly to XML and then connecting MS Excel or MS Access to the XML file. Yet I'm not sure how Access or Excel will behave when I overwrite the exported XML file with a new file with fields added or removed from the schema. I'm leaning towards this option. But I think that's mostly because I have next to zero experience with ADO, and XML is a lot more tangible to me as it's easy to open up in notepad and poke around and see what was exported.
In reality, once things are setup, the schemas of my tables shouldn't change with each update. It's just that each project in my office that uses my app will have a unique set of fields to export, and I don't want to have to go rewriting my app for each new project. So flexibility needs to be built in. The best solution may involve having each project setup a base version of their tree structure, and then generating a "project database schema" from it. Major changes to the structure, that would result in changes to the schema, would then be treated as an entirely new export.
Again, sorry for the long question. I'm pretty much a virgin to ADO, and don't have a whole lot of experience with databases. I also don't work at a software company where I'd have people to bounce ideas off of. So I'd really apreciate some more experience developers take on this problem and how they would approach it.
Supplemental Information
Here's the reason why my table schema may vary with each export. This may or may not be important, but I have a feeling someone is going to ask why.
The relationships and the depth of each node in my tree is significant. Thus for each leaf node I need to track how deep it is in the tree, and each of its parents and their depth in the tree. Similar to a Family tree where the relationship between people is based on their distance from one another in the tree. Parents are directly above, grandparents are two levels above, great grandparents are three levels above etc.. etc..
In addition, I want to be able to give the user the ability to add their own custom properties and the values of these properties would also be tacked on to the tables in additional fields.
For example if I had a tree like this:
A
B C
D E
F
I might get a table like this:
ID Name Depth Level1 Level2 Level3 UserProp1 UserProp2
A A 1 Blue Yes
B B 2 A Blue No
C C 2 A Green No
D D 3 A C Black Yes
E E 3 A C Orange Yes
F F 4 A C E Red No
Where the number of Level# fields depends on the depth of the tree, and the UserProp# fields are custom fields added by the user. But ID and Name will always be included.