What is best SQLConnection strategy

  • Thread starter Thread starter Lenin Vyas
  • Start date Start date
L

Lenin Vyas

hi
I would like to know whats better---
To declare a SQLConnection variable as a Static member of
a Class containing functions to MakeConnection and
CloseConnection, or should I just declare it as a Public
member of the above mentioned class containing Connection
code.
I ask this because I have read in MS documents that-----
"Any public static (Shared in Visual Basic) members of
SqlConnection type are safe for multithreaded operations.
Any instance members are not guaranteed to be thread safe."
Does that mean if SqlConnection object is a non static
member of a class, it will not be able to take advantage
of Connection Pooling?
-lenin
 
Hi Lenin,

I would say that you should share only connection string.
You should create a connection instance only when you need it, open
immediately before you need it and close it asap.
 
Lenin Vyas said:
I would like to know whats better---
To declare a SQLConnection variable as a Static member of
a Class containing functions to MakeConnection and
CloseConnection, or should I just declare it as a Public
member of the above mentioned class containing Connection
code.
I ask this because I have read in MS documents that-----
"Any public static (Shared in Visual Basic) members of
SqlConnection type are safe for multithreaded operations.
Any instance members are not guaranteed to be thread safe."
Does that mean if SqlConnection object is a non static
member of a class, it will not be able to take advantage
of Connection Pooling?
Click to expand...

The statement about SqlConnection's thread-safety has nothing to do
with where the variable is declared - it's talking about instance
members *of SqlConnection* and static members *of SqlConnection*.

Aside from that, I agree with Miha's post.
 
hi
Thanks for the above answers.
Now, my next question is----
is it a good strategy to put my connection code in a seperate class file
and DataManipulation code in seperate class?. This will help me to keep
my connection code in one place and whenever i want to open connection i
just need to create an object of let's say clsConnection class and then
create an object of the class containing the DataManipulation code. This
obviously increases code resuability, but it also involves an extra
object creation overhead when manipulating data.
Instead of the above, can i simply put my connection code in the same
class which contains DataManipulation code? This reduces object
overheads but certainly involves redundancy for each DataManipulation
class.
Please reply.
-lenin
 
Lenin Vyas said:
Thanks for the above answers.
Now, my next question is----
is it a good strategy to put my connection code in a seperate class file
and DataManipulation code in seperate class?. This will help me to keep
my connection code in one place and whenever i want to open connection i
just need to create an object of let's say clsConnection class and then
create an object of the class containing the DataManipulation code. This
obviously increases code resuability, but it also involves an extra
object creation overhead when manipulating data.
Instead of the above, can i simply put my connection code in the same
class which contains DataManipulation code? This reduces object
overheads but certainly involves redundancy for each DataManipulation
class.
Click to expand...

I would suggest that if you really need a separate class for
connection-handling, it should probably just have a static method which
takes some parameters and returns a SqlConnection (or whatever).
 
Back
Top