CODEWITHSUNDEEP
c#sql
Chandru velan
Chandru velan

Reputation: 146

What will happen if Connection close event called inside the Using Statement in C#

My assumption is if Sqlconnection opened With in Using Statement, dispose event will takes place automatically after execution of statement, if i forced to close the connection with in the Using Statement what will happen is that bad Practice ?

using (SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["Constring"]))
{
    // SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["Constring"]);
    conn.Open();

    String query = String.Empty;
    query = "exec " + StoredProcedure + " " + sampleproc + "";

    using (SqlDataAdapter mainTableAdapter = new SqlDataAdapter(query, conn))
    {
        System.Data.DataSet mainTableDS = new System.Data.DataSet();
        mainTableAdapter.Fill(mainTableDS);

        conn.Close();
        mainTableAdapter.Dispose();
        conn.Dispose();

        return mainTableDS;
    }
}

Upvotes: 1

Views: 169

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415725

I'm only adding this as an answer because it won't fit in the comments, but in addition to everything in the other answer, you also don't need to call conn.Open(). The Fill() method will take care of it for you. The whole 20 line sample above can be reduced to this brief 8 line snippet without any loss of function:

var mainTableDS = new DataSet();
String query = String.Format("exec {0} {1}", StoredProcedure, sampleproc);  //possibly vulnerable to SQL Injection attacks!
using (var  conn = new SqlConnection(ConfigurationSettings.AppSettings["Constring"]))
using (var mainTableAdapter = new SqlDataAdapter(query, conn))
{
    mainTableAdapter.Fill(mainTableDS);
}
return mainTableDS;

Upvotes: 1

Justin Helgerson
Justin Helgerson

Reputation: 25521

  1. There's no need to call both Close() and Dispose(); calling Dispose() will automatically close the connection and dispose of the object.

  2. There's no need to call Close() or Dispose() inside the using block. The using statement automatically disposes of the object (and because of #1 above also closes the connection).

Also, a note about #2 above. It should be safe to call Dispose() multiple times if Microsoft's guidance is followed:

If an object's Dispose method is called more than once, the object must ignore all calls after the first one. The object must not throw an exception if its Dispose method is called multiple times. Instance methods other than Dispose can throw an ObjectDisposedException when resources are already disposed.

Upvotes: 2

Related Questions