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.

  • Was just reading that article when you posted it :) Thanks. – webnoob Mar 29 '12 at 9:59
  • 2
    The webpage you linked to is specific to SQL Server. Does .NET also provide automatic pooling when connecting to other databases eg - Oracle, Sqlite, MySql? – briddums Mar 29 '12 at 18:32
  • @briddums - I think that depends on the connector. .Net, for example, doesn't provide a MySql connector. It's written and maintained by MySql. And whilst it works, in my experience the earlier implementation were far from bug free. – ZweiBlumen Mar 29 '12 at 18:45
  • 1
    @briddums: Depends on the provider assembly. I am certain that both Microsoft's Oracle implementation and Oracle's own both support connection pooling, because I've used them. I've heard that there's a MySql one that does, and I'd expect the providers in Spring.NET to support pooling, but you're better off searching or asking the provider directly than asking me. – pdr Mar 29 '12 at 18:50
  • 1
    It should known that opening, running a query, and disposing a connection, even in a loop, is equally as fast, and sometimes FASTER than opening it once and looping the query. Always just dispose. It's more secure, and FAST. Do not worry about the overhead of the getting a connection from the pool--it is so trivial. – smdrager Oct 2 '12 at 17:33

Best practice it to create one connection per query - and in the case of displaying data, best practice is to have the query bring in all the needed data in one go.

Background information:

In .NET, calling SqlConnection.Open() will by default always transparently use connection pooling (see "Using Connection Pooling with SQL Server" on MSDN). So you can just grab a new connection using Open(), and call Close() when you're done, and .NET will do the right thing.

Note that without connection pooling, one connection per query would be a very bad idea because creating real database connections can be very costly (authentication, network overhead etc.), and the number of simultaneous open connections is usually very limited.

  • 7
    @webnoob - Since .NET uses connection pooling, no, it doesn't. The reason is that connections can get closed, reallocated etc - so reusing a connection is not good practice. – Oded Mar 29 '12 at 9:51
  • 11
    -1 The answer is rather misleading. Creating a connection per query is a very bad idea. What you probably mean is "retrieve a new connection for each query from the connection pool" - but that's not the same as creating a connection. – sleske Mar 29 '12 at 13:54
  • 1
    @sleske - How is that different from the answer by pdr? – Oded Mar 29 '12 at 14:05
  • 3
    @Oded: Ah, I see. In .NET, calling SqlConnection.Open() will always transparently use connection pooling. So the difference between "open a connection" and "retriebve a connection from a pool" does not exist. My misunderstanding. I took the liberty of editing a little explanation into the question, and took back the vote. – sleske Mar 29 '12 at 14:28
  • 1
    Thanks for this answer. Although I use PHP, the best practice was good to know. My colleague told me that he uses only a single connection throughout the life of an app, which I found discomforting. – dotslash Dec 11 '14 at 6:48

protected by gnat Feb 12 '16 at 18:51

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.