Thursday, August 9, 2007

Why you shouldn't use Typed Dataset...!

When working with persistent layer in .NET environment, you feel something is needed like hibernate or ibatis as a data access layer which automatically makes relations between tables and object, and give us an opportunity to call stored procedures and queries like we call methods of an object. Although hibernate is ported into .NET version, i don't trust its performance and other issues because of why i don't know. Then i looked around for different solutions and found bingoo Typed Dataset. If you haven't used it or know it, you can have a quick check here; Using Strongly-Typed Data Access in Visual Studio 2005 and ASP.NET 2.0. It has many nice sides such as simplifying many things, lessening code size, fastening development time in persistent layer etc... I have implemented all my tables, stored procedures and views using this method. It was really fun to make them. Click, Next, Select Ding dang finish. And it works fine. But after some time, i begin to see the disadvantages and problems of it. So let me list these problems and warn you not to do the same mistakes i did.

  • Database name change
    You created your typed data access layer with wizards. It is okay. But it saves database name, db username information in XSS files, means when you try to deploy your application, you have to use the same database name in a production server. You cannot change the name of the database. Big problem it there is a database with the same name in our production table, or when you try to create two instance of the application which is not possible.
  • Table structure change
    It is common to change the database tables' structure depending on new requirements or because of the wrong analysis. Maybe we need to change the data type of a column of a table, or length or size of a column. So if you change the table's structure you have also to change your entire typed datasets which are related to that table, which is a big headache. Everytime you change table structure, you also need to change dataset.
  • Database username
    I suggest you to create your tables and stored procedures under the name "dbo", not custom user. If you create your stored procedure as a "customuser.SelectProducts" instead of "dbo.SelectProducts", the username "customuser" is stored in XSS files of typed dataset. So you cannot change the user name of the database in any place in the future. Same problem again like "Database name change"
Finally, i want to say that i am writing these problems because i didn't find the solution myself. i will be glad if you share with us, if you know the way how to handle these problem.

Hope it help ;)

See Also;


FlySwat said...

For #1 -
When using the TableAdapter wizard, simply point it to your connection string in the web.config or app.config file and it will use whatever is placed in there at runtime.

For #2 -

This is a no brainer, and a problem present regardless of your approach to implementing a DAL. The only real answer (regardless of how you built your database layer) is to try and get the best specification possible and plan ahead for the future in your table design.

For #3 -

It does seem like a bug how the wizard creates Stored Procedures. You can use the script here to fix it:

Or, you could be like me, and avoid stored procedures like the plague, and instead put their logic into the DAL.

Happy Coding.

Bayarsaikhan said...

Thanks Flyswat for the hacks and tips.

For #1,
It didn't work for me

For #2,
I must be intelligent enough in designing my tables. Nobody is perfect or can see the future changes completely. More or Less, there must be change in db schema.

For #3,
So It is a bug ;) I cannot change my schema after i compiled my project.

I will not use Typed Dataset in my future projects.

Anonymous said...

Why you shouldn't use typed dataset? That's like saying "why you shouldn't use electricity". Object-relational mapping (ORM) has been around since the early 90's - and so have the issues you described. If you are just learning about this now you are a decade late to the game.

Bottom line: using a code generator for the DAL will save 40-60% of coding. This is such a huge productivity gain that it outweighs any other issues (db schema changed? yeah, no shit I expect a code change too). Try manually writing the DAL on 2,000 table enterprise database - it just isn't done anymore (or hasn't been done for a decade).

Anonymous said...

i don't know if i'm missing something here but AFAK the XSS file is used for storing dataset designer's layout information only!! why you just open the XSD file outside VS and rename all the schema/DB name there?

bayarsaikhan said...

Yes i can change it using my VS ide during DEVELOPMENT. You cannot change it after COMPILATION. I mean you have to COMPILE IT AGAIN EVERY TIME you change the db structure or name etc...

Anonymous said...

I mean you have to COMPILE IT AGAIN EVERY TIME you change..

THAT is always TRUE event if you don't use typed dataset!!

bayarsaikhan said...

ok. lets assume that i have compiled my project and deployed it on the production server. it works fine.

now i wish to deploy the same application with the different database but same tables and procedures. in this case, i again have to go back to my source files and change my db name and compile again.

i just want to change it from my web.config and everything would run. but it is not. you have to re-compile it again when your db name is changed. you got me? or ama i wrong?

bayarsaikhan said...

If i coded my DAL layer with SQL statements etc, i dont have to compile it when my db name is changed.

Anonymous said...

"i just want to change it from my web.config and everything would run. but it is not. you have to re-compile it again when your db name is changed. you got me? or ama i wrong?"
YES, you are wrong, because you can do just that with typed dataset