Do I need to close OleDbDataReader everytime I use it?

Question

I am using the OleDbDataReader class in my c# project and my program keep locking up my mdb file after it runs. I am wondering how do I close my connection reliably at the end to release the lock. And if I need to close it everytime I run a query or can I just do it all in one go at the end of the program. Here is how I am setting it up:

private OleDbConnection myDbC = new OleDbConnection(connectionString);
myDbC.Open();

And here is how I use it, many many times:

OleDbCommand cmd = new OleDbCommand(SQL, myDbC);
OleDbDataReader reader = cmd.ExecuteReader();
reader.Close();

When the program finishes, I also do the following:

myDbC .Close();

So this is somehow locking up the mdb file. Any help?

See http://stackoverflow.com/questions/744051/sqldatareader-dispose — nawfal, May 31 '13 at 17:42

score 2 · Answer 1 · edited May 31 '13 at 17:55

I suggest you to use using keyword. Because sometimes you can forget to close or dispose connection and reader. Because using automatically disposes object. Using statement tells .NET to release the object specified in the using block once it is no longer needed.

using (OleDbConnection conn = /* Create new instance using your favorite method */)
{
    conn.Open();
    using (OleDbCommand command = /* Create new instance using your favorite method */)
    {
        using (OleDbDataReader dr = cmd.ExecuteReader())
        { 
            while (dr.Read()) 
            { 
                //read here 
            } 
       } 
    }
    conn.Close(); // Optional
}

Additional details from MSDN:

C#, through the .NET Framework common language runtime (CLR), automatically releases the memory used to store objects that are no longer required. The release of memory is non-deterministic; memory is released whenever the CLR decides to perform garbage collection. However, it is usually best to release limited resources such as file handles and network connections as quickly as possible.

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.

Is there anyway I can force the connection to close at the end? — Bill Software Engineer, May 31 '13 at 17:56
@YongkeBillYu OleDbConnection is inside using statemnet right now. So It will be closed 100%. BUt ayou can call conn.Close() at the end optionally as in my example. — Farhad Jabiyev, May 31 '13 at 18:03
See I already am calling Close on the database, but it still seem to be locking it up unless I restart my program. — Bill Software Engineer, May 31 '13 at 18:11
@YongkeBillYu Update your code like my example. Then get state of your connection with conn.State, it couldn't be open. — Farhad Jabiyev, May 31 '13 at 18:13

score 1 · Answer 2 · answered May 31 '13 at 17:52

It is always a good practice to .Close() connections. Do NOT rely on the Garbage Collector to do it for you. There are certain situations where the connection will automatically close for you; like when using a SqlDataAdapter and its .Fill() method. But that doesn't change the fact that you should manage your connections in your code.

That being said, this is best achieved with a using statement, like this:

using(OleDbConnection oledbConn = new OleDbConnection())
{
   oledbConn.Open();       
}

You can also nest these using statements, wrap command objects and even data adapters:

using(OleDbConnection oleDbConn = new OleDbConnection())
{
   oleDbConn.Open(); 
   using (OleDbCommand oleDbCmd = new OleDbCommand())
   {
      using (OleDbDataAdapter oleDbAdapter = new OleDbDataAdapter())
      {
         //More code can go here.
      }
   }
   oleDbConn.Close();      
}

Is there anyway I can force the connection to close at the end? — Bill Software Engineer, May 31 '13 at 17:53
Yes. Call the `.Close()` method on your connection object. See my code correction above. — Brian, May 31 '13 at 17:54

Do I need to close OleDbDataReader everytime I use it?

2 Answers2