posted on Thursday, July 22, 2004 9:01 PM
by
Benjy
ADO.NET, XSD and DB Agonies - and some relief
Goodness, I've had a couple of really frustrating days trying to get my head round a knotty problem. We have this XSD with about 30 odd complex types and the data source is a DB with dozens of tables. The XSD is more or less a subset of the data. I was looking for the fastest way to code a component that generates XML files from the DB.
Tried looking at SQL FOR-XML EXPLICIT but gave that up as too complicated. Then started to look at coding classes to represent the complex types and manually write the SQL. Went someway down the road and was then advised against it as the XSD is volatile right now and I would need to regenerate the entire set again. By the way, I used XSD.exe (which I only discovered recently) to do the class generation.
So this evening I started coding up the SQL but in a separate external class, so i neednt worry about overwriting my stuff. In any case, the classes in this component are just data containers. So this may not be a 'encapsulated'/composite approach where I call a Load on the root which calls its children and so on, but it works.
A couple of hours ago I was going through the TDD in .NET book (James Newkirk et al) and in the chapter on testing ADO.NET, there was something on quickly generating datasets.
The approaches are
(a) using the DataSet wizard to generate a DataSet class by dragging and dropping db tables on to the design surface (if i remember the material correctly). I think that if the wizard generates the dataset filling code for me, then it might be easy to serialise that and apply an XSL transform to get the schema i want.
(b) Using XSD.exe to generate the Dataset. The problem I envisage here is that i still have to write all that code to fill the dataset and then i still have to serialise it and it wont match the schema I need (or will it?).
Gotta have a look and see. Will post the results here. Does anyone know of any tools that can simplify this process? I'm sure that this is one of the most common problems in data access.