ADO.NET is a data access technology from the Microsoft .NET Framework that provides communication between relational and non-relational systems through a common set of components. This post is intended to cover the proper usage of the SqlDataReader and indirectly connection management and performance. It is also geared towards SQL Server, but concepts can be generally applied for other databases.
A bit on database connections. Database connections are expensive resources and typically consist of several time-consuming steps. Therefore, a simple rule to follow is to open a connection as late as possible, close it as soon as possible, and rely on Connection Pooling to do the rest.
I often see legacy and even new code that does not close or dispose of ADO.NET objects that implement the IDisposable Interface. Here is an example of bad code:
What is wrong with this code? Well, a lot of things. Let's get into it.
The SqlConnection and SqlDataReader both implement the IDisposable Interface because both types use unmanaged resources. Unmanaged resources are objects that wrap operating system resources, such as files, windows, network connections, or database connections.
Let's re-write the code by wrapping the ADO.NET objects in using statements.
The re-written code is now much cleaner. The using statement simplifies the code that you must write to create and clean up an object. Behind the scenes, it obtains one or more resources, executes the statements that you specify, and automatically disposes of the object. However, the using statement only applies to objects that are used within the scope of the method in which they are constructed.
In this post, I went over the correct pattern to ensure the correct disposal of ADO.NET objects that implement the IDisposable Interface. A final note on the using statement - if you want to catch any errors then you'll need to wrap everything in a try-catch block. Using blocks simply ensures that non-managed resources are disposed of. They cannot handle exceptions.