opening & closing SQL connection

[LeAnne]

my application polls a database every X seconds.
Some times X = 0.5 seconds (500 milli seconds)

The way the code has been written, the SQL connection is opened & closed
during every poll.
Is this effecient?

Should i be leaving the connection opened & then polling the database every
500 milli seconds?
Or will the connection be simply picked up from the connection pool & hence
there isn't a drop in performance.

And also, there there's a message to be processed, then the processing of
this message would take 1-5 seconds, not longer.
What should i do?

 

[Guest]

Best practices coding form dictates that you should open the connection
immediately prior to using it, and close it immediately following, which
allows it to return to the ADO.NET connection pool. You could experiment
with holding a connection open but you have to make sure you do not have
multiple threads doing this (such as a web page where hundreds of separate
requests would end up opening the same connection).
Regarding the timing issue, probably your processing should be done on a
background thread. Take a look at the BackgroundWorker class for a simplified
mode for that.
Pete