At the moment I create a database connection when my web page is first loaded. I then process the page and run any queries against that conection. Is this the best way to do it or should I be creating a database connection each time I run a query?

p.s It makes more sense to me to create 1 connection and use it but I don't know if this can cause any other issues.

I am using C# (ASP.NET) with MSSQL.

If you create one per query / transaction, it is much easier to manage "closing" the connections.

I can see why common sense dictates that you should open one and use it throughout, but you will run into problems with dropped connections and multithreading. So your next step will be to open a pool, say of 50, connections and keep them all open, doling them out to different processes. And then you'll find out that this is exactly what the .NET framework does for you already.

If you open a connection when you need it and dispose of it when you've finished, that will not actually close the connection, it'll just return it to the connection pool to be used again.
