SqlConnection and DB connection failures detection

[Tomasz]

Hello everybody,

i'm writing an application, which should be connected all the time to
the database. So i want to test it in few ways if reconnection works in
order. First thing i tried to do is killing my apps process from MS SQL
(2005 Express). Problem is that after i have still "Open" status for
SqlConnection.Status. That means, that "Status" property is not enough
to test I would like to not to test the connection everytime with
some simple "select" queries, but if there is an another solution ?
Thanks in advance for any help.

 

[Tomasz]

Hello everybody,

i'm writing an application, which should be connected all the time to
the database. So i want to test it in few ways if reconnection works in
order. First thing i tried to do is killing my apps process from MS SQL
(2005 Express). Problem is that after i have still "Open" status for
SqlConnection.Status. That means, that "Status" property is not enough
to test I would like to not to test the connection everytime with
some simple "select" queries, but if there is an another solution ?
Thanks in advance for any help.

Finally i did it by a combination of exception and loop blocks, but i'd
love to have an proper event or property to test :/

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Hello everybody,

i'm writing an application, which should be connected all the time to
the database.

Why ?
This will seriusly hamper the performance/scalability of both the DB and the
app

So i want to test it in few ways if reconnection works in
order. First thing i tried to do is killing my apps process from MS SQL
(2005 Express). Problem is that after i have still "Open" status for
SqlConnection.Status. That means, that "Status" property is not enough
to test I would like to not to test the connection everytime with
some simple "select" queries, but if there is an another solution ?

I think that the status is not refhresed until a request is sent and an
error is received.


Again, IMO I think this is a bad design criteria.
What are you trying to do?

 

[Tomasz]

Hi,



Why ?
This will seriusly hamper the performance/scalability of both the DB and the
app

So, should i connect each time i want to launch a query (only insert
queries i'm performing)? Even if there are many such queries at the
time ? I mean, all these queries will be done from one machine, so
isn't better if i don't need to connect each time i want to insert a
row ?

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

So, should i connect each time i want to launch a query (only insert
queries i'm performing)?

YES !!

Even if there are many such queries at the
time ?
YES
I mean, all these queries will be done from one machine, so
isn't better if i don't need to connect each time i want to insert a
row ?

Even if you close the connection it does not mean that the connection is
"really" closed. SQL server use something named connection pooling that does
indeed manage the connections for you. MS recommendation is that you open
the connection the latter possible and you close it the sooner you can.

 

[Tomasz]

Even if you close the connection it does not mean that the connection is
"really" closed. SQL server use something named connection pooling that does
indeed manage the connections for you. MS recommendation is that you open
the connection the latter possible and you close it the sooner you can.

Didn't know this! Man learns the whole life! Thanks for tips!

 

[Tomasz]

YES !!

Ok, i changed this - my code is now much more simplified!
Now question - after performing insert query I'm closing connection,
but my process still exists in SQL Server Activity Monitor (even if i
close my app) with "Sleeping" status. I believe that this is what you
said about functionality of MS SQL (Damm i will order an MS SQL
bible-like book immediatelly!). How long server will keep this process
? Is it good idea to play with such timeouts to improve performance
(App is going to work 24h/day)?

Then second question - I'm using SQL Server authentication instead of
AD one, i think this is better solution for more missions-critical
apps, is it true ? Theoretically even if the whole my network is down
this application will still work (SQL server is on the same PC). What
would you say ?

Thanks in advance for your help!

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Ok, i changed this - my code is now much more simplified!
Now question - after performing insert query I'm closing connection,
but my process still exists in SQL Server Activity Monitor (even if i
close my app) with "Sleeping" status. I believe that this is what you
said about functionality of MS SQL (Damm i will order an MS SQL
bible-like book immediatelly!). How long server will keep this process
? Is it good idea to play with such timeouts to improve performance

I would advise you not to. IF and only IF you are experimenting connection
problems you should modify the pooling. which is configurable btw. see
"connection pooling" in msdn

(App is going to work 24h/day)?

Then second question - I'm using SQL Server authentication instead of
AD one, i think this is better solution for more missions-critical
apps, is it true ?

Not sure about this. I do also prefer using SQL auth, in my current office
( florida department of transportation) it may takes 1 week to create a new
account in the domain.
Not sure what is best in mission-critical apps though.

Theoretically even if the whole my network is down
this application will still work (SQL server is on the same PC). What
would you say ?

Not sure about this. maybe you will get a better answer in a SQL NG

 

[Willy Denoyette [MVP]]

| Ignacio Machin ( .NET/ C# MVP ) wrote:
| > >> Why ?
| > >> This will seriusly hamper the performance/scalability of both the DB
and
| > >> the
| > >> app
| > >
| > > So, should i connect each time i want to launch a query (only insert
| > > queries i'm performing)?
| >
| > YES !!
|
| Ok, i changed this - my code is now much more simplified!
| Now question - after performing insert query I'm closing connection,
| but my process still exists in SQL Server Activity Monitor (even if i
| close my app) with "Sleeping" status. I believe that this is what you
| said about functionality of MS SQL (Damm i will order an MS SQL
| bible-like book immediatelly!). How long server will keep this process
| ?

The physical connection drops after ~6 minutes, provided no logical
connections exists.

Is it good idea to play with such timeouts to improve performance
| (App is going to work 24h/day)?
|

No.

| Then second question - I'm using SQL Server authentication instead of
| AD one, i think this is better solution for more missions-critical
| apps, is it true ?

Authentication is all about security, not about being mission-critical. When
using integrated security, there is no way to expose the credentials used to
authenticate, while using SQL authentication, you can expose your secrets.

Theoretically even if the whole my network is down
| this application will still work (SQL server is on the same PC). What
| would you say ?
|

Yep. And authentication is less of a concern, credentials aren't going over
the network anyway.



Willy.

 

[Tomasz]

Hello!

Ok, then the last, i hope question.

Is it a good idea to launch insert queries in separate threads ?
Problem is my application waits for input from a user (proximity cards
reader), so could be, that for some reason procedure will freeze my
app, and then it will be impossible to react on next input, or I'm
wrong?

Thanks very much from your response