OleDbConnection.close() takes long time, when using Microsoft access database engine 2016 in c#, whereas there is no such issue with microsoft access database engine 2010.

Question

When using microsoft access database engine 2016, OleDbConnection.close() is taking long time and the program got struck at this line (conn.Close()) which is resulting in few issues with my application. There is no such issue, when I use microsoft access database engine 2010. Unfortunately I cant use access 2010 going further and I should make this piece of code work with access 2016. PFB, the code which is having issues.

Thanks in advance.

Answer 1

First off, never post code as an image.

Never add parameters in a foreach, create them once outside the foreach and set values in the foreach.

Consider not using OleDb, instead look at a dedicated library such as SpreadSheetLight installed via NuGet which uses Open Office XML and virtually uses no resources.

Answer 2

Hi,@Kousiga Govindarajan. Welcome Microsoft Q&A.

Long delays or hangs when calling OleDbConnection.Close() can sometimes occur with certain versions of the Microsoft Access Database Engine. This issue might be related to resource cleanup or other factors specific to the database engine version. Here are some steps you could take to diagnose and potentially resolve the problem:

Update to the Latest Version: Ensure that you are using the latest version of the Microsoft Access Database Engine. You can download it from the official Microsoft website. Newer versions may include performance improvements and bug fixes.

Check for Connection Leaks: Make sure that you are not leaking connections in your code. Ensure that every OleDbConnection object you create is properly closed and disposed of. Using using statements around the connection can help ensure that the connection is properly closed when you are done with it.

using (var conn = new OleDbConnection(connectionString))
{
    // Your code here
} // The connection will be automatically closed and disposed.

**Use Connection Pooling: **Connection pooling can help manage and optimize database connections. Ensure that connection pooling is enabled in your application by default (it usually is). Connection pooling should handle the opening and closing of connections efficiently.

Check for Resource-Intensive Operations: Review the operations you perform before calling OleDbConnection.Close(). If there are resource-intensive or time-consuming operations, they could lead to delays in closing the connection. Optimize those operations if possible.

Test with Different Data: If the issue seems specific to certain data or tables in your database, try creating a test database with a smaller dataset to see if the problem persists. This can help identify if the issue is data-related.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.