SqlConnection - Reset

[John J. Hughes II]

Is there a way of resetting the SqlConnection with closing/opening it again.
I am not seeing anything called reset.

Regards,
John

 

[Marc Gravell]

Indirectly... since connections are often pooled, closing and opening
won't close the underlying connection, but it will force it to reset
that connection for you (to clear up any temp data etc) - which sounds
more-or-less like what you want anyway? You could perhaps run a trace
to see what (if any) SQL is issued, but this may occur under the
bonnet...

Marc

 

[John J. Hughes II]

Basically that is what I want, clean up the connection... Thanks.

Regards,
John

 

[Christof Nordiek]

Hi Marc and John,

yes, the Open method of SqlConnection sends some reset command to the
server.
I've seen it before in the trace. I suppose, this is so, that the Connection
object can be used indepentently from any changes made in the underlying
connection, if it is a repooled connection.

Christof

 

[John J. Hughes II]

Basically I opened a connection and leave it open forever which normally
works fine. Before using the connection each time I checked the connection
state and if it is anything besides open I closed the connection and
reopened it.

What I would like to do assuming the connection is fetching or in error
would be to reset the connection instead of closing it.

public bool CheckConnection()
{

.... other code

switch (_Connection.State)
{
case System.Data.ConnectionState.Closed:
if (this.OnError != null)
this.OnError("Opening SQL"); ;
this._Connection.Open();
return true; ;
case System.Data.ConnectionState.Broken:
case System.Data.ConnectionState.Connecting:
case System.Data.ConnectionState.Fetching:
case System.Data.ConnectionState.Executing:
this.OnError("Closing SQL");
this._Connection.Close();
System.Threading.Thread.Sleep(TimeSpan.FromSeconds(1));
cnt++;
break;
case System.Data.ConnectionState.Open:
return true;
}

.... error handling

return false;
}

Regards,
John

 

[PS]

Basically I opened a connection and leave it open forever which normally
works fine. Before using the connection each time I checked the
connection state and if it is anything besides open I closed the
connection and reopened it.

You are best to close the connections when you are finished with them and
open them only if necessary. Why do you close and reopen a connection if it
is already open?

PS

 

[John J. Hughes II]

You are best to close the connections when you are finished with them and
open them only if necessary. Why do you close and reopen a connection if
it is already open?

Two reasons. One is because of error or undisired state, it seems to be
the only way to get the connection back to the desired state. Two, which I
am not currently doing, is to save resouces on the SQL server. If I am not
going to need the connection for a while it seems better to close it, since
I am not releasing the SqlClient I am not sure if this has any affect. I am
also think this might reset the connection.

 

[John J. Hughes II]

Oh the profiler show exec sp_reset_connection when a connection is closed
so I am assuming that is how the correction is reset.

Regards,
John

 

[Dave Sexton]

Hi,

You are best to close the connections when you are finished with them and
open them only if necessary. Why do you close and reopen a connection if
it is already open?

Unless the OP requires pessimistic concurrency and the server and client
applications are within the same, private network or DMZ. This might also
increase performance of the application if many connections would be closed
and reopened otherwise.