sql connection pool

Question

I am making multiple queries to a database over a short period continiously. In the past I have always closed the connection and reopened when I needed to talk to sqlserver again - this served me well. But now when I try to do this over a very short period I get no connections availible messages. I then thought I would just try and keep the connection open for the life of the application, so far so good I haven't run into any problems. I am just wondering what others think of this approch...am I on a short road to disaster?

One other thing I have nocied that I really don't have to call connection open() method, the connection just seem to work without the call - what's the deal with that?

void main()
{
SqlConnection cn = new SqlConnection();
cn.ConnectionString = <connection>;
tmr.interval=100;
tmr.Elapsed += new System.Timers.ElapsedEventHandler(tmr_Elapsed);
}

void tmr_elapsed()
{
tmr.Enabled = false;
SqlDataAdapter da = new SqlDataAdapter(query, cn);
da.Fill(dt);
tmr.Enabled = true;
}

C#, sql-server 2000, .net 2.0

Answer 1

The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

For more information look at this question.

Answer 2

You really shouldn't keep this connection open and then reuse it. Granted, your timer is very fast (every 100 ms) but at the same time, you are keeping that connection open for quite a long time.

Rather, you should open the connection and Dispose of it properly when you are done in the event handler for the timer. If you use a using statement for your connection you should be fine and not worry about resource exhaustion.

As for not having to open the connection, the SqlDataAdapter does it for you, opening and closing the connection as needed. It will leave the connection in the state it was in when you passed it in after it does its work (it will keep it open if it was open, or open then close it if it was closed).