?
Solved

ODBC Connections - Sharing an open connection via multiple threads.

Posted on 2011-03-14
6
Medium Priority
?
569 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 71

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 500 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

862 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