Database connection not closing windows form

Question

I seem to be having an issue closing my Database Connection (Program.DB_CONNECTION).

When I close the form I run Program.DB_CONNECTION.Close but this dosnt seem to close the connection as the database lock file is still there and I cannot delete the database.

Can anyone see what I am doing wrong for the database not to close?

private void update_gic_attendances()
    {
        OleDbCommand com = new OleDbCommand("SELECT count(*), [Squadron] FROM Personnel WHERE [Position] = ? and [Current?] = TRUE GROUP BY [Squadron]", Program.DB_CONNECTION);
        com.Parameters.Add(new OleDbParameter("", PositionIDs.GIC));

        OleDbDataReader dr = com.ExecuteReader();

        while (dr.Read())
        {
            try
            {
                OleDbCommand com2 = new OleDbCommand("SELECT * FROM GIC_Attendances WHERE [Attendance_Date] = ? AND [Squadron] = ?", Program.DB_CONNECTION);
                com2.Parameters.Add(new OleDbParameter("", DateTime.Today.Date));
                com2.Parameters.Add(new OleDbParameter("", dr.GetInt32(1)));

                OleDbDataReader dr2 = com2.ExecuteReader();

                if (dr2.HasRows)
                {
                    //update the number of attendees
                    OleDbCommand com3 = new OleDbCommand("UPDATE GIC_Attendances SET [Number_Attended] = ? WHERE [Attendance_Date] = ? AND [Squadron] = ?", Program.DB_CONNECTION);
                    com3.Parameters.Add(new OleDbParameter("", dr.GetInt32(0)));
                    com3.Parameters.Add(new OleDbParameter("", DateTime.Today.Date));
                    com3.Parameters.Add(new OleDbParameter("", dr.GetInt32(1)));

                    com3.ExecuteNonQuery();
                }
                else
                {
                    OleDbCommand com3 = new OleDbCommand("INSERT INTO GIC_Attendances ([Attendance_Date], [Squadron], [Number_Attended], [Number_Expected], [AddedOnSlave]) VALUES (?, ?, ?, ?, TRUE)", Program.DB_CONNECTION);
                    com3.Parameters.Add(new OleDbParameter("", DateTime.Today.Date));
                    com3.Parameters.Add(new OleDbParameter("", dr.GetInt32(1)));
                    com3.Parameters.Add(new OleDbParameter("", dr.GetInt32(0)));
                    com3.Parameters.Add(new OleDbParameter("", 4));

                    com3.ExecuteNonQuery();
                }

                dr2.Close();
            }
            catch
            {
            }
        }

        dr.Close();
    }

Have you tried handling any errors in the catch block? As it stands you'll have no idea if anything goes wrong. — Nathan, May 05 '14 at 18:19
You might want to look into the [using statement](http://stackoverflow.com/questions/16122740/how-to-add-a-using-statement-to-the-system-data-entity-namespace) which is the best practice recommended with your connection and commands.. This is the best practice, as [it handles closing/disposing automatically](http://stackoverflow.com/questions/4717789/in-a-using-block-is-a-sqlconnection-closed-on-return-or-exception/4717802#4717802)... — David, May 05 '14 at 18:21
Closing the connection is not enough to remove the LDB file (I suppose you are using Access). You need to dispose the connection and, as @DavidStratton said the using statement is the only correct way to go. But probably this requires a refactoring of your data access code. — Steve, May 05 '14 at 18:23
The code doesn't return any errors, i an only wanting the close the connection now as another section of my project needs to copy the current database change its name and delete the original file but i cannot delete the original because its still open with the code above — Ryanagray, May 05 '14 at 18:23
The reason the “code doesn't return any errors” is because `catch {}` is hiding them. Do not do that. Ever. — Dour High Arch, May 05 '14 at 18:46

Steve · Answer 1 · 2014-05-06T06:57:01.653

As explained rather well in the comments you need to close and dispose the connection. Having a global variable that keeps the connection is not a good pattern because it is always difficult to properly destroy the variable.

You could try something like this to demonstrate the point

private void update_gic_attendances()
{
    using(OleDbConnection cn = new OleDbConnection(....your connection string...))
    using(OleDbCommand com = new OleDbCommand(....., cn))
    {
         cn.Open();
         .....
         using(OleDbDataReader dr1 = com.ExecuteReader())
         {
            while(dr1.Read())
            {
                using(OleDbCommand com2 = new OleDbCommand(...., cn);
                using(OleDbDataReader dr2 = com2.ExecuteReader())
                {
                   ....
                   if(dr2.HasRows)
                   {
                        using(OleDbCommand com3 = new OleDbCommand(....., cn)
                        {
                           ....
                          com3.ExecuteNonQuery();
                        }
                   }
                   else
                   {
                        using(OleDbCommand com3 = new OleDbCommand(....., cn))
                        {
                            com3.ExecuteNonQuery();
                        }
                   }
                }
            }

        }
    }
}

Now the connection is local to the method. It is created and enclosed in a using statement that will close and destroy it at the exit from the using block. and the same happens to the other disposable objects like the readers and the commands. At the end of this code the connection is closed and disposed and the file should be no more locked (unless you have another connection opened somewhere)

However, said that, there something that doesn't seems to be correct in your first query.
You call a SELECT COUNT(*) statement, but this returns just the number or records satisfying your query WHERE clause, not the whole rows. Then you use a DataReader to loop over this query result and, worst, you try to call GetInt32 for the first and second field. But the second one doesn't exist.

Database connection not closing windows form

1 Answers1