0

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
HerrimanCoder
  • 6,835
  • 24
  • 78
  • 158
  • 1
    Just use one place to create all connections, then you will have all connections used in your application in one place – Fabio Nov 16 '16 at 16:23
  • 3
    Now you see the danger of having "global" connections. The idiomatic way to use disposable resources is to create them, use them, and dispose of them. – D Stanley Nov 16 '16 at 16:23
  • 1
    Another idea (better) always create new connection(`OleDbConnection` instance) for some query and dispose it right away after finished with it – Fabio Nov 16 '16 at 16:24
  • That said, you seem to know how to close the connections, so I don't understand exactly what the problem is. What is the scenario in which is does not work? – D Stanley Nov 16 '16 at 16:25
  • Fabio: we do create these 2 connection objects in 1 place, 1 central function. Stanley: We originally went down the road of create/use/dispose, but we run hundreds of thousands of sql commands in certain call stacks and the performance is horrible when we do that. Stanley: as mentioned, the way we close normally works, but in a certain function it doesn't. No idea why. Thus this post. – HerrimanCoder Nov 16 '16 at 16:33
  • I don't get what the problem is. Does this function throw errors? Are you sure you don't open *any* other connection? If this method does not throw errors, then odds are that buried somewhere there's a connection being opened and then forgotten about. After all, you yourself said the app is large and complex. EDIT: also, you could try to toy around with Perf Counters, I'm pretty sure there's one that will tell you how many open connections you have. – s.m. Nov 16 '16 at 17:03
  • re: "other thought" - Have you seen [this question](http://stackoverflow.com/q/4159887/2144390)? – Gord Thompson Nov 16 '16 at 17:54
  • Also, if the replication steps involve any forms being displayed, do those forms have any automatically-generated objects like a DataSource or TableAdapter that might be opening their own connections? – Gord Thompson Nov 16 '16 at 19:47
  • 1
    Can you add the code illustrating how you use these in that *certain 100% reproducible scenario*. The problem clearly is not in that little snippet, but how the objects are being used. – Ňɏssa Pøngjǣrdenlarp Dec 13 '16 at 01:12
  • Since you manage your connections, you don't need implicit connection pooling. Dispose just puts the connection back in the pool (why you don't benefit from pooling in the first place is obscure). I suggest you disable pooling and add a OLE DB Services=-2 for example to your connection string, as described here: https://blogs.msdn.microsoft.com/selvar/2007/11/10/ole-db-resource-pooling/ – Simon Mourier Dec 13 '16 at 07:10
  • I'm trying this out (OLE DB Services=-2) and will report back. Thanks Simon. – HerrimanCoder Dec 13 '16 at 18:11
  • I'd be surprised of disabling pooling had any effect. Conns in the pool are inactive and available for use by the app. Its the undisposed ones which will have a lock...but we arent privy to that code – Ňɏssa Pøngjǣrdenlarp Dec 15 '16 at 19:02
  • @Plutonix - the benefit of pooling is precisely to keep the physical connections alive for a certain amount of time, because establishing a connection is costy in general. Disposing a connection doesn't close the physical connection if pooling is enabled. – Simon Mourier Dec 16 '16 at 07:49
  • Disabling pooling had no effect. – HerrimanCoder Dec 16 '16 at 13:42
  • It's dirty but if only as a test have you tried simply deleting the lock file? You will see it created when locked *.laccdb – Paul Zahra Dec 16 '16 at 16:40
  • Also make sure there are still open connections that are causing the issue and not something else http://stackoverflow.com/questions/11876202/find-number-of-open-connection-on-database – Paul Zahra Dec 16 '16 at 16:46
  • Also another possible gotcha: what authentication are you using? Generally a connection pool is created for each variation of the connection string but I believe if you are using integrated security and Basic or Windows authentication will cause a new connection pool to be created for each user. – Paul Zahra Dec 16 '16 at 16:48
  • Have you tried setting the DCGlobals.Connection1 and DCGlobals.Connection2 to null after disposing (but before GC collecting)? Also, is it possible there is a timeout issue? What happens in your special scenario after a few seconds of running CloseOleDBConnections? after 10 seconds? after 20 seconds? a minute? Does it every work after a while? – JuanR Dec 19 '16 at 17:12

3 Answers3

2

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.

Kind Contributor
  • 17,547
  • 6
  • 53
  • 70
0

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/

a-man
  • 657
  • 1
  • 6
  • 16
0

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.

Paul Bruce
  • 554
  • 2
  • 7