When and where to call dispose method c#?

Question

I have this below code to retrieve data from db and when I run the Code Analysis from Visual Studio, it suggests me to call dispose method on SqlConnection, DataTable and SqlDataAdapter objects.

        SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString);
        SqlCommand cmd = sqlconn.CreateCommand();
        cmd.CommandText = "SELECT * FROM tbl_serial WHERE serial = @serial";
        cmd.Parameters.AddWithValue("@serial", txtQuery.Text);
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter();
        try
        {
            sqlconn.Open();
            da.SelectCommand = cmd;
            da.Fill(dt);

        }
        catch (SqlException ex)
        {
            lblStatus.Text = ex.Message;
        }
        finally
        {
            sqlconn.Close();
        }

        if (dt.Rows.Count > 0)
        {
            lblStatus.Text = "FOUND!";
        }
        else
        {
            lblStatus.Text = "NOT FOUND!";
        }

And this is my first time doing this, I called dispose method on sqlconn just like this -

        finally
        {
            sqlconn.Close();
            sqlconn.Dispose();
        }

But then, Visual Studio warns me like this -

CA2202 : Microsoft.Usage : Object 'sqlconn' can be disposed more than once in method 'test_search.Unnamed1_Click(object, EventArgs)'. To avoid generating a System.ObjectDisposedException you should not call Dispose more than one time on an object.: Lines: 41

So I would like to know when should I correctly call dispose methods.

Answer 1

Rather than calling dispose directly, use the using statement, which will make certain to do it for you when the code execution moves out of its associated block. E.g.:

SqlConnection con;

using (con = new SqlConnection(/* ... */)) {
    // Do your stuff with `con`
}

This is the pattern to use with disposables in general.

Answer 2

Here is your code re-written without ever having to all Dispose/Close manually

using (var sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
using (var cmd = sqlconn.CreateCommand())
{
    cmd.CommandText = "SELECT * FROM tbl_serial WHERE serial = @serial";
    cmd.Parameters.AddWithValue("@serial", txtQuery.Text);
    using (var dt = new DataTable())
    using (var da = new SqlDataAdapter())
    {
        try
        {
            sqlconn.Open();
            da.SelectCommand = cmd;
            da.Fill(dt);

        }
        catch (SqlException ex)
        {
            lblStatus.Text = ex.Message;
        }

        lblStatus.Text = dt.Rows.Count > 0 ? "FOUND!" : "NOT FOUND!";
    } 
}

Answer 3

Best way to handle connection disposing is USING

So your code will become like this,

  using(SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
  { 
      code here..
  }

since Sqlconnection implements IDisposable, using block will automatically dispose object after call completes.

Answer 4

sqlconn.Dispose() is just redundant in that finally block.A Close() in effect has called Dispose() and that's why the specific call to Dispose is throwing the error.

There are subtle differences between Close and Dispose.

• Calling a close multiple times will not throw Exception.
• Calling a dispose more than once will throw Exception.

In your case its very clear that the close has in turn invoked the Dispose. Your second call to Dispose therefore is causing the exception.

Always use USING as its the better approach.

Answer 5

using(SqlConnection sqlconn = new SqlConnection(ConnectionStrings))
{ 
   //Automatically this block will disposes the SqlConnection
}

or 

you can dispose all your objects in Finally Block

Answer 6

I agree with the others on using it definitely is best practice to use this. However, you should change your finally block to this:

finally
{
    if (sqlconn != null)
    {
        if (sqlConn.ConnectionState == ConnectionState.Open) sqlconn.Close();
        sqlConn.Dispose();
        GC.SuppressFinalize(sqlConn);
        sqlConn = null;
    }
}

With GC.SuppressFinalize you are telling the garbage collector to not bother disposing of sqlConn since you've disposed of it already. While it is possible to do this, I've always believed best practice is to implement IDisposable on your object and handle all clean ups in the Dispose method of the contract - also calling GC.SuppressFinalize(this) on your object.

public class MyObject : IDisposable
{
    private SqlConnection _sqlConn;

    public MyObject()
    {
        _sqlConn = new SqlConnection("connection string");
        _sqlConn.Open();
    }

    void IDisposable.Dispose()
    {
        if (_sqlConn != null)
        {
            if (_sqlConn.ConnectionState == ConnectionState.Open)
            {
                _sqlConn.Close();
            }

            _sqlConn.Dispose();
            _sqlConn = null;
        }

        // tell the garbage collector to ignore this object as you've tidied it up yourself
        GC.SuppressFinalize(this);
    }
}

This previous SO post has the best answer to this question.

Answer 7

Hi it is good to write dispose and close statment in finaly as it will excute if there is exception occurd in the code.

finally
{
   sqlconn.Close();
   SqlConnection.ClearAllPool();  // this statement clears pool
   sqlConn.Dispose(); 
}

when you close or dispose connection it will not close connection at same time. it will only get close or cleared connection pool only when appdomain is refreshed

Answer 8

You probably want to format your code like this:

using (var sqlconn = new SqlConnection(...)){
  try {
            sqlconn.Open();
            da.SelectCommand = cmd;
            da.Fill(dt);
  }
  catch (SqlException ex) {
            lblStatus.Text = ex.Message;
  }
}

    if (dt.Rows.Count > 0)
    {
        lblStatus.Text = "FOUND!";
    }
    else
    {
        lblStatus.Text = "NOT FOUND!";
    }

This way, you let the using syntax take care of disposing/closing your connection and your code focuses on the logic that's important to you.