Why disconnect from a database?

Question

Background info: I'm coding with C#, using Microsoft SQL Server for databases.

I didn't find much on Google on the subject, so I'm asking here: should I always close a connection to my database after performing a query?

I'm torn between two solutions (maybe better ones exist...):

• either open the connection before querying, then close it right after the SQL query

• or open the connection at the start of my application, and before each SQL query check if the connection is still up and reopen it if needed.

In the past, I used the first solution but I discovered that opening a new connection can take quite some time (especially over a VPN connection to my LAN opened through 3G), and that it would slow down my application. That's why I decided to go with the second solution (in that case, my connection should be always up if we forget about time-out) and noticed some better performances.

Do I need to close the connection at the end of my application or can I forget about it?

Answer 1

Yes, you should close your connection after each SQL query. The database connection pool will handle the physical network connection, and keep it open for you. You say that you found that opening a connection can take some time - did you find that the application was really doing that multiple times?

(I hope your real application won't be talking directly to the database over 3G, btw... presumably this is just for development purposes...)

Answer 2

One important thing to remember is that there is a unique connection pool for each unique connection string you use... so always use the same connection string unless you need to connect to a different database (or have unique requirements).

Here is a good document on connection pooling with System.Data.SqlClient.SqlConnection.