SqlConnection in WinApp

  • Thread starter Thread starter Marco Liedekerken
  • Start date Start date
M

Marco Liedekerken

Hi,

Are there some "Good practices" in how to use a SqlConnection object in a
windows application?
Because I always use the same DB I don't want to give the SqlConnection
object as a parameter to all of my methods who use the SqlConnection. I want
it to be like a global variable to my whole application so I can use it
everywhere without thinking about it! Is this possible in a nice OO way
(with use of multiple threads, DataReaders, DataAdapters and still good
performance)?
Is it better to keep the SqlConnection open all the time? Opening and
Closing the connection 1000 times is not ideal!
This SqlConnection always points to the same database (an xml settingsfile
has a string which points to this DB) in all the application code.

Hope someone can help me, Marco
 
Marco Liedekerken said:
Hi,

Are there some "Good practices" in how to use a SqlConnection object in a
windows application?
Because I always use the same DB I don't want to give the SqlConnection
object as a parameter to all of my methods who use the SqlConnection. I want
it to be like a global variable to my whole application so I can use it
everywhere without thinking about it! Is this possible in a nice OO way
(with use of multiple threads, DataReaders, DataAdapters and still good
performance)?
Click to expand...

No, you'll have to create a new connection before any operation.
This is specially true for multiple threads and for DataReaders.
Is it better to keep the SqlConnection open all the time? Opening and
Closing the connection 1000 times is not ideal!
Click to expand...

Yes, it is becuase ado.net uses connection pooling under the hood.
So you are not really opening and closing connections all the time (you can
set via connection string the size of pool)
Plus the server doesn't get swamped by many connections opened all the time.
It is optimal - plus you doesn't look after when to create a new connection
(when, for example, you need the same connection from within two or more
threds).
This SqlConnection always points to the same database (an xml settingsfile
has a string which points to this DB) in all the application code.
Click to expand...

Bottom line: make connecton string global and create SqlConnection objects
when required and close them asap.
Hope someone can help me, Marco
Click to expand...

Hope I've helped.
 
Marco said:
Are there some "Good practices" in how to use a SqlConnection object
in a windows application?
Click to expand...

Yes, see the following article:
http://msdn.microsoft.com/library/en-us/dnbda/html/daag.asp

Because I always use the same DB I don't want to give the
SqlConnection object as a parameter to all of my methods who use the
SqlConnection. I want it to be like a global variable to my whole
application so I can use it everywhere without thinking about it! Is
this possible in a nice OO way (with use of multiple threads,
DataReaders, DataAdapters and still good performance)?
Click to expand...

It's better to use a global connection string, then open and close the connection
as needed on each page (in order to use connection pooling)
Is it better to keep the SqlConnection open all the time?

No

Opening and Closing the connection 1000 times is not ideal!
Click to expand...

Yes it is - IF you use connection pooling.

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com
 
Thanks for your quick response.

Now I don't create a new connection before any operation (in your reply you
said I have to do this). In the Load method of some forms I have initialized
a SQLConnection object (which exists in the scope of the whole form) which I
can use anywhere in this form class. Is this a good practice (bearing in
mind the use of threads and DataReaders)?
I also use a class with a lot of static methods. Here is where I have to
give the SqlConnection as a parameter because there are no class objects
which I can use (static methods can not reach them)

What is a good size for the size of the connection pool? How can I figure
out this ideal size?
It is optimal - plus you doesn't look after when to create a new
Click to expand...
connection (when, for example, you need the same connection from within two
or more threds).
I don't understand it. What can I do best when I have the same connection in
2 of more threads?
Bottom line: make connecton string global and create SqlConnection objects
Click to expand...
when required and close them asap
Now you say that I have to create SqlConnection objects when required and
close them asap! This is not the same as just opening the SqlConnection in
the Load method and not closing it at all (like I do now)! I thought the
latter was the method we agreed to be the best? I will only create the
SqlConnection 1 time (in my Form_Load() method)!

Do you know where I can get some guidelines to for my SqlConnection problem
with different Forms, threads, methods (in combination with DataReaders)?

Thanks again, Marco
 
Thanks Carl.

Now I use the following connectionString:
string sqlConnString = @"Data source=" + server + ";" + @"initial catalog="
+ datbs + ";" + @"integrated security=SSPI;" +@"Connect Timeout=200";

You say this is not right because I don't use the connection pooling
capabilities, correct?
And also that was the reasen why I had a big performance loss when I tried
opening and closing the SqlConnection for the 100000 times I wanted to use
it?
So opening the SqlConnection in the Load methods of my Forms and not closing
them is not good. I have to open and close the SqlConnection object all the
100000 times I use it, correct?

The link you send me was really helpfull.

Hoping for some new answers, Marco
 
Marco Liedekerken said:
Thanks for your quick response.

Now I don't create a new connection before any operation (in your reply you
said I have to do this). In the Load method of some forms I have initialized
a SQLConnection object (which exists in the scope of the whole form) which I
can use anywhere in this form class. Is this a good practice (bearing in
mind the use of threads and DataReaders)?
Click to expand...

I would rather creata a SqlConnection just before the opeartion itself - so
two threads will have different objects which is ok.
I also use a class with a lot of static methods. Here is where I have to
give the SqlConnection as a parameter because there are no class objects
which I can use (static methods can not reach them)
Click to expand...

Just set to some global (public) static property the connection string and
create a SqlConnection when needed :)
What is a good size for the size of the connection pool? How can I figure
out this ideal size?
Click to expand...

