OleDbConnection: How to open and close a connection using a function

Question

I have a function that connects to a Excel File:

    public OleDbConnection connection;

    public void eConnection(string srcString, string id)
    {
        conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + srcString + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        connection = new OleDbConnection(conString);
        connection.Open();

    } 

I want to create another function that will close this existing connection when called or invoke

This is what I have to try and close the existing connection:

    public void eCloseConnection()
    {
        connection.Close();
    }

How can I close the existing connection using a function that calls the same connection and closes it

How can I test to see if the connection is closed?

Answer 1

Don't do it like this. OleDbConnection implements the IDisposable interface should be disposed as soon as you are done using it, and the best way to do it is to use it as a local variable declared in a using statement:

public void DoStuffWithExcel(string srcString)
{
    conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + srcString + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
    using(var connection = new OleDbConnection(conString))
    {
        connection.Open();
        // do your stuff here...
    }
}

The using statement ensures the connection will be disposed properly even if an exception occurs inside the using block.
This way is the best way to prevent memory leaks, as well as to use the connection pool.

From Microsoft docs page OLE DB, ODBC, and Oracle Connection Pooling:

We recommend that you always close or dispose of a connection when you are finished using it in order to return the connection to the pool. Connections that are not explicitly closed may not get returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.