question

GregK-2722 avatar image
0 Votes"
GregK-2722 asked TimonYang-MSFT commented

OdbcConnection Automatically Closing When Opening Another via API

I have an application that opens an OdbcConnection via System DSN. It stays open up until the point I have to make an async API call that has its own SQL connection. Is there something causing the OdbcConnection to automatically close if I need to use another connection, even if its an API call that manages its own connection on the API layer? I've never seen this before even using multiple connections within the same application.

The main application is a worker service using .NET 5 and C#. It open the OdbcConnection but then I make an async API call and when that is called, it closes my OdbcConnection for some reason. They aren't even pointing to the same database obviously. Is there something going on or can I prevent this from happening?

Thanks,
Greg

dotnet-csharp
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@GregK-2722
Simply opening another connection does not seem to close the previous connection.
Could you please provide a minimal example that can reproduce this problem for us to test?
Also, why keep a connection open?
“open/close” SqlConnection or keep open?

0 Votes 0 ·
GregK-2722 avatar image
1 Vote"
GregK-2722 answered TimonYang-MSFT commented

I have figured out what the issue was. I was basically mixing synchronous and async calls. So, when I made the first await call, it returned back since the calling method was not awaited and executed the code to close the ODBC connection. Once I added await and async to my calling methods, the connection did not close until all methods were completed.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am glad to hear this news.
So the problem is solved, right? If so, you can accept your own answer so that members who have similar problems in the future can find solutions more quickly.

0 Votes 0 ·
GregK-2722 avatar image
0 Votes"
GregK-2722 answered

It's not that basic.

I am not keeping the ODBC connection open - only for the lifetime of the process. Here is the flow:
- Process starts
- Open ODBC connection using DSN to connect to legacy file-based system.
- Retrieve data from the legacy system
- While processing retrieved data from legacy system, I make an API call to a ,NET 5 API application (using async methods).
- When I call the API, the ODBC connection closes (this is my problem as it shouldn't).
- Return back from the API call, need to process and get more data from the ODBC connection based on returned data.
- Try to retrieve data from the legacy system again but the ODBC connection is closed.
- Process ends so close the ODBC connection.

The legacy system is really picky when it comes to opening and closing many connections so we try to limit it to one opening and closing for each process. We have a connection limit of 25 so within a single process, I probably retrieve data 10-15 times for various things. I cannot open and close that many times because it doesn't release the connection immediately and then we run out of connections on the legacy system.







5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.