VS2005 Automated Dataset Generation

Something that bugged me yesterday at work was the pain involved when the database is changed and a projects datasets need to be updated (something constantly happening during an apps early dev cycle when the database is still under tweaking). In our case the datasets are effectivelly a straight mirror of the information in the database (simply drag on the tables and away you go – no internal customization) so I was like “man – there has to be a better way – surely this can be automated and how does the designer do it?”. So I went on an investigation…After a late night coding session it was done 🙂

Firstly, as always, I googled a bit and then generated some datasets (checking out all the project options/the files generated etc) and watched what was going on with good old regmon, filemon, and pmon as a way of reverse engineering and seeing what was going on when you were using the ide. After digging a bit I came across MSDataSetGenerator and xsd.exe. MSDataSetGenerator from what I can gather is a com component which handles the dataset components inside the ide (when you check the default tool on the properties of the dataset it is listed there) – I tried for a while to find where the dll was located but had no luck so I then decided to try something else – I could see that xsd.exe would generate the file which is commonly called datasetname.Designer.cs (dataset class file) from the dataset xsd so all I now needed was the xsd file. The xsc and xss just dealt with the layout properties so I figured they were just ide overhead and I could work those out later.

Firstly I wanted to avoid calling out to xsd.exe and I worked out that you could use: TypedDataSetGenerator.Generate() from the System.Data namespace to get similar functionality i.e. it would generate a dataset class file from an xsd. However, near the end of my journey I found that this generated un-compilable dataset class files from the xsd – It would ignore some of the options specified against the dataset name (msprop:Generator_TablePropName) so if your dataset had the same name as a table it would have name conflicts with properties exposed on the dataset. xsd.exe on the same xml picked these options up perfectly (and generated an identical match with what the ide would generate) – I had a winner – call out to xsd.exe with the dataset xsd (this seemed to be the fastest option to get something to work).

To get the dataset xsd I reverse engineered the xml that would be generated in the xsd if you dragged on a few tables, set up some relationships etc i.e. Where did the elements need to go? How where they named? What were the options if they were nullable? What could be stripped out? etc. I then rolled the generation all up into a class which I could then call and just tell it “these are the tables in my dataset – make it!” The datasets where being autogenerated! 🙂  As for the xsc and xss files I wrote these out as if no tables had been dragged on. The good thing about this is that when you drag them on the display settings get saved into the xsc file – so you need to go and move all the tables to get them looking pretty. By leaving them as the default, visual studio would do a pretty arrangement (like the sql management studio option)! Something I havent seen available even from in the ide – killed another problem – didnt need to lay them out now either = even less work 😀

So what are the cons – I havent implemented the mapping of all the sql types to xml types yet (just the most obvious ones) – this wont be hard at all since most of the types produce the same xml – it is more a case of just checking for the special cases and putting those in. Something I’ll do as they come up. xsd.exe needs to be called – maybe this is not too ugly…The code had been slapped together (but it seems to work – some file stuff access needs to be looked at + refactoring) 🙂 – I’ll fix it up during intergration. The dataset specification file is just a comma delimited text file (something to be upgraded to xml during intergration into the main generation tool – if the guys like the tool :-P).

The pro’s are – no more dragging, 1 click to update all datasets to a new database schema, no more arranging, we dont use the data adapters (we delete them) so those are left off (if someone wants those I could put them back in), is quicker than the ide – no lagging out :-). You just need to keep the generation file up to date (we already have one for the main tool which will have all of this info so no probs there)

I’ll post up the first cut of code but if someone wants a hand or has some questions then post them up – Hopefully you wont curse at your db designer when they do an update now 😉 Note: You will need to setup the app config with the right options for your machine (xsd.exe is under C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\xsd.exe if you have installed vs to your c drive) – The app also needs to be compiled 🙂 Big ups to JD for chucking this on his server for me! Thanks bro!

http://blog.bluecog.co.nz/uploads/DataSetGeneration_00.zip

Advertisements

9 Responses to “VS2005 Automated Dataset Generation”

  1. Michael FINLAN Says:

    Website is currently transferring to godaddy so it may be down for a day or two.

    Hi (I am not sure of your name),

    I accidentally came across your xsd generation example. Really excellent. I would never have figured out that myself. I really learnt a lot.

    I am really interested in the process of developing software and hate how much time I spend doing drag and drop etc.

    All the best

    Michael

    Michael FINLAN

  2. Johann Oosthuizen Says:

    Hi

    Great Utility just browsed through the code quickly, Im wondering if its possible to build datasetgeneration functionality into a visual studio add-in that uses server explorer. I am building a crud generation utility and am using datasets to generate Create, Read, Update, Delete, Find & UI. Thanks couldn’t find much information about dataset generation on the net and your article and project really helped out.

    Let me know if youre interested in working on an open source code generation utility that generates C# code.

  3. jamesstory Says:

    Thanks Michael! I’m glad you learnt something – I’m in that state too – always learning 🙂 I have done some updates to the code available on the net (found a couple of bugs) but I’m really glad you liked it 🙂 It was just something I wacked together but have found it has saved me quite a bit of time as well!

    ps. Thanks for the blog comment 🙂

    James

  4. jamesstory Says:

    Hey Johann! Yeah it would definitelly be possible to put this in as a vs addin (and a good idea 🙂 ). I know addin’s aren’t always the easiest thing to write but it could definitelly be done. (I’m not sure if you know this so ignore it if you do :-)) but if you into Visual Studio and go new project->Extensibility there is a project type called Visual Studio addin. Thats the guy you want to start taking a look at 🙂 I would advise deploying it on a vm or on another instance of visual studio as they can be kind of fragile/a bit of a pain to get working 🙂

    Yeah I do a lot of work with a tool called MyGeneration which allows you to write custom templates to perform code generation (in particular around code files in a 1-1 relationship with database tables) – I finished a template a long time ago but I’m just restructuring it now… I’ll happily forward it on if you wanted to take a look – It’s used for generating NHibernate class files… I may not have time to work full on opensource project but flick me your thoughts and we’ll go from there 🙂

    All the best,

    James

  5. leo Says:

    Hi James,

    I read your article 4 times but sorry to say that i didn’t got as what do you concluded in the end and what is the use of the application(zip file) available for download.
    What i could understand is that you didn’t wanted to use xsd.exe.
    For understanding the application made by you, i have downloaded it and made the necessary changes in app.config file.Now when i run it, it gives NULLReference exception in tablewrapper.cs line 48.I am running it from vs.net.
    I don’t want to hurt your feelings by this posting and what i have stated here is actually what problem i am facing.
    Kindly throw some light on this
    regards

  6. Petro Says:

    hi, hi, hi! Beautiful site.

  7. Param Says:

    Excellent 🙂 It works cool…saves lot of time…It would be great if one could add “TableAdapter” as well 🙂

    Cheers…

  8. Kenji Says:

    Hi. Generator works great. Can be datasets generated with tableadapters using xsd.exe?

  9. Bond Says:

    Hi…Awesome stuff…now I just need to get it working…also it would be awesome if you could show us how to add a table adaptor. Please do.
    Thanks very much for this!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: