CODEWITHSUNDEEP
c#winformsms-accessoledbconnection
HerrimanCoder
HerrimanCoder

Reputation: 7208

How to find and close any open OleDbConnections in winforms?

I have a C# Winforms app that is large and complex. It makes OleDB connections to an Access database at various times for various reasons. In a certain function we need to MOVE (copy + delete) the mdb file, but it can't be done because it's locked. I've tried lots of different things to unlock/release the mdb file, and sometimes it works.

But in a certain 100% reproducible scenario, it cannot be unlocked. We have 2 global oledb connection variables we reuse everywhere, for efficiency, and to avoid having 1-off connections everywhere. And these 2 connection vars are useful for when we want to CLOSE the connections, so we can delete the mdb.

Here is my function (which normally works - just not in this 1 case) to forcibly close/release the 2 oledb connections from our winforms app:

public static void CloseOleDBConnections(bool forceReleaseAll = false) {
    if ( DCGlobals.Connection1 != null )
       DCGlobals.Connection1.Close();

    if ( DCGlobals.Connection2 != null )
       DCGlobals.Connection2.Close();

    if ( forceReleaseAll ) {
       DCGlobals.Connection1.Dispose();
       DCGlobals.Connection2.Dispose();
       OleDbConnection.ReleaseObjectPool();
       GC.Collect(GC.MaxGeneration);
       GC.WaitForPendingFinalizers();
    }
}

I am passing true into the above function.

One other thought: Certainly my Winforms app knows about all open oledbconnections. Is there no way to tell c# to find and iterate all open connections? When I close/exit my application - poof - the open connection to the mdb is released and I can delete the file. So something in .net knows about the connection and knows how to release it -- so how can I tap into that same logic without exiting the application?

Post Script

(I am aware that Access is bad, non-scalable, etc. - it's a legacy requirement and we're stuck with it for now).

I have seen numerous stack discussions (and on other forums) on this topic. I have tried numerous recommendations to no avail.

Upvotes: 0

Views: 738

Answers (3)

Kind Contributor
Kind Contributor

Reputation: 18513

Disposed IDataReaders?

Do you disable all IDataReader objects properly? They may prevent the connection closing properly.

Tracking Solution

In any case, you need to at least better track all your connections. It sounds like a very large project. You need to be absolutely sure that all connections are being disposed.

1. New TrackedOleDbConnection object

Create a TrackedOleDbConnection object which inherits from OleDbConnection, but adds a static ConcurrentList named StillOpen. When the TrackedOleDbConnection is constructed, add to the list, when it's disposed (override that function), remove it.

public class TrackedOleDbConnection: OleDbConnection
{
    public TrackedOleDbConnection() : base()
    {
    }

    public TrackedOleDbConnection(string ConnectionString) : base(ConnectionString)
    {
    }

    //You don't need to create a constructor for every overload of the baseclass, only for overloads your project uses
    ConcurrentList<TrackedOleDbConnection> ActiveConnections = new ConcurrentList<TrackedOleDbConnection>();
    void AddActiveConnection()
    {
        ActiveConnections.Add(this);
    }

    override void Dispose()
    {
        ActiveConnections.RemoveIfExists(this); //Pseudo-function
        GC.SuppressFinalise(this);
    }

    //Destructor, to ensure the ActiveConnection is always removed, if Dispose wasn't called
    ~TrackedOleDbConnection()
    {
        //TODO: You should log when this function runs, so you know you still have missing Dispose calls in your code, and then find and add them.
        Dispose();
    }
}

2. Don't directly reference OleDbConnection anymore

Then do a simple Find and Replace across your solution to use TrackedOleDbConnection.

Then finally, during your CloseOleDBConnections function, you can access TrackedOleDbConnection.StillOpen to see if you've got a problem of an untracked connection around somewhere.

Wherever you find such untracked problems, don't use the single central references, but instead using to ensure your connection is disposed properly.

Upvotes: 2

Paul Bruce
Paul Bruce

Reputation: 554

It's highly likely that ADOX is not releasing the connection to the database. Make sure that you:

  • explicitly call 'Close' the ADOX Connection objects
  • call 'Dispose' them
  • call System.Runtime.InteropServices.Marshal.FinalReleaseComObject(db.ActiveConnection);
  • call System.Runtime.InteropServices.Marshal.Marshal.FinalReleaseComObject(db);
  • set them to Nothing/null

Also when something calls close on a file handle the close request is put in a queue to be processed by the kernel. In other word even closing a simple file doesn't happen instantly. For this, you may have to put in a time-boxed loop to check that the .LDB file is removed...though that will ultimately require the user to wait. Seek any other alternative to this approach, though it has been necessary with other formats/connections IME in the past.

Upvotes: 0

a-man
a-man

Reputation: 707

Probably if the only thing you need is to copy the file probably there is no need to mess with connections. Please take a look at this:

https://www.raymond.cc/blog/copy-locked-file-in-use-with-hobocopy/

Upvotes: 0

Related Questions