paulyoder/LinqToExcel — pull request #24 by acorkery (2013-09-11 13:13:13+00:00)
Title: Option to use a persistent connection for the lifetime of ExcelQueryFactory

Hey Paul,

For our use case, we need to be able to keep a single connection open for multiple queries to the same spreadsheet. I couldn't see a way of doing this with the current API.

I thought of a few ways of doing this, all of them required a fair bit of internal change within ```ExcelQueryExecutor```. Some options were:

- passing in a connection created/maintained outside the library. This involved too much change to the public API and moves the responsibility for abstracting the connection outside of the library.
- adding methods to ExcelQueryFactory to create, open, close and dispose a private connection. This felt like bloating the API for a use case that might not be widespread.
- add a constructor overload to ```ExcelQueryFactory``` to provide this option.

I went with the 3rd option.

```c#
public ExcelQueryFactory(string fileName, bool createPersistentConnection)
```

I followed your guidance of using ```ExcelQueryArgs```. Given the atomic nature of the operations e.g. ```WorksheetRange```, I needed to make a fair few changes within ```ÈxcelQueryExecutor``` to facilitate the option of keeping a connection open across multiple invocations.

The gist of the change is that instead of instantiating a new connection each time one is required, the following ``ÈxcelUtilities``` method is called:

```c#
internal static OleDbConnection GetConnection(ExcelQueryArgs args)
{
if (args.PersistentConnection != null)
return args.PersistentConnection;

return new OleDbConnection(GetConnectionString(args));
}
```

Another not-insignificant change is that in my fork, ExcelQueryFactory implements ```IDisposable```, requiring the user to invoke ```Dispose``` if they want to close & dispose of the persistent connection.

Anyway, I realise this update may be a bit heavy for your library if it's an edge case, but I thought I'd send it on - it might be of interest to someone with a similar problem.

Andrew