Solved

ODBC Connections - Sharing an open connection via multiple threads.

Posted on 2011-03-14
6
538 Views
Last Modified: 2013-12-17
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.


0
Comment
Question by:PSSupport
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

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

Question2;
Your doubt is right;
http://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.
0
 
LVL 1

Author Comment

by:PSSupport
ID: 35132642
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.
0
 
LVL 14

Expert Comment

by:systan
ID: 35135074
>>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
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 68

Expert Comment

by:Qlemo
ID: 35135651
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.
0
 
LVL 14

Accepted Solution

by:
systan earned 125 total points
ID: 35138422
>>it is better to keep the connection open.
That's the trick, closed it if app also closed.

>>Question 2:
>>What is the best practice: to open a connection and close it after the query has been made,
I only use this method when adding, deleting, updating SMALL amount of records, example; the maintenance or config  records.
>>or
leave a connection open for the entire span of the programs run?
Yes, your main database table that carry big amounts of data.


>>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,
YES,
>>and then leave the connection open and have the threads utilize that open connection to query ODBC.
YES
I am in favor of the method, and I am doing this kind of system until now, since 1980s using old dbase for dos, and even in unix I also leave the base connection open and store records globally for faster access.

That is the trick, many developers are also tricked by Microsoft samples, as you know and see it.
0
 
LVL 1

Author Closing Comment

by:PSSupport
ID: 35147543
Hi, thanks for your help.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now