I have a report engine that monitors a Queue table. It runs a Select TOP 1 Query every 2-3 seconds. What I have done in the past with ADODB was open a global connection for the program and leave it open for the duration of the .EXE lifetime (it never caused resource issues in the past). I'm wanting to migrate this to use SqlClient through the .NET Framework 2 and I've found that with SQL Server 2000 it doesn't support multiple connections if the connection has one open reader without opening more connection objects?
Is there a way to get around this with 2000 as the DB Server. I could open a new connection and close it every 2-3 seconds but wouldn't that be less efficient than opening a single connection that's just reused over and over again?