asked on

ODBC Connections - Sharing an open connection via multiple threads.

This is a two part question. Please note the below hypothetical situation. I am looking for
expert advice and knowledge.

I have created an ODBC connection within Windows.
I have a C# program that will spawn several threads, and each thread will be making calls to the ODBC connection.

Question 1:
Is it safe to create a connection via "System.Data.Odbc.OdbcConnection" (C#) prior to spawning the threads, store the connection in a global variable, and then leave the connection open and have the threads utilize that open connection to query ODBC.

Will this work as intended? I..e each thread shares the connection? I suppose it can be done, but I want to know the right way to do this, so I am looking for knowlegable responses. I'm thinking there is more to this, especially when I use threads.

Question 2:
What is the best practice: to open a connection and close it after the query has been made, or
leave a connection open for the entire span of the programs run?

Now, this may not be a clear cut answer and will likely have pros and cons, so I would like to discuss further. Please let me know your thoughts. At this point in time, leaving 1 active connection open while the program is running may help with some licesing considerations.

systan

Question1;
Use .NET connection;
https://www.experts-exchange.com/blogs/systan/B_2913-Basic-SqlCommand-using-Insert-Update-Delete.html

Question2;
Your doubt is right;
https://www.experts-exchange.com/blogs/systan/B_2891-using-dataadapter-as-a-source-commands.html

LinQ is another Story, another slow framework, not SQL standard.

PSSupport

ASKER

Systan, I'm not sure how you post relates to any of my questions...
Maybe I'm not linking to the right links, but I'm not following...

Thanks.

systan

>>Question 2:
>>What is the best practice: to open a connection and close it after the query has been made, or
leave a connection open for the entire span of the programs run?

Maybe you should look at the codes, that opens database when loads, then closed when closes.

>>Question 1:
>>Will this work as intended? I..e each thread shares the connection? I suppose it can be done, but I want to know the right way to do this, so I am looking for knowlegable responses. I'm thinking there is more to this, especially when I use threads.

I'll post a code snippet later

Qlemo

In general opening a connection is a costly operation. The server needs to reallocate memory, the authentication stuff needs to be done, TCP/IP addressing happens (DNS, ARP, ...), aso. So if a connection is needed more often (not only once an hour), it is better to keep the connection open.

ASKER CERTIFIED SOLUTION

systan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PSSupport

ASKER

Hi, thanks for your help.