Why People Don’t Use Data Readers

A problem with data readers is their connected nature. Every data reader has one database connection tied to it. With .NET 1.X the limitation is one open data reader per database connection, while .NET 2.0 brings new features which allow more than one active result set per connection, but the connection still exists. This means that passing a data reader to the client means also passing the responsibility of closing the data reader and the connection.

Developers also tend to avoid using data readers because they are read-only, forward-only and therefore a bit unsuitable for complex calculation scenarios which might involve the need to access data more than once. This means that with data readers you would need to keep the connection and reader open for a longer time or reacquire the result set, increasing the time the connection is in use.

Using Delegates to Control DAL Responsibility

Delegates are a way to reference methods based on their signature and return type, allowing an instantiated delegate to reference a given method assuming that the method matches the delegate definition. Delegate instances can be passed as method parameters which means that methods themselves can be passed and again invoked by the class/method which receives the delegate as an argument.

Delegates are one solution to the previously mentioned responsibility problem with data readers. In most common data binding scenarios an IDataReader instance is pulled from the DAL, assuming data readers and the data binding are in use, resource releasing is left up to the client. With delegates we can turn this so that a data binding method taking an IDataReader instance as a parameter, is itself given as a parameter to the DAL. With this idea the responsibility of cleanup is up to the DAL, because DAL invokes the delegate instance, waits for it to finish execution and then closes the data reader and the database connection.

TO BE CONTINUE......