Ado.Net Closing Connection When Using "Using" Statement

ado.net Closing Connection when using using statement

The using will take care of it for you. Under the hood, SqlConnection.Dispose() calls the SqlConnection.Close() method, and SqlCommand.Dispose() calls SqlCommand.Close().

As additional background, a using statement is syntactic sugar for a try ... finally that disposes the IDisposable object in the finally.

Working on using statement in ADO.NET

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.

Why is my using statement not closing connection?

When you call SqlConnection.Dispose(), which you do because of the using block, the connection is not closed, per-se. It is released back to the connection pool.

In order to avoid constantly building/tearing down connections, the connection pool will keep connections open for your application to use. So it makes perfect sense that the connection would still show as being open.

What's happening after that, I can't explain offhand - I know that keeping a random connection open would not cause that, though, because your application can certainly make more than a single concurrent connection.

Will return keyword inside using statement, leave the connection open?

Does the return keyword inside the using statement leaving the connection to opened and hence this issue?

No. The using statement is effectively a try/finally block statement a Dispose call in the finally part - so your connection will still be disposed at the end for the method.

I suspect that either you're just calling this from too many threads at the same time and exhausting your pool that way, or you're opening a connection elsewhere without closing it.

Note that you can make your code simpler by getting rid of the i local variable:

public int ExecuteNonQuery(SqlParameter[] param, string strSPName)
{
    using (SqlConnection conn = new SqlConnection(_connStr))
    {
        using (SqlCommand cmd = new SqlCommand(strSPName, conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);
            conn.Open();
            return cmd.ExecuteNonQuery();
        }
    }
}

Again, the command and connection will still be disposed appropriately.

using Statement And ADO.NET Objects

Objects will be cleaned up when they are no longer being used and when the garbage collector sees fit. Sometimes, you may need to set an object to null in order to make it go out of scope (such as a static field whose value you no longer need), but overall there is usually no need to set to null.

Regarding disposing objects, I agree with @Andre. If the object is IDisposable it is a good idea to dispose it when you no longer need it, especially if the object uses unmanaged resources. Not disposing unmanaged resources will lead to memory leaks.

You can use the using statement to automatically dispose an object once your program leaves the scope of the using statement.

using (MyIDisposableObject obj = new MyIDisposableObject())
{
    // use the object here
} // the object is disposed here

Which is functionally equivalent to:

MyIDisposableObject obj;
try
{
    obj = new MyIDisposableObject();
}
finally
{
    if (obj != null)
    {
        ((IDisposable)obj).Dispose();
    }
}

Got this from Zach Johnson here: Credit Due

Do i need to close the connection when using parametrised connection

Yes you need always to close the connection. Also you don't need the first if.

using(SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{

    con.Open();

    string sql = "INSERT INTO RegisterUser(Name,LastName,email,Nationality,Country) VALUES (@param1,@param2,@param3,@param4,@param5)";

    SqlCommand cmd = new SqlCommand(sql, con);
    cmd.Parameters.Add("@param1", SqlDbType.NVarChar, 200).Value = txtName.Text;
    cmd.Parameters.Add("@param2", SqlDbType.NVarChar, 100).Value = txtLastName.Text;
    cmd.Parameters.Add("@param3", SqlDbType.NVarChar, 50).Value = txtEmail.Text;
    cmd.Parameters.Add("@param4", SqlDbType.NVarChar, 50).Value = ddCountry.SelectedItem.Value.ToString();
    cmd.Parameters.Add("@param5", SqlDbType.NVarChar, 50).Value = txtCountryCode.Text;

    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
}

So using will automatically close your connection for you. This will happen even if your code throws an exception. using represents try/catch/finally block. In this way you are guaranteed that the connection is return to Connection Pool if error happens.

try
{
    Sqlconnection conn = new SqlConnection("your conn string");
}
catch(Exception ex)
{
    throw;
}
finally
{
    conn.Close();
}

---

Related Topics