Working on "using" statement in ADO.NET

.net, ado.net, asp.net, c#

Solution

You don't need the `try/catch` if you're just going to throw it, just change your code to this:

public int Hello()
{
    using(SqlConnection con=new SqlConnection(constring))
    {
        using(SqlCommand cmd=new SqlCommand(Query,con))
        {
            con.Open();
            return cmd.ExecuteNonQuery();
        }
    }
}

and regardless of what happens, exception or not, the connection will get closed if it's open and disposed.

Problem

I want to properly dispose the SqlConnection object whenever i come out of the method. So im using the "using" statement as shown below. ``` public int Hello() { using(SqlConnection con=new SqlConnection(constring)) { using(SqlCommand cmd=new SqlCommand(Query,con)) { try { con.Open(); return cmd.ExecuteNonQuery(); } catch(Exception ex) { throw ex; } finally { con.Close() } } } } ``` Now, what i want to know is, Will the above code - Dispose the Connection properly when an Exception is occured in ExecuteNonQuery. - Make sure we will not get any ConnectionPool issues - Make sure the data is returned properly - If an exception occurs in SqlConnection will it dispose the object? Can anyone help me on this?

Original source