SQL connection in threading

Posted on 2004-10-11
Last Modified: 2010-04-23
Hi all,

i'm trying to make a program with multiple threads that do some work then insert records into my SQL 2000 . should i declare SQLconnection1 as shared, or private to each thread ? what do i have to look out for when working with databases and threads ?
Question by:shenlong_wc
  • 4
  • 3
LVL 27

Expert Comment

ID: 12283545
Hi shenlong_wc,
I suggest you share the SQLConnection and within the thread just check its state and see if it needs opening.


Author Comment

ID: 12283580
is it ok if i declare it private shared to the whole class ? (this classes might be part of a thred too )

it'll be used by some subs that are multi-threaded and some which are not.

other than checking if it's open, is there anything i should be aware of ?
LVL 27

Accepted Solution

Dabas earned 50 total points
ID: 12283595
Without seeing your class structure, it is hard for me to answer that.
But essentially you should try to limit your connection to a minimum. If you have various instances of the same class, then it would not make sense to declare it within the class. It would cause the same connection to be open many times.
In that case you should consider holding your connection at application level instead of at class level.

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!


Author Comment

ID: 12284120
code's kinda long , kinda difficult to post it here

how about if i create only 1 SQLconnection for the entire project then pass it by ref to all the methods that need it ? or maybe even declare it as a global variable ? I will open it when i start the app and close it when i exit the app. in between, it'll be free for all the threads , objects , sub etc to use, and they won't have to worry about opening or closing it ?

is that a good idea ?

sorry, my connection pooling concepts are kinda weak .
LVL 27

Expert Comment

ID: 12284173
Sounds like the way to go, yes!

Author Comment

ID: 12284185
thanks !

Author Comment

ID: 12355593
Hi there,

the problem didn't seem to go away ...

if i shared my OleDbCommand and OleDbConnection  as a global, sometimes my threads scream something about the OleDbCommand still open or fecthing some data .

if i shared only my OleDbConnection as a global, i get errors saying that it still has open datareaders on it .

what should i do ? short of creating a new OleDbCommand and OleDbConnection everywhere , that is . how can i make my OleDbCommand thread safe ?

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at

760 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

20 Experts available now in Live!

Get 1:1 Help Now