global sql connection string and deadlocking error

[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.

 

[Steve C. Orr [MVP, MCSD]]

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

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.

 

[Paul M]

hi there,

as mentioned, i think its also a good idea to store the connection string in
the webconfig file.

I am using the Microsoft Data Application Block for my web projects, which
is downloadable from the MS website, and think it handles the database calls
very well.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/
daab-rm.asp

regards,
Paul.

 

[Guest]

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

OK. Here we go. I have an ASP.NET application that does many hits to

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

only have to declare it in one place

Would DB connection pooling resolve the deadlock issue? I am thinkin

that the deadlock is happening from all the connections into the SQL Server

 

[Steve C. Orr [MVP, MCSD]]

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

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.

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"