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 :
- You could always change the MaxLength on VarChar / NVarchar to restrict user when inputing data to database.
- 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.
- 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 :
- Create a variable to store the data if exists, fill the variable with null.
- 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!.