Re-Connect with Remote SQL Servr 2005 Database

B

BD

I am developing C# win form app to work with remote database on SQL
Server 2005. Problem scenario is as follows:

1. a form is open that has downloaded dataset to local cache
2. computer is put into stand-by or hibernation
3. later, computer is brought out of stand-by or hibernation
4. when trying to save or close form, SQL exception comes up
(remote system closed conn)

I know this relates to connection pooling, but I am having a problem
trying to solve it. I am letting VS 2005 do the majority of the code
writing for me so therefore unsure about where to insert code fix.
For example, would I put the code into the "update" statements? I
have seen some articles about coding multiple connections, however,
the articles reference older versions and I am new to C#. At this
time, I am letting VS code the conn string with the wizard.

Thanks for any help,

BD
 
N

Nicholas Paldino [.NET/C# MVP]

BD,

Are you keeping one connection open and then using that for all of your
data operations? If so, then that's probably the issue and you should open
a new connection when needed, perform your operations, and then close it.
 
C

Chris Alton [MSFT]

You can do a couple of things in this instance.

1. Turn off Connection Pooling altogether (this can cause a performance
decrease since opening new connections everytime is a performance hit).
a. This will only work if you are opening/closing connections after
all of your data access calls. If you are not then it will not help the
matter since the underlying TCP connection will still stay open and will
get closed once the computer goes into standby.

2. Add error handling around your data access calls that will trap for
connection errors and then reopen a new connection if a failure occurs.
a. If you are using connection pooling what you'd probably want to do
is purge the pool of all the connections to make sure all of the bad
connections are gone. Here is an article on the "ClearPool" function of a
SqlConnection object:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection
..clearpool(VS.80).aspx
b. If you are not using connection pooling then just do a new
SqlConnection in your error handler and try the operation again.
 
B

BD

BD,

Are you keeping one connection open and then using that for all of your
data operations? If so, then that's probably the issue and you should open
a new connection when needed, perform your operations, and then close it.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)


I am developing C# win form app to work with remote database on SQL
Server 2005. Problem scenario is as follows:
1. a form is open that has downloaded dataset to local cache
2. computer is put into stand-by or hibernation
3. later, computer is brought out of stand-by or hibernation
4. when trying to save or close form, SQL exception comes up
(remote system closed conn)
I know this relates to connection pooling, but I am having a problem
trying to solve it. I am letting VS 2005 do the majority of the code
writing for me so therefore unsure about where to insert code fix.
For example, would I put the code into the "update" statements? I
have seen some articles about coding multiple connections, however,
the articles reference older versions and I am new to C#. At this
time, I am letting VS code the conn string with the wizard.
Thanks for any help,

That is a good question. I let the wizard do all the code writing for
me. I would think the connection is closing once data is downloaded
and then reopened when trying to save data.
 
B

BD

You can do a couple of things in this instance.

1. Turn off Connection Pooling altogether (this can cause a performance
decrease since opening new connections everytime is a performance hit).
a. This will only work if you are opening/closing connections after
all of your data access calls. If you are not then it will not help the
matter since the underlying TCP connection will still stay open and will
get closed once the computer goes into standby.

2. Add error handling around your data access calls that will trap for
connection errors and then reopen a new connection if a failure occurs.
a. If you are using connection pooling what you'd probably want to do
is purge the pool of all the connections to make sure all of the bad
connections are gone. Here is an article on the "ClearPool" function of a
SqlConnection object:http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcon...
.clearpool(VS.80).aspx
b. If you are not using connection pooling then just do a new
SqlConnection in your error handler and try the operation again.

Forgive me, but where do I find the connection code when the wizard in
VS 05 writes the code?
 
C

Chris Alton [MSFT]

I personally recommend against letting the designer control your
connections since you don't really know what its doing and it can be
difficult to modify it once it is in there.

The connection code should be in the ClassName.Designer.cs file if I am not
mistaken.

I forgot to mention earlier that to turn off connection pooling you'd add
the following to your connection string: "Pooling=false", but as I stated
before I'd recommend against using this as it will create/destroy a new
TCP/IP connection everytime you open/close your SqlConnection.
 
B

BD

I personally recommend against letting the designer control your
connections since you don't really know what its doing and it can be
difficult to modify it once it is in there.

The connection code should be in the ClassName.Designer.cs file if I am not
mistaken.

I forgot to mention earlier that to turn off connection pooling you'd add
the following to your connection string: "Pooling=false", but as I stated
before I'd recommend against using this as it will create/destroy a new
TCP/IP connection everytime you open/close your SqlConnection.

Thanks for the help, I will look for it later today. Also, I will
follow up with the link you sent me. I will let you know what I find
out.
 
B

BD

I personally recommend against letting the designer control your
connections since you don't really know what its doing and it can be
difficult to modify it once it is in there.
The connection code should be in the ClassName.Designer.cs file if I am not
mistaken.
I forgot to mention earlier that to turn off connection pooling you'd add
the following to your connection string: "Pooling=false", but as I stated
before I'd recommend against using this as it will create/destroy a new
TCP/IP connection everytime you open/close your SqlConnection.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: BD <[email protected]>
Newsgroups: microsoft.public.dotnet.languages.csharp
Subject: Re: Re-Connect with Remote SQL Servr 2005 Database
Date: Mon, 08 Oct 2007 10:20:12 -0700
Organization:http://groups.google.com
On Oct 8, 12:11 pm, (e-mail address removed) (Chris Alton [MSFT])
wrote:
You can do a couple of things in this instance.
1. Turn off Connection Pooling altogether (this can cause a performance
decrease since opening new connections everytime is a performance hit).
a. This will only work if you are opening/closing connections after
all of your data access calls. If you are not then it will not help the
matter since the underlying TCP connection will still stay open and will
get closed once the computer goes into standby.
2. Add error handling around your data access calls that will trap for
connection errors and then reopen a new connection if a failure occurs.
a. If you are using connection pooling what you'd probably want to do
is purge the pool of all the connections to make sure all of the bad
connections are gone. Here is an article on the "ClearPool" function of a
SqlConnection
.clearpool(VS.80).aspx
b. If you are not using connection pooling then just do a new
SqlConnection in your error handler and try the operation again.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: BD <[email protected]>
Newsgroups: microsoft.public.dotnet.languages.csharp
Subject: Re-Connect with Remote SQL Servr 2005 Database
Date: Mon, 08 Oct 2007 09:45:22 -0700
Organization:http://groups.google.com
Lines: 21
I am developing C# win form app to work with remote database on SQL
Server 2005. Problem scenario is as follows:
1. a form is open that has downloaded dataset to local cache
2. computer is put into stand-by or hibernation
3. later, computer is brought out of stand-by or hibernation
4. when trying to save or close form, SQL exception comes up
(remote system closed conn)
I know this relates to connection pooling, but I am having a problem
trying to solve it. I am letting VS 2005 do the majority of the code
writing for me so therefore unsure about where to insert code fix.
For example, would I put the code into the "update" statements? I
have seen some articles about coding multiple connections, however,
the articles reference older versions and I am new to C#. At this
time, I am letting VS code the conn string with the wizard.
Thanks for any help,
BD
Forgive me, but where do I find the connection code when the wizard in
VS 05 writes the code?

Thanks for the help, I will look for it later today. Also, I will
follow up with the link you sent me. I will let you know what I find
out.

Sorry for the delay. I found the problem and solved it. I solved the
problem with try catch blocks creating a backup connection. Now, you
can switch between LAN, WLAN, or even Sprint PCS aircard, standby, or
hibernation. I have tested it with laptop over the past several
days. Works like a champ and switches connections as neccessary.
Thank you for all your help.
 
C

Chris Alton [MSFT]

Great! Glad to hear you got it worked out. Good luck with your future
coding.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: BD <[email protected]>
Newsgroups: microsoft.public.dotnet.languages.csharp
Subject: Re: Re-Connect with Remote SQL Servr 2005 Database
Date: Tue, 23 Oct 2007 09:23:05 -0700
I personally recommend against letting the designer control your
connections since you don't really know what its doing and it can be
difficult to modify it once it is in there.
The connection code should be in the ClassName.Designer.cs file if I am not
mistaken.
I forgot to mention earlier that to turn off connection pooling you'd add
the following to your connection string: "Pooling=false", but as I stated
before I'd recommend against using this as it will create/destroy a new
TCP/IP connection everytime you open/close your SqlConnection.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: BD <[email protected]>
Newsgroups: microsoft.public.dotnet.languages.csharp
Subject: Re: Re-Connect with Remote SQL Servr 2005 Database
Date: Mon, 08 Oct 2007 10:20:12 -0700
Organization:http://groups.google.com
On Oct 8, 12:11 pm, (e-mail address removed) (Chris Alton [MSFT])
wrote:
You can do a couple of things in this instance.
1. Turn off Connection Pooling altogether (this can cause a performance
decrease since opening new connections everytime is a performance hit).
a. This will only work if you are opening/closing connections after
all of your data access calls. If you are not then it will not help the
matter since the underlying TCP connection will still stay open and will
get closed once the computer goes into standby.
2. Add error handling around your data access calls that will trap for
connection errors and then reopen a new connection if a failure occurs.
a. If you are using connection pooling what you'd probably want to
do
is purge the pool of all the connections to make sure all of the bad
connections are gone. Here is an article on the "ClearPool" function of
a
SqlConnection
.clearpool(VS.80).aspx
b. If you are not using connection pooling then just do a new
SqlConnection in your error handler and try the operation again.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
From: BD <[email protected]>
Newsgroups: microsoft.public.dotnet.languages.csharp
Subject: Re-Connect with Remote SQL Servr 2005 Database
Date: Mon, 08 Oct 2007 09:45:22 -0700
Organization:http://groups.google.com
Lines: 21
I am developing C# win form app to work with remote database on SQL
Server 2005. Problem scenario is as follows:
1. a form is open that has downloaded dataset to local cache
2. computer is put into stand-by or hibernation
3. later, computer is brought out of stand-by or hibernation
4. when trying to save or close form, SQL exception comes up
(remote system closed conn)
I know this relates to connection pooling, but I am having a problem
trying to solve it. I am letting VS 2005 do the majority of the code
writing for me so therefore unsure about where to insert code fix.
For example, would I put the code into the "update" statements? I
have seen some articles about coding multiple connections, however,
the articles reference older versions and I am new to C#. At this
time, I am letting VS code the conn string with the wizard.
Thanks for any help,

Forgive me, but where do I find the connection code when the wizard in
VS 05 writes the code?

Thanks for the help, I will look for it later today. Also, I will
follow up with the link you sent me. I will let you know what I find
out.

Sorry for the delay. I found the problem and solved it. I solved the
problem with try catch blocks creating a backup connection. Now, you
can switch between LAN, WLAN, or even Sprint PCS aircard, standby, or
hibernation. I have tested it with laptop over the past several
days. Works like a champ and switches connections as neccessary.
Thank you for all your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top