Keep Alive SQL Server

[Modi]

Hello All,

I would like to ask you how can I keep alive SQL connection?

Thanks in advance.

[eclipsed4utoo]

why would you want to keep a sql connection alive?

[Modi]

Just for checking connection if something happened with server try to reconnect.

[monalin]

Sql connections you want to close as soon as you're done. You should try you best to never keep an SQL Connection open. They're a limited resource the server can only handle so many.

[Shuja Ali]

I am not sure what you are actually asking. But in case you want to check whether the connection is alive, you can use SqlConnection.State property to determine whether the connection is still active or handle SqlConnection.StateChange event to determine when the connection changes the state.

As already stated in post # 2, why would want to keep a connection active all the time? You should just open the connection and close it once the process is completed.

[Arjay]

Most modern db providers (like SqlConnection) internally cache connections.

So you wrap the connection in a using block, but open a connection every time that you need to hit the database.

It sounds expensive, but it really isn't because most providers hold onto a pool of connections and they keep a hold on them for a given time period.

Sure the provider will release the connections after a period of inactivity, but how reliable would a connection be if you kept a connection opened for several hours (or days)?

Chances are, you're just going to have to check that the connection is valid, find it isn't and reconnect anyway.

Try creating a connection every time and let the provider do it's pooling job.

 
using( SqlConnection cn = new SqlConnection( ... ) )
{
  // do db work here
}

[Modi]

Thank you for your replies ) I'll try all ideas.
Thanks again.