Today I wanted to write about good practices when working with ADO.Net. To begin with, it is important to correctly dispose of your ADO objects, as it can have a big impact on the performance against your precious database resources. There are plenty of articles and blogs on this topic to be found on the internet. So, today I just want to remind you about it’s importance, or introduce those who are just learning ADO techniques to do more research.
ADO utilizes a few objects to interact with a database. Generically, there is a connection object and a command object as the most basic tools. You will use an instance of a connection that works with your database engine. It could be an ADO object implementing the ADO specification, specific to your data engine. It could also be an ODBC object using an ODBC driver specific to your engine, or an OleDb object with an implementation for your database engine.
The purpose of the connection object is to manage a communication session with your data engine. The Command object is used to execute queries against the database engine by using a connection object to pass and receive queries and query results. With that in mind, the key thing to know is that when you are done with your command and connection objects, they must be disposed correctly.
USING, when coding in C#, is your friend when working with these ADO objects. The reason for this is that when you handle your ADO objects with USING it knows how to dispose of those objects in the most efficient way possible. Here is a very simple example of using these object to run an SQL command that doesn’t return anything from the database.
var sql = "UPDATE users set LastName = 'Taylor' WHERE UserId = 123";
using (var conn = new OleDbconnection(connectionString)
using(var cmd = new OleDbCommand(sql, conn)
{
cmd.ExecuteNonQuery();
}
By creating your connection and command objects in a Using clause results in the automatic creation, closing and destruction of each in the correct order. When it comes to the connection object this is important because it handles your database connectivity correctly if you are or aren’t using connection pooling. This can have a dramatic impact on your application performance.
While the example is simple, it provides you with enough information to do your own research for the implementation
specific to your situation.
Cheers,
Ben