none
Access Database INSERT With backgroundworker locking DB after Connection is closed. RRS feed

  • Question

  • Hello,

    I am using ADO.Net to Insert rows to my database table, and make use of the backgroundworker to show the current progress of the insertion (for each row added). The problem is that once everything is added to the database i check the connection state (Open)  and Close it. But when i do so, the ldb file is not deleted and my next operation (Compacting the database) fails.

    I am using only one OleDbConnection object that is global and therefore shared throughout the whole application. So i don't where another connection would have been opened. Is there a way to force any pending connection on a database to be released? How to programmatically check what is causing the connection to not release the lock on the database after being closed?

    Thank you in advance!


    Tuesday, July 22, 2008 6:36 PM

All replies

  • It sounds like there is an object that still has a reference to the connection. Instead of using a single global persistent connection why not open and then close the database on an as needed basis? Access is file based so there is little or no overhead to the open operation.
    Wednesday, July 23, 2008 12:59 PM
  • An addition to Paul's post. Do not forget to call Dispose method of your connection after Close. It should release all the resources associated with that connection

     

    Wednesday, July 23, 2008 4:51 PM
    Moderator
  • Thank yo guys for your answers.

    Paul, I use to have a connection created on the fly, and delete whenever i needed to establish a connection to the database, but it was kind of hard keeping track of how many possible connection migt have been open since not only i couldn't check on different thread which one was creating the connection ( modifying a selected value in one of the many comboboxes i had in the UI would open a connection and update subsequent related combo boxes triggering a cascade fetching of data ...). So i gave up the idea and open the connection and close it when everything is done.

     

    VMazur, i tried with the dispose method with no more results. but i have a concern about it.

     

    Shouldn't the closing of the connection release the lock on the database? Is it possible that because of the backgroundworker that is updating the database table on a different thread, the call to close fails? because i notice that when i exit my application (to which point i call a close connection), the connection get closed and the ldb file deleted.

    (sorry, i should have precised that).

     

    Any ideas, or more suggestions?

    Thank you.

     

    Thursday, July 24, 2008 7:17 PM
  • Not sure if I entirely understand your description. What I'm suggesting is that each thread should be responsible for opening and closing its own connections. You shouldn't need to check for open connections. Open a connection, perform the database operation and then close it. If you're working with threads there may be multiple open connections to the database concurrently but like I said each thread should be handling its own connection.

    Friday, July 25, 2008 4:13 AM