ASP.NET Typed Dataset Common Problem

If you’re new to ASP.NET you will introduced to Typed Dataset, what the heck is that? It’s feature which available in Visual Studio. I assume you already understand how to create Typed Dataset.

Most of programmer tell me that Typed Dataset is very fragile to any modification. Yes, indeed it is. Why I have such conclusion? It started when I starting to use Typed Dataset. The famous “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.” error will make you desperate and force you to write a class to accesing your table. If you think it’s simpler to write your own code than automatically generated by ASP.net, please do so. It will save your time a lot.

Well, some people will find the answer why it happen to their Typed Dataset. Typed Dataset saves our time to write a Data Access Layer (DAL) class, really!. I just run a wizard the voila you could use it in any database component in your Visual Studio toolbox. Okay, I admit it’s not easy when you found an error in dataset. Okay, let’s start!

First, I found that we must consistent in any SELECT command to query (TableAdapter). Let’s take simple case, you start with this SELECT command :

SELECT username, realname FROM myUsers

When you want to filter using WHERE clause for the user by userID then your query must like this :

SELECT username, realname FROM myUsers WHERE userID = @userID

Did you see what the same? yes! the field must exact like the first query when we create new TableAdapter. Why? it will save you from unnecessary error. I don’t know the technical reason, because this tool is really complex.

Secondly, stay away from Table Changes. When developing process often we add or change the column in a table, because many reasons. Often people meet the famous error that I mention above after changing the table and the corresponding DataTable/Table Adapter in Dataset. By trial and error I found several solution to this :

1. You could always change the MaxLength on VarChar / NVarchar to restrict user when inputing data to database.
2. If you want varchar data type to ntext / text, please user varchar(MAX) / NVarChar(MAX).  Then set the MaxLength with -1 value to allow release the length restriction. You will void the famouse error by stay using NVARCHAR(MAX) rather than NTEXT.
3. Plan your database and the queries to heart before starting to build Typed DataSet … hehehe. What else better?

Thridly, DataType Conversion Error. The error often show like “System.InvalidCastException: Unable to cast object of type ‘System.DBNull’  to type ‘System.String’.“. How you get rid of this? don’t worry, it’s ASP.NET fault not the DataSet. You could this :

1. Create a variable to store the data if exists, fill the variable with null.
2. Check your field value

Example in C# :

string _realName = null;
If(!myUsers.IsRealNameNull()) _realName = myUsers.RealName;
labelRealName.Text = _realName;

myUsers is the TableAdapter, and IsRealNameNull() was automatically generated by ASP.NET.

Hopes this journal will help you out, I found many problem like this. Microsoft must fixing this Typed Dataset complexity into simpler form that human and developer understood.

Happy Programming, Folks!.