Solved

ODBC Connections - Sharing an open connection via multiple threads.

Posted on 2011-03-14
6
546 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 69

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

820 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