By default pool keeps one connection open. The ideal size would be the max
number of concurrent operations commonly used.
(if you often need two connections open then you'll better set the minimum
size to 2).
connection (when, for example, you need the same connection from within two
or more threds).
I don't understand it. What can I do best when I have the same connection in
2 of more threads?
objects
when required and close them asap
Now you say that I have to create SqlConnection objects when required and
close them asap! This is not the same as just opening the SqlConnection in
the Load method and not closing it at all (like I do now)! I thought the
latter was the method we agreed to be the best? I will only create the
SqlConnection 1 time (in my Form_Load() method)!
Click to expand...

No, it is not. What if you have two forms open? You'll have for entire forms
lifetime two physicall connection to database.
What if you open 10 forms? What if 10 users open 10 forms? You get the
point.

Do you know where I can get some guidelines to for my SqlConnection problem
with different Forms, threads, methods (in combination with DataReaders)?
Click to expand...

Check out
Connection Pooling for the .NET Framework Data Provider for SQL Server
and
connection pooling, ADO.NET connections

..net help topic.
 
Marco said:
Now I use the following connectionString:
string sqlConnString = @"Data source=" + server + ";" + @"initial
catalog=" + datbs + ";" + @"integrated security=SSPI;" +@"Connect
Timeout=200";

You say this is not right because I don't use the connection pooling
capabilities, correct?
Click to expand...

No. How you create the connection string has no relation to connection
pooling other than you need to use the same exact connection string each
time to reuse a connection pool. If it's off by one space, then a new connection
pool is created for that connection string. Note Integrated security is a special
case - each user will have their own connection pool.

Just remember to open, use, and close the connection as soon as possible on
each page (for function).
And also that was the reasen why I had a big performance loss when I
tried opening and closing the SqlConnection for the 100000 times I
wanted to use it?
Click to expand...

Try setting a Max Pool Size to a large number
http://msdn.microsoft.com/library/e...ntSqlConnectionClassConnectionStringTopic.asp

So opening the SqlConnection in the Load methods of my Forms and not
closing them is not good. I have to open and close the SqlConnection
object all the 100000 times I use it, correct?
Click to expand...

Correct, open the connection, run one or more commands, then close the connection.

Make sure to read up on connection pooling.
http://msdn.microsoft.com/library/e...nectionPoolingForSQLServerNETDataProvider.asp

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com
 
Hi Miha,

I changed the structure of my windows application, but now I get the
"Timeout expired" exception after some time!
I close all the connections that I have opened and all the DataReaders get
closed also, but after some time my application gets stuck and I get the
Timeout exception message. It tries opening a connection but it waits till
the timeout happens.
Properties of the connectionstring are: Timeout=400, MinPoolSize=10 and
MaxPoolSize=100

Do you know what can be the problem or how I can check what my problem is?
I searched the internet and newsgroups but could not find an answer.

Thanks, Marco
 
Hi Carl,

I changed the structure of my windows application, but now I get the
"Timeout expired" exception after some time!
I close all the connections that I have opened and all the DataReaders get
closed also, but after some time my application gets stuck and I get the
Timeout exception message. It tries opening a connection but it waits till
the timeout happens.
Properties of the connectionstring are: Timeout=400, MinPoolSize=10 and
MaxPoolSize=100

Do you know what can be the problem or how I can check what my problem is?
I searched the internet and newsgroups but could not find an answer.

Thanks, Marco
 
Hi Marco,

Try Setting MinPoolSize to 1 And MaxPoolSize to 2 (or some other sort of
extreme limitation) to see if there are open connections in the pool.
 
Hi Miha,

I changed the structure of my windows application, but now I get the
"Timeout expired" exception after some time!
I close all the connections that I have opened and all the DataReaders get
closed also, but after some time my application gets stuck and I get the
Timeout exception message. It tries opening a connection but it waits till
the timeout happens.
Properties of the connectionstring which I set in an settings.xml file are:
Timeout=400, MinPoolSize=10 and MaxPoolSize=100

Do you know what can be the problem or how I can check what my problem is?
I searched the internet and newsgroups but could not find an answer.

Thanks, Marco
 
I did set the minPoolSize to 1 and the maxPoolSize to 10 and then the
application hangs on startup.

How can I check which connections are open? I want to find out why or where
I forgot to close the connection! But I am pretty sure I close all the
opened connections!

Hope to hear from you soon, Marco
 
It stops at a line where it tries to open a new connection.
Total number connections pooled stays well under 8! While I set my max
poolsize to 100

My counters look like this (after restarting the win app in debug mode a
couple of times):

_global
myApp
Current # connection pools 7 7
Current # pooled and nonpooled connections 2 2
Current # pooled connections 2 2
peak # pooled connections 0 6
Total # failed commands 1 1
Total # failed connects 9
9


I really don't have a clue now, Marco
 
It stops at a line where it tries to open a new connection.
Total number connections pooled stays well under 8! While I set my max
poolsize to 100

My counters look like this (after restarting the win app in debug mode a
couple of times):

_global
myApp
Current # connection pools 7 7
Current # pooled and nonpooled connections 2 2
Current # pooled connections 2 2
peak # pooled connections 0 6
Total # failed commands 1 1
Total # failed connects 9
9


I really don't have a clue now, Marco
 
I think I found the solution.

It was the project setting of SqlDebugging which was set to true in my case.
When I put it to false it seems I don't have the problem. This is realy
strange!
I found it in another post in a newsgroup
(http://groups.google.nl/groups?hl=nl&lr=&ie=UTF-8&oe=UTF-8&threadm=szxCrv9g
DHA.1928%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3D%2522timeout%2Bexp
ired%2522%2Bsql%2Bdebugging%26hl%3Dnl%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26se
lm%3DszxCrv9gDHA.1928%2540cpmsftngxa06.phx.gbl%26rnum%3D1) .
I am feeling happy now.

Thanks for your help
 
Back
Top