When using "DbConnection" should I use "using" or try-catch-finally to use DbConnection.close() to close the connection?

Question

I was searching the web for a while now. But didn't find any clear answer to my question. Whether when connecting to a database I should use "using" or I can just go with try-catch-finally? What I mean is:

I don't know if I should call the dispose method each time I finish interacting with the database or just close the connection.

    static public List<Category> GetAll()
    {
        List<Category> CategoryList;

        try
        {
            BaseDAO.Dbconn.Open();
            BaseDAO.SetCommand(BaseDAO.CommandAction.Read, "SELECT * FROM Categories");

            CategoryList = new List<Category>();

            using (DbDataReader reader = BaseDAO.Dbcmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int ID = reader.GetInt32(reader.GetOrdinal("CategoryID"));
                    string Name = reader.GetString(reader.GetOrdinal("CategoryName"));
                    string Description = reader.GetString(reader.GetOrdinal("Description"));
                    CategoryList.Add(new Category(ID, Name, Description));
                }
            }
            return CategoryList;
        }
        catch (Exception ex)
        {
            BaseDAO.Dbconn.Dispose();
            throw ex;
        }
        finally { BaseDAO.Dbconn.Close(); }

    }

The "Dbconnection" is static not sure if this is a good solution as well...

I am getting to know ADO and just wanted to know what's the best answer for this kind of question.

possible duplicate of [Uses of "using" in C#](http://stackoverflow.com/questions/75401/uses-of-using-in-c-sharp) — Cheng Chen, Jul 05 '12 at 05:08

Michael · Accepted Answer · 2012-07-05T05:58:45.900

4

It doesn't make any difference - whatever you prefer. The using clause gets turned into a try-finally by the compiler anyway.

EDIT I just noticed you are only calling Dispose if an exception occurs. You should move this into the finally clause as well. Dispose will call Close, so you don't need to specifically call it if you don't want to.

Further edit Given that, as you say, your DbConnection is static, then you don't want to call Dispose, but you do need to call Close. However, there should not be any need to use a static DbConnection - connection pooling will take care of efficiently handling the connections. Your code should create a new connection instance every time.

edited Jul 05 '12 at 05:58

answered Jul 05 '12 at 05:12

Michael

8,891
3
29
42

Yes, i know that using is translated to try-finally. But should i use the dispose each time i stop using the try statement or should i just close the connection? – Sergey Rotbart Jul 05 '12 at 05:17
Just saw your comment after my update. In short, Dispose always needs to be called, either explicitly through `Dispose()` or implicitly through `using`. – Michael Jul 05 '12 at 05:21
Comment on the edit: Yeah, but here is the question, is it very important to call dispose or should i just close the connection? because Dbconnection is static if i call dispose i will need to re-initialize it again – Sergey Rotbart Jul 05 '12 at 05:22
So using static isn't a good solution? i just find it more organized to use it. or should i use new instance every time? – Sergey Rotbart Jul 05 '12 at 06:20
Yes, just take static off your current declaration, and create a new instance every time. It's the OO way :) – Michael Jul 05 '12 at 07:05
Thanks, I'll follow your suggestion :) – Sergey Rotbart Jul 05 '12 at 07:42

score 2 · Answer 2 · edited May 23 '17 at 12:31

Reference: Using Statement C#.

The using statement allows the programmer to specify when objects that use resources should release them.

The object provided to the using statement must implement the IDisposable interface. This interface provides the Dispose method, which should release the object's resources.

A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement.

Reference : Example

As per Code Project, the .NET CLR converts

 using (MyResource myRes = new MyResource())
 {     
     myRes.DoSomething();
 }

to:

{ 
   // limits scope of myRes   
   MyResource myRes= new MyResource();    
  try    
  {        
    myRes.DoSomething();  
  }   
  finally   
  {        
     // Check for a null resource.        
     if (myRes!= null)            
     // Call the object's Dispose method.          
      ((IDisposable)myRes).Dispose();    
  } 
}

score 0 · Answer 3 · answered Jul 05 '12 at 05:44

0

Calling Dispose is equivalent to calling Close in most (if not all) DbConnection implementations, so there's no need to call the former if you are already calling the latter. Just make sure you do so in a finally in case there's an exception.

answered Jul 05 '12 at 05:44

Richard Szalay

83,269
19
178
237

When using "DbConnection" should I use "using" or try-catch-finally to use DbConnection.close() to close the connection?

3 Answers3