c# - OleDbConnection.State issue with Microsoft Access

Question

I have a C# application that reads/writes from/to an Access Database. I do this using an OleDbConnection.

Before a read / write, I want to check that my database connection is open. It should be straight-forward....

if (Connection.State != ConnectionState.Open) Connection.Open();

While this generally works well, it is not working for situations where network access is interrupted. The Connection.State is still recorded as 'Open', so I'd get an exception. So, I tried this:

Connection.ResetState();
if (Connection.ConnectionState(DatabaseType) != ConnectionState.Open) Connection.Open();

Nope... That's not working either, as the ResetState() method isn't actually resetting the state.

So I tried this extension method (a bit ugly...)

public static ConnectionState ConnectionState(this OleDbConnection connection, DatabaseType type)
{
    connection.ResetState();
    if (type == DatabaseType.Access)
    {
        //Reset state might not work... Check that the datasource is locatable
        if (!File.Exists(connection.DataSource)) return System.Data.ConnectionState.Closed;
        return connection.State;
    }
    return connection.State;
}

This "sort of" works, in that it correctly identifies that the DataSource can't be located. But then I get an exception when I try to open the database; not that it can't be found, but that its an invalid operation trying to open a connection that is already open!

I've spent about an hour trying to google to find a reliable way to get the true status of the database connection. Any other suggestions?

Answer 1

Correct way to use DbConnection is to create a new one for every command. Connection pooling means there is not usually a big overhead, and any network issues in between are sorted out automatically.

Make sure to Dispose correctly.