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.

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


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

Industry Leaders: 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!

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

696 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