By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,906 Members | 1,744 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,906 IT Pros & Developers. It's quick & easy.

Re-Connect with Remote SQL Servr 2005 Database

P: n/a
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

Oct 8 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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]
- mv*@spam.guard.caspershouse.com

"BD" <bw******@charter.netwrote in message
news:11**********************@57g2000hsv.googlegro ups.com...
>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
Oct 8 '07 #2

P: n/a
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/lib....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 <bw******@charter.net>
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

Oct 8 '07 #3

P: n/a
BD
On Oct 8, 11:53 am, "Nicholas Paldino [.NET/C# MVP]"
<m...@spam.guard.caspershouse.comwrote:
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]
- m...@spam.guard.caspershouse.com

"BD" <bwald...@charter.netwrote in message

news:11**********************@57g2000hsv.googlegro ups.com...
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
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.

Oct 8 '07 #4

P: n/a
BD
On Oct 8, 12:11 pm, cal...@online.microsoft.com (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 object:http://msdn2.microsoft.com/en-us/lib...lclient.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.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: BD <bwald...@charter.net>
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?

Oct 8 '07 #5

P: n/a
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 <bw******@charter.net>
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, cal...@online.microsoft.com (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
object:http://msdn2.microsoft.com/en-us/lib...lclient.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.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
From: BD <bwald...@charter.net>
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?

Oct 8 '07 #6

P: n/a
BD
On Oct 8, 1:10 pm, cal...@online.microsoft.com (Chris Alton [MSFT])
wrote:
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 <bwald...@charter.net>
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, cal...@online.microsoft.com (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

object:http://msdn2.microsoft.com/en-us/lib...lclient.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.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
From: BD <bwald...@charter.net>
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.

Oct 8 '07 #7

P: n/a
BD
On Oct 8, 3:30 pm, BD <bwald...@charter.netwrote:
On Oct 8, 1:10 pm, cal...@online.microsoft.com (Chris Alton [MSFT])
wrote:
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 <bwald...@charter.net>
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, cal...@online.microsoft.com (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
object:http://msdn2.microsoft.com/en-us/lib...lclient.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.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
From: BD <bwald...@charter.net>
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.

Oct 23 '07 #8

P: n/a
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 <bw******@charter.net>
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
On Oct 8, 3:30 pm, BD <bwald...@charter.netwrote:
On Oct 8, 1:10 pm, cal...@online.microsoft.com (Chris Alton [MSFT])
wrote:
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 <bwald...@charter.net>
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, cal...@online.microsoft.com (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
>
object:http://msdn2.microsoft.com/en-us/lib...lclient.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.
-------------------------------------
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers
no
rights.
--------------------
From: BD <bwald...@charter.net>
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.

Oct 24 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.