Search This Blog


Tuesday, February 17, 2009

What's Wrong with Strongly Typed Dataset Designer

I might be beating a dead horse here.But strongly typed dataset was giving me so much hard time that I have to rant about it. Actually strongly typed dataset is quite handy if all you have to do is just need to query or update a single table. The problem is that the real world is far less ideal than that. When you apply a suboptimal tool on a complicated, messy real world situation you are going to face a lot of problems. And this was exactly what happened when I was using strongly typed dataset designer in my work.

Before you ask me why I didn't want to use a more proper ORM such as LINQ to SQL , or Microsoft ADO.NET Entity Framework , let me preempt the question by offering the answer here: my project used Microsoft Access as the back end, and regretably, the two don't offer the support for MS Access, yet.

Now for the rant:
  1. No synchronization between the actual database schema and the datasets
    Synchronization between actual database schema and the ORM layer is important, especially when your database is still early in the prototyping stage and subject to change. It is also very useful when you upgrade your database schema because you don't have to manually update the datasets. But unfortunately the strongly typed dataset designer doesn't provide this functionalities. So everything I change my column name, or I add a new column to a table, I have to reconstruct the dataset. Of course I change manually change the schema in the dataset designer, but that's not a good practice anyway because who knows what other changes I did.  

  2. Cannot perform join table queries
    It is incredible that I can't preform join table queries in the designer. First the select query type is broken. To see what I mean, try the following query:

    And the code behind is this function

    public virtual string ScalarQuery() ;

    Digging into the code, it seems that what gets return is

    command.ExecuteScalar() ;

    So I can only get the first row of the SELECT statement.. obviously the select command isn't working.

    What about SQL INSERT?

    Sadly, it isn't working as well. You can't insert with condition across different tables. In fact, the designer won't even allow you select different tables when configuration the INSERT command.
  3. Non standardized  query syntax

    SQL uses prefix '@' to denote parameter. For example, the following syntax is valid in MS Access

    UPDATE table1 SET
    table1.nationality = @nationality

    Sadly the syntax above doesn't work for dataset designer. The correct syntax would be

    UPDATE table1 SET
    table1.nationality = ?

    The IDE will parse the '?' properly according to the order of appearance. So you can still get a nicely wrapped up function for that.

    This kind of language idiosyncrasies are not hard to learn, but they can be extremely frustrating when you can't figure them out. I wonder why Microsoft has to come up with different notations


Jef Claes said...

Yep typed datasets are evil in general.

Danish Shamim said...

yes, but i read something online and tried it, at least you can update the schema by right click, going into configure and selecting deselecting the columns as wanted - this updates the schema as well in VS 2008

mateiacd said...

Does the link below help you ?

The strongly typed dataset designer has other problems as well.

See below such a problem:

Blogger said...

The ? for parameters in sql in the designer is only for Oracle databases. MS SQL Server uses @. I've been using typed datasets every day since 2002 and I always use @ for parameters in the sql in the designer when hitting MS databases. As a matter of fact, I never even saw the use of "?" until 2006 or 2007 when I had to maintain some old Oracle based apps.

Anonymous said...

Did any of the MS "programmers" had a thought through the whole DataSet Designer? Never...
Maybe in the early stages when someone figured out this might be a nice tool to have.

But then the lack o time and hmm... must say knowledge took over.

Anything you can do now is just rebuild the damn thing from scratch, and create your own DataSet Builder as I did.

Suits my needs at least.

TypedDataTables are really cool if you know what you are doing.