SQL connection in threading

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 ?
shenlong_wcAsked:
Who is Participating?
 
DabasConnect With a Mentor Commented:
shenlong_wc,
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.

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

Dabas
0
 
shenlong_wcAuthor Commented:
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 ?
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
shenlong_wcAuthor Commented:
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 .
0
 
DabasCommented:
Sounds like the way to go, yes!
0
 
shenlong_wcAuthor Commented:
thanks !
0
 
shenlong_wcAuthor Commented:
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 ?
0
All Courses

From novice to tech pro — start learning today.