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:
- 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.
- 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
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.
- 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