Is CommandBehavior.CloseConnection enough to close connection if exception occurs?

Posted on 2009-05-26
Medium Priority
Last Modified: 2012-05-07
I have a method that opens a DataReader. When obtaining the DataReader via the SqlCommand.ExecuteReader method I use the command behavior "CommandBehavior.CloseConnection" to make sure the connection is closed when the reader is closed.

  But what happens if an exception iccurs during the "SqlCommand.ExecuteReader" statement? What happens to the Sql Connection? Do I need to place a SqlConnection.Close() explicitly in the "try .. catch .. finally" so that SqlConnection is closed properly? (Example below)
SqlDataReader sdr = null;
   string sql = <MY SQL STATEMENT>;
   sqlConn = new SqlConnection(this.SqlConnectionString);
   SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
   sqlCmd.CommandType = CommandType.Text;
   sdr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
   return sdr;
catch (Exception ex)
   if (sqlConn != null && sqlConn.State == ConnectionState.Open)

Open in new window

Question by:thecodingflea
LVL 15

Assisted Solution

mohan_sekar earned 200 total points
ID: 24474172
Yes, close and free resources in the finally block

Author Comment

ID: 24474191
So I am assuming then that it is not enough to rely on CommandBehavior.CloseConnection to close the database connection? Thanks.
LVL 14

Accepted Solution

jjardine earned 800 total points
ID: 24474265
use the using block for this....

using (SqlConnection sqlConn = new SqlConnection(this.SqlConnectionString))
    All your cmd and datareader stuff in here.   You could use the using block with the Command object as well.

The using block automatically calls the dispose method of your object.  So if your reader threw an exception, before leaving the using block, the connection's dispose would get called, which by default closes the connection.
LVL 12

Assisted Solution

GuitarRich earned 800 total points
ID: 24474447
the using object will only be useful if any databinding or using of the data reader is inside that using block. You are correct in using the CommandBehaviour.CloseConnection - this will allow you to return the data to the code behind in the UI, bind it and then clean it up there. If you try to return the SqlDataReader while inside the using block you will get an exception as soon as you use it as the connection will no longer be open.
Its not enough to rely on that to close the connection in the event of an exception, because the error more than likely is going to stop the data reader being initialised and loaded properly. So you will need to do it in the finally block as you have done.
LVL 26

Assisted Solution

by:Anurag Thakur
Anurag Thakur earned 200 total points
ID: 24483422
i will be using what jjardine is suggesting becaue even when an exception will occur and the control jumps out of the using block the connection will be disposed automatically
read the following article and it explains in detail how will the connection object disposes

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question