Updating and Inserting Data with ADO.NET and ASP.NET 2.0 - Closing SqlConnection and SqlDataReader objects through the using block
(Page 2 of 4 )
In C# we can make sure that any object that has a Dispose() method, by implementing the IDisposable interface, can be disposed of with the using block. The using block is passed a reference to an object that is used inside the block; when the execution of the block's code completes, the object's Dispose() method is called. Note that the Dispose() method will also be called in the event of an exception; in fact the Dispose() method will be called no matter what happens. Both the SqlConnection and the SqlDataReader implement the IDisposable interface so they have a Dispose() method defined and implemented. The Dispose() method implicitly calls the Close() method which is exactly what we need. Let's see the modified version of the above example that uses this technique.
Replace the Page_Load() event handler with the following:
protected void Page_Load(object sender, EventArgs e){
// create the connection and set the connection string
// through the connection object's constructor
string connectionString = "Data Source=(local);
Initial Catalog=Northwind;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionString);
// create the command object and the T-SQL SELECT statement
// and assign the connection object along with the T-SQL
// SELECT statement to command object through the constructor
string commandText = "SELECT LastName, FirstName FROM
Employees";
SqlCommand command = new SqlCommand(commandText, connection);
// we have initialized the connection and the command so it's
// time to open the connection and execute the command
// using a try/catch block to handle exceptions
SqlDataReader dataReader;
try{
// check if the connection is already open
if (connection.State == ConnectionState.Closed){
connection.Open();
}
// make sure that the connection is closed after we finish
// using it
using (connection){
// get a SqlDataReader object from the Command's
// ExecuteReader() method and make sure that the data
// reader is closed too
using (dataReader = command.ExecuteReader()){
// adding the returned rows to the listbox control
while (dataReader.Read()){
ListBox1.Items.Add(dataReader["LastName"] + ", " +
dataReader["FirstName"]);
}
}
}
}
catch (Exception ex){
Label1.Text = ex.Message;
}
}
What we have added in this example is two using blocks, one for the connection object and the other for the data reader object. Note that you can create the object with the using block like so:
using(SqlConnection connection = new SqlConnection(connectionString)){
connection.Open();
// execute the command and return the data reader
}
// here the SqlConnection.Dispose() method will be called
// which in turn calls the SqlConnection.Close() method
But in our example we passed a reference to the connection object because we wanted to test whether or not the connection is open before we use it. We have also used another nested using block with the data reader object to make sure that it's closed when we finish using it. Actually if we don't close the SqlDataReader object, the SqlConnection object can't be used for anything else because it's busy serving the SqlDataReader object, so it's very important to close the SqlDataReader object. We have removed the finally block because it's not needed anymore; we have the using block which closes the connection, and the data reader, when it finishes executing its statements.
Next: Inserting and Updating Data using the SqlCommand >>
More ASP.NET Articles
More By Michael Youssef
