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. 4 2743
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" <mb*********@wnfnet.com> wrote in message
news:EC**********************************@microsof t.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. Any insight would be greatly appreciated. Thanks.
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/de...us/dnbda/html/
daab-rm.asp
regards,
Paul.
"Steve C. Orr [MVP, MCSD]" <St***@Orr.net> wrote in message
news:uT**************@TK2MSFTNGP11.phx.gbl... 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" <mb*********@wnfnet.com> wrote in message news:EC**********************************@microsof t.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. Any insight would be greatly appreciated. Thanks.
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" <mb*********@wnfnet.com> wrote in messag
news:EC**********************************@microsof t.com.. 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 SQLConnection Public ors_wnf As SqlConnection = Ne
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
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 Are these issues related 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 Any insight would be greatly appreciated. Thanks
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" <mb*********@wnfnet.com> wrote in message
news:07**********************************@microsof t.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"
value="Server=(local);Database=DBName2;UID=sa;PWD= "/></appSettings></configu
ration> 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" <mb*********@wnfnet.com> wrote in message news:EC**********************************@microsof t.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. >> Any insight would be greatly appreciated. Thanks. > This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: fruddy |
last post by:
Hi everybody,
I currently have my SQL Server connection string in an
Application variable in the global.asa.
Could that be a security risk?
I have heard that with Cold Fusion, when there is a...
|
by: Matt Calhoon |
last post by:
Hi there,
How does IIS 6.0 treat the global.asa file? I had the following problem;
1. uploaded a new site to the win3k server
2. had incorrect db connection string in Session_OnStart in...
|
by: bloodhound |
last post by:
Hi,
Problem with global.asa not firing 100% of the time. This error crops
up several times throughout the day but if you wait a while and reload
the page (could be 5 mins or an hour) it will...
|
by: Colin Graham |
last post by:
i am currently developing an asp.net web application which is linked
to an Access database.
The main problem that i have is in creating a global pathname that i
can access. When i put my string...
|
by: Dan Bass |
last post by:
Environment
C#, Asp.Net 2.0, Windows 2000 Server
Problem
I'm trying to enumerate all the names in the global contacts on the exchange
server using CDO / ADO / (whatever I can).
In writing an...
| |
by: PRTC |
last post by:
I'm trying to use the global.asax in my new web aplication proyect
using the Application start to store my connection string
GLOBAL.ASAX.vb
Sub Application_Start(ByVal sender As Object,...
|
by: tshad |
last post by:
I am trying to set up some shared functions in my Global.asax file and can't
seem to get it to work.
In my Global.asax:
Public Class myUtils
inherits System.Web.HttpApplication
public...
|
by: John M |
last post by:
Hello,
On Microsoft Visual Studio .NET 2003,
I want to use some global elements,
that can be used in each one of my pages.
i.e I put a oleDBConnection on global.asax.vb
How can I use it...
|
by: hynek.cihlar |
last post by:
A strange behaviour thatI found in ASP.NET 2.0.
I am trying to issue a callback request (handled by
ICallbackEventHandler and RaiseCallbackEvent) and a regular GET request
in the client browser...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |