Problem with OleDbConnection, Excel and connection pooling

Question

So, I have the same symptoms as described in C#/ASP.NET Oledb - MS Excel read "Unspecified error", but my my answer did not seem to fix it. Even always closing the OleDBConnection and disposing of it show the same symptoms.

var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 12.0;", _excelFile);
using (var conn = new OleDbConnection(connectionString))
{
    try
    {
        DoSomething();
    }
    finally
    {
        conn.Close();
    }
}

Now I have found the following info on connection pooling:

The .NET Framework Data Provider for OLE DB automatically pools connections using OLE DB session pooling. Connection string arguments can be used to enable or disable OLE DB services including pooling. For example, the following connection string disables OLE DB session pooling and automatic transaction enlistment.

Provider=SQLOLEDB;OLE DB Services=-4;Data Source=localhost;Integrated Security=SSPI;

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.

(Source: http://msdn.microsoft.com/en-us/library/ms254502.aspx)

What is the connection string property OLE DB SERVICES and what is a value of -4?

Menorah

If you plan to award a bounty you should do this explicitly like as described here for example. – Lennalennard

@Residuum,Is it working with OLE DB Services=-4 in connection string ? I am facing the same problem... – Louque

@Thirisangu: Yes, OLE DB Services=-4 is working. – Menorah

Answer 1

If I understand correct your question you want to know what means the connection string property OLE DB Services=-4.

You can find the corresponding documentation here, here and here. I hope the information is what you need. If you want play with DBPROPVAL_OS_AGR_AFTERSESSION property it has the value 8 (see oledb.h).

Lennalennard

Answer 2

I'm not really into OleDbConnections, but have you seen this post?

http://blogs.msdn.com/b/selvar/archive/2007/11/10/ole-db-resource-pooling.aspx

Table 4 in this post is mentioning the OLE DB SERVICES property and the values that belong to the -number values.

Procurer

now at learn.microsoft.com/en-us/archive/blogs/selvar/… – Actionable