global sql connection string and deadlocking error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK. Here we go. I have an ASP.NET application that does many hits to a SQL Server DB on a separate server. When I first created this application (2 years ago) and was very new to ASP/ASP.NET, to make the SQL connection string global throughout the application I created a .vb file within the application project and declared a public SQLConnection:

Public ors_wnf As SqlConnection = New SqlConnection("SERVER=ZEUS;UID=wnfdbuser;PWD=******;DATABASE=EZPrice;")

This works fine functionally, but I know its not the best way to do this. Also, a few times a day when it gets real busy on the web server I get a aspnet_wp.exe error - "aspnet_wp.exe was recycled becasue it was suspected to be in a deadlocked state..." This error states that the 180 second timeout has been reached and hangs the web server. There are a few applications on this web server that connect to the SQL Server with their sqlconnections declared publicly the same way within each web app. However, the SQL Server is only running at around 10% when this deadlock occurs (the SQL commands are mostly basic selects), and the web server is only running at about 25%. And, I am not using multiple threads within each web application. This deadlock issue is becoming a huge problem as we have a few hundred users on the intranet now.

Are these issues related?
What is the best way to use the sqlconnection within each session so I only have to declare it in one place?
Would DB connection pooling resolve the deadlock issue? I am thinking that the deadlock is happening from all the connections into the SQL Server.

Any insight would be greatly appreciated. Thanks.
 
The way most developers handle it is to put the connection string in your
web.config file, like this:
<configuration>
<appSettings>
<add key="DSN" value="Server=(local);Database=DBName;UID=sa;PWD="/>
<add key="OtherConnectionString"
value="Server=(local);Database=DBName2;UID=sa;PWD="/>
</appSettings>
</configuration>

This is a nice way to manage it. You can change the connection string
easily without rebuilding the app or restarting IIS or anything, and the
change goes into effect immediately.

Then in your code behinds you can get the connection string like this:
Dim sConn As String = ConfigurationSettings.AppSettings("DSN")

You'll want to put that line on every page that does data access. Open the
connection just before you need it and close it as soon as you are done with
it. Automatic connection pooling in ADO.NET makes this a very efficient
technique.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com





Mark@WFI said:
OK. Here we go. I have an ASP.NET application that does many hits to a
Click to expand...
SQL Server DB on a separate server. When I first created this application
(2 years ago) and was very new to ASP/ASP.NET, to make the SQL connection
string global throughout the application I created a .vb file within the
application project and declared a public SQLConnection:
Public ors_wnf As SqlConnection = New SqlConnection("SERVER=ZEUS;UID=wnfdbuser;PWD=******;DATABASE=EZPrice;")

This works fine functionally, but I know its not the best way to do this.
Click to expand...
Also, a few times a day when it gets real busy on the web server I get a
aspnet_wp.exe error - "aspnet_wp.exe was recycled becasue it was suspected
to be in a deadlocked state..." This error states that the 180 second
timeout has been reached and hangs the web server. There are a few
applications on this web server that connect to the SQL Server with their
sqlconnections declared publicly the same way within each web app. However,
the SQL Server is only running at around 10% when this deadlock occurs (the
SQL commands are mostly basic selects), and the web server is only running
at about 25%. And, I am not using multiple threads within each web
application. This deadlock issue is becoming a huge problem as we have a
few hundred users on the intranet now.
Are these issues related?
What is the best way to use the sqlconnection within each session so I
Click to expand...
only have to declare it in one place?
Would DB connection pooling resolve the deadlock issue? I am thinking
Click to expand...
that the deadlock is happening from all the connections into the SQL Server.
 
Steve
Thanks for the advice. I modified my web apps all declare the sql connection string in the web.config file. I then declared the actual sql connection as a protected variable at the top of the code behind for each page. I also included in the connection string some connection pooling settings (pooling=true;max pool size=1500;connection lifetime=0;). Hopefully this will prevent the deadlock errors on the web server from occurring. Thanks again

Mar


----- Steve C. Orr [MVP, MCSD] wrote: ----

The way most developers handle it is to put the connection string in you
web.config file, like this
<configuration><appSettings><add key="DSN" value="Server=(local);Database=DBName;UID=sa;PWD="/><add key="OtherConnectionString
value="Server=(local);Database=DBName2;UID=sa;PWD="/></appSettings></configuration

This is a nice way to manage it. You can change the connection strin
easily without rebuilding the app or restarting IIS or anything, and th
change goes into effect immediately

Then in your code behinds you can get the connection string like this
Dim sConn As String = ConfigurationSettings.AppSettings("DSN"

You'll want to put that line on every page that does data access. Open th
connection just before you need it and close it as soon as you are done wit
it. Automatic connection pooling in ADO.NET makes this a very efficien
technique

--
I hope this helps
Steve C. Orr, MCSD, MV
http://Steve.Orr.ne
Hire top-notch developers at http://www.able-consulting.co





Mark@WFI said:
OK. Here we go. I have an ASP.NET application that does many hits to
Click to expand...
SQL Server DB on a separate server. When I first created this applicatio
(2 years ago) and was very new to ASP/ASP.NET, to make the SQL connectio
string global throughout the application I created a .vb file within th
application project and declared a public SQLConnectionAlso, a few times a day when it gets real busy on the web server I get
aspnet_wp.exe error - "aspnet_wp.exe was recycled becasue it was suspecte
to be in a deadlocked state..." This error states that the 180 secon
timeout has been reached and hangs the web server. There are a fe
applications on this web server that connect to the SQL Server with thei
sqlconnections declared publicly the same way within each web app. However
the SQL Server is only running at around 10% when this deadlock occurs (th
SQL commands are mostly basic selects), and the web server is only runnin
at about 25%. And, I am not using multiple threads within each we
application. This deadlock issue is becoming a huge problem as we have
few hundred users on the intranet now
What is the best way to use the sqlconnection within each session so
Click to expand...
only have to declare it in one place
Would DB connection pooling resolve the deadlock issue? I am thinkin
Click to expand...
that the deadlock is happening from all the connections into the SQL Server
 
The default settings for pooling are pretty efficient in most cases.
You might not want to tamper with them (by including the settings you
specified) unless you encounter a reason to do so.

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com



Mark@WFI said:
Steve,
Thanks for the advice. I modified my web apps all declare the
Click to expand...
sql connection string in the web.config file. I then declared the actual
sql connection as a protected variable at the top of the code behind for
each page. I also included in the connection string some connection pooling
settings (pooling=true;max pool size=1500;connection lifetime=0;).
Hopefully this will prevent the deadlock errors on the web server from
occurring. Thanks again.
Mark


----- Steve C. Orr [MVP, MCSD] wrote: -----

The way most developers handle it is to put the connection string in your
web.config file, like this:
<configuration><appSettings><add key="DSN" value="Server=(local);Database=DBName;UID=sa;PWD="/><add
key="OtherConnectionString"
Click to expand...
 
Back
Top