CODEWITHSUNDEEP
.netconnectionodbcdisablepooling
adrianstovall
adrianstovall

Reputation: 63

How do I definitively close ODBC connections when Pooling=false appears broken

So, I haven't seen a thread that talks about this from this direction.

I'm trying to write some code to track the average amount of time it takes to open an odbc connection to a database.

I figured it'd be straightforward. Create connection, stopwatch.start, open connection, stopwatch.close.

What I'm finding is that even though I have pooling set to no in my connection string, the first connection takes 700-ish ms, and all subsequent attempts in the same application execution report 0 ms.

I was planning on having a monitor service run this on a timer, but it's pointless if I don't get a "real" connection after the first try.

Shouldn't it take nearly the same amount of time on each open? Why is it 0 after the first try?

Trivial example in a worker service (I got rid of the using wrapper and tried explicit close/dispose, but doesn't seem to matter. I had Pooling=False originally and thought that was the problem, too, but nothing seems to change the behavior):

protected override async Task ExecuteAsync(CancellationToken stoppingToken)

{

    while (!stoppingToken.IsCancellationRequested)

    {

        if (_logger.IsEnabled(LogLevel.Information))

        {

            _logger.LogInformation("Worker running at: {time}", DateTimeOffset.Now);

        }

        string connectionString = $"Driver={{ODBC Driver 17 for SQL Server}};Server=blah;Database=blah;UID=blah;PWD=blah;Pooling=No";

        Stopwatch stopwatch = new Stopwatch();

 

        OdbcConnection connection = new OdbcConnection(connectionString);

 

        stopwatch.Start();

        connection.Open();

        stopwatch.Stop();

 

        connection.Close();

        connection.Dispose();

        connection = null;

 

        Console.WriteLine($"Time taken to open the connection: {stopwatch.ElapsedMilliseconds} ms");

        Console.ReadKey();

    }

}

Anybody know why it's doing this and/or how to make it definitively not reuse the connection?

Upvotes: 0

Views: 81

Answers (1)

JohnRambo93
JohnRambo93

Reputation: 91

So i was searching for the same thing and found out that it's not as easy as you would do it with an ADO.NET driver (like defining that in the connection string).

I found the following options, where only option 1 worked in my case (using an "IBM i Access ODBC Driver"):

  1. Open odbcad32.exe and go to the Connection-Pool Tab. Double-click a driver and deactivate pooling for this driver. This has a downside - this setting deactivates pooling for all applications on this client. The workaround I came up with was to register a "new" ODBC-driver using odbcconf /A {INSTALLDRIVER "<NEW_DRIVER_NAME>|driver=<DLL_PATH>"} (maybe you have to copy the settings in the registry key Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI from the existing driver) and change the connection pool setting on this "new" driver. So you can deactivate it for this application only (when you set the "new" driver name in the connection string). Problem is that this is not exactly a portable solution.
  2. Add Pooling=false to the connection string. (Did not work in my case, maybe the driver can decide to implement it or not)
  3. Using the informations from learn.microsoft.com - SQLSetEnvAttr Function, you could call the method SQLSetEnvAttr from odbc32.dll. I used the Code like below. The values for the constants are from sqlext.h. (Im my case the function returned 0 as response, but had no effect.)
[DllImport("odbc32.dll")]
private static extern int SQLSetEnvAttr(IntPtr EnvironmentHandle, int Attribute, IntPtr ValuePtr, int StringLength);

private const int SQL_ATTR_CONNECTION_POOLING = 201;
private const int SQL_CP_OFF = 0;

// Disable connection pooling
var r = SQLSetEnvAttr(IntPtr.Zero, SQL_ATTR_CONNECTION_POOLING, new IntPtr(SQL_CP_OFF), 0);

Upvotes: 0

Related Questions