Use of using to dispose resources

Question

Possible Duplicate:
Trying to understand the ‘using’ statement better

I've really read all other posts, but no one really answers my question.

This is my function that returns a table

        public DataTable ReturnTable()
        {
            DataTable dt = new DataTable();   
            using (SqlConnection con = new SqlConnection(mainConnectionString))
            {
                con.Open();                             
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandType = CommandType.Text;    
                    SQL = " SELECT * from table";                        
                    cmd.CommandText = SQL;                                            
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }
                }
            }           
            return dt;
        }

what's the advantage of the previous against the following (the one that I always used prior to discover 'using'):

public DataTable ReturnTable()
            {
                DataTable dt = new DataTable();   
                SqlConnection con = new SqlConnection(mainConnectionString);
                con.Open();                             
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;    
                SQL = " SELECT * from table";                        
                cmd.CommandText = SQL;                                            
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                con.Close();
                return dt;
            }

With the second, aren't con, cmd and da properly disposed? Is there anything wrong with the second?

Thanks!

The connection object is the most important one to close or dispose since it holds physical database resources. It will not be closed until the GC finalizes the object in the second case if there is an exception. Disposing the command and the data adapter is a really good idea but not as critical. — Dmitry S., Dec 15 '12 at 03:58

score 4 · Answer 1 · answered Dec 14 '12 at 23:29

Is there anything wrong with the second?

As long as you don't get an exception in your code, this will work the same way for con, since Close() and Dispose() are effectively the same in this case. It will not dispose of da or cmd immediately, and will wait until they are garbage collected to free their resources.

That advantage of using is that your resource is still disposed even in the case of exceptions or early exit from the method (you adding a return in the middle of your method).

Robert Harvey · Answer 2 · 2012-12-14T23:34:26.133

0

The advantage is that the using pattern calls the Dispose() method on the IDisposable interface, guaranteeing that any cleanup logic you may have missed gets properly executed, even if an exception is thrown.

In practice, objects implementing IDisposable are holding onto unmanaged resources which get cleaned up when Dispose() is called. So calling Close() may not be sufficient.

edited Dec 14 '12 at 23:34

answered Dec 14 '12 at 23:28

Robert Harvey

178,213
47
333
501

score 0 · Answer 3 · answered Dec 14 '12 at 23:29

0

No, they're not the same. That's the whole point of using.

What happens if your code throws an exception? Those objects won't be disposed until the garbage collector happens to get around to it.

answered Dec 14 '12 at 23:29

John Saunders

160,644
26
247
397

score 0 · Answer 4 · answered Dec 15 '12 at 03:52

0

"using" in in this case tries to implement the RAII pattern, which is especially useful when dealing with limited resources such as database connections.

answered Dec 15 '12 at 03:52

Zar Shardan

5,675
2
39
37

Use of using to dispose resources

4 Answers4