Typed DataSets are a type safe wrapper around a DataSet which mirrors your database structure. It was created to make sure that the code accessing the database is type safe and any changes in the database structure that changes tables, columns or column types will be caught at compilation time rather than runtime.

If you have a big typed dataset that contains a lot of tables, columns and relations it might be quite expensive to create it in terms of memory and time.

The main reason that creating a big typed dataset is expensive is due to the fact that all of the meta data that is contained within the typed data sets (tables, columns, relations) is created when you create a typed dataset even if eventually all you’ll use it for is to retrieve data from a single table.

I can speculate that the reason all of the typed dataset meta data is created during the instantiation of the typed dataset is due to the fact that it inherits from a generic DataSet and accessing the meta data (tables, columns) can also be done in a non type safe manner (i.e. access the Tables collection and/or Columns collection of a table).

If you are using a typed dataset (or dataset in general) you might be interested in the following tips:

  • If you have a big typed dataset, avoid creating it too many times in the application. This is specifically painful for web applications where each request might create the dataset. You can use a generic DataSet instead, but this might lead to bugs due to database changes and the fact that you’ll only be able to find these bugs during runtime rather than compilation time (which basically misses the whole point of using a typed dataset in the first place).
  • DataSets (typed datasets included) inherits from the MarshalByValueComponent class. That class implements IDisposable which means DataSets will actually be garbage collected after finalization (you can read more about finalization and the finalizer thread here). To make sure datasets are collected more often and are not hagging around waiting for finalization make sure you call the “Dispose” method of the dataset (or typed dataset) or use the “Using” clause which will call “Dispose” for you at the end of the code block.
  • Don’t use DataSets at all 🙂 Consider using a different data access layer with a different approach such as the one used in the SubSonic project.

I guess it would be rather trivial creating a typed dataset that is lazy in nature which creates the meta data objects only when they are accessed for the first time. That would reduce the memory footprint of a large typed dataset but will make the computation used to create these objects a little less predictable. If you are up to it or already did a lazy typed dataset ping me through the contact form 🙂

If you are getting the error “Internal .Net Framework Data Provider error 6” and you are using SQL Server 2005 with database mirroring know that its a bug in the SQL managed client code.

Check out KB article 944099 for more info.

At the time of writing this post the only solution for this problem is a private hot fix that you need to obtain from Microsoft.

The links around the web for the Hotfix Request Web Submission form seems to be broken. I’ve contacted Microsoft and got this replacement link.

Use this link to contact Microsoft, state that you require the hotfix for KB article 944099 and submit the form. You will be contacted shortly by a Microsoft representative that will give you the download details or request further information to better understand your needs.

If you get the following error:

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Make sure that you are closing your connection. Specifically closing connections that you opened transactions from.

It seems the Connection Pool implemented in ADO.NET has two lists. One for connections that you did not open transaction from and the other for connections that a transaction was opened (and did not close, yet).

The two lists share the same amount of connections set in the “Max Pool Size” parameter passed in the connection string (the default is 100).

Be sure to use one of the following patterns and make sure to call connection.Close() when you are done with running your SQL statements, otherwise, it will take until the next run of the Finalizer thread to call Dispose on your connection object which will return it to the pool.

If your application is loaded it might take quite a while until the Finalizer thread runs and by then you will exhaust your connection pool and start getting mentioned above.

The two patterns used in this scenario are:

using (IDbConnection = new SqlConnection()) {



The “using” statement is only good for C# and it will call Dispose (which in turn will call Close) on your connection object.

Another pattern which is similar and gives you more control is this:

IDbConnection = new SqlConnection();

try {



finally {



The finally block gives you better control and can also be combined with a “Catch” block that you can gracefully handle various errors such as “Unique Key Violation” (if you application logic needs this kind of treatment).

If you really want to know that you have exhausted your connection pool and you forgot to run PerfMon (see my previous post about PerfMon) you can take a memory dump using adplus (you can read a little bit about adplus here) and use WinDbg and the SOS extension to look at the Connection Pool object.

Search for “ConnectionPool” and you’ll get various objects, one of them will be “DbConnectionPoolGroup” which has a property named “_poolCount” which can tell you the count of connections used on your pool. This will give you a rough estimate as to what is the status of your connection pool.


Sometimes when you have a big DataSet with elaborated relationships you might get the following error when trying to add or load data into the dataset:
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

Some of the causes for this error are usually “regular” violations of the foreign-key constraints, which means you are referencing a certain key that does not exist in the parent table. If that is the case, you can check this article on MSDN that explains a bit on how to resolve these issues.

If you are still having problems with your dataset and ADO.NET code, you might just want to try this little trick.
It appears that inside a DataRow there is a property called RowError.

RowError is a string value that can be set or read in numerous other occasions, but the situation in which I encountered was due to a bad relationship that was added on a table which caused the code to throw an exception at runtime. In that case the RowError property hold the exact name of the troublesome relationship.

So, how do you access it?
When you code at runtime throws an exception and you are with a debugger attached, check the following thing using the “immediate window” or Quickwatch:


Don’t forget to replace “myDataSet” with the variable name of your dataset and “YourTableName” with the table that is (probably) causing the problems.

In the case I’ve described above, this property told me exactly what is the problematic foreign-key that I had and from there I figured out what is the problematic relationship.