asked on

Details about specific commands to close SQLConnection class

Dear experts,

I need a little help for a question connected with Net3.5 SQLConnection class. When I finish usages of SQLDataReader and SQLConnection I know that I should release the recourses, my question is for detail command I should use.

I have 2 SQLDataReaders and 1 SQLConnection – now I use a code like this
readerCamp.Dispose(); reader.Dispose(); conn.Dispose();

The right questions I have are:
1.      Do I really need to close SQLDataReaders, or just conn.Dispose is enogh because it will close on himself all relative connection /SQLDataReaders which used it/
2.      The command conn.Dispose(); is equal to conn=null; - true or not?
3.      Long time I use code like:
if (conn.State != ConnectionState.Closed) conn.Close();
conn.Dispose();

is that necessary? If the result is same prefer use just conn.Dispose();

Guy Hengel [angelIII / a3]

closing the connection will NOT close any data reader or dataset.

apart from that, you may want to use the USING syntax:

using ( SqlConnection cnn = new SqlConnection("connection string") )
{
   cnn.Open();
   using ( SqlCommand cmd = new SqlCommand("select ... ", cnn))
   {
      using (SqlDataReader dr = cmd.ExecuteReader())
      {

      }
   }
   cnn.Close();
}

Open in new window

the "using" will run a dispose automatically at it's end.
of course, you shall put some try{} catch {} handlers inside the different brackets, but that is another part of the programming

dvplayltd

ASKER

I use using on all places where this is practical - but in most place it isn't.

Till now you answer only to my first question - i should close the reader object too, not just its connection. What about other two questions ?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dvplayltd

ASKER

10x. I'm starting programming before 13 years with VB6 and C, so I understand the difference between conn=null and conn.Dispose() as you kindly explain me.

So I'll use conn.Close(); and then conn.Dispose(), only will not chek this if (conn.State != ConnectionState.Closed) , i think even the connection is not Open Close() will not produce error, right?

Guy Hengel [angelIII / a3]

>ConnectionState.Closed) , i think even the connection is not Open Close() will not produce error, right?

if connection is closed, connection.Close() will raise an error.

dvplayltd

ASKER

Thanks for your last note, i fix my code with global Search and Replace. It show - 55 copies replaced ...

I say it is not practical to use Using clause as I should have 2-3 levels of nasted code in 4-6 screen pages ... it will make worst reading of the code.
bye and have a nice day!