Solved

ODBC Connections - Sharing an open connection via multiple threads.

Posted on 2011-03-14
6
544 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

773 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