practices - using connection c# close




Correct way to close database connection in event of exception (4)

Does the following code leave the connection open if there is an exception?

I am using a Microsoft SQL compact edition database.

try
{
    SqlCeConnection conn = new SqlCeConnection(ConnectionString);

    conn.Open();

    using (SqlCeCommand cmd =
        new SqlCeCommand("SELECT stuff FROM SomeTable", conn))
    {
      // do some stuff
    }

    conn.Close();
}
catch (Exception ex)
{
    ExceptionManager.HandleException(ex);
}

Surely a better way would be to declare a connection object before the try, establish a connection inside the try block and close it in a finally block?

 SqlCeConnection conn = null;
 try
 {
    conn = new SqlCeConnection(ConnectionString);

    conn.Open();

    using (SqlCeCommand cmd =
        new SqlCeCommand("SELECT stuff FROM SomeTable", conn))
    {
      // do some stuff
    }
}
catch (Exception ex)
{
    ExceptionManager.HandleException(ex);
}
finally
{
    if( conn != null )  conn.Close();
}

The way you are handling SqlCeCommand in your code with the help of a using block, you could do the same for the SqlCeConnection.

SqlCeConnection conn;
using (conn = new SqlCeConnection(ConnectionString))
{
   conn.Open();
   using (SqlCeCommand cmd = 
       new SqlCeCommand("SELECT stuff FROM SomeTable", conn))
   {
   // do some stuff
   }
}

Note: You can use a using block for classes that implement IDisposable.

EDIT: This is same as

try
{
    conn = new SqlCeConnection(ConnectionString);
    conn.Open();

    SqlCeCommand cmd = conn.CreateCommand();
    cmd.CommandText = "...";

    cmd.ExecuteNonQuery();
}
finally
{
    conn.Close();
}

ref: http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection%28VS.80%29.aspx


Use Using

using(SqlConnection conn = new SqlConnection())
{
//put all your code here.
}

You Have To Try Following Way. Because Connection Close In Finally Block

try
{
    SqlCeConnection conn = new SqlCeConnection(ConnectionString);

    conn.Open();

    using (SqlCeCommand cmd =
        new SqlCeCommand("SELECT stuff FROM SomeTable", conn))
    {
      // do some stuff
    }
}
catch (Exception ex)
{

}
finally
{
\\close connection here
}





database-connection