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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

688 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