Opening the database connection once or on every databaseaction?

Question

Im currently creating a webportal with ASP.NET which relies heavily on database usage. Basically, every (well almost every :P ) GET query from any user will result in a query to the database from the webserver.

Now, I'm really new at this, and I'm very concerned about performance. Due to my lack of experience in this area, I don't really know what to expect.

My question is, using ADO.NET, would it be a smarter choice to just leave a static connection open from the webserver to the database, and then check the integrety of this connection serverside before each query to the database? - Or, would I be better off opening the connection before each query and then close it afterwards?

In my head the first option would be the better as you save time handshaking etc. before each query and you save memory both on the database and the server side since you only have one connection, but are there any downfalls to this approach? Could 2 queries send at the same time potentially destroy each others integrity or mix the returned dataset?

I've tried searching everywhere in here and on the web to find some best-practices about this, but with no luck. Closest I got was this: is it safe to keep database connections open for long time , but that seems to be more fitting for distributed systems where you have more than one user of the database, whereas I only got my webserver..

Answer 1

You're way early to be worrying about performance.

Anyhow, connections are pooled by the framework. You should be opening them, using them, and disposing of them ASAP.

Something like...

public object Load()
{
  using (SqlConnection cn = new SqlConnection(connectionString))
  using (SqlCommand cm = new SqlCommand(commandString, cn))
  {
    cn.Open();
    return cm.ExecuteScalar();
  }
}

Answer 2

It's better to let ADO.NET handle the connection pooling. It'll persist the connection if it thinks it needs to, but don't use a static connection object. That just smells. It would be better to pass the connection object around to methods that need it, and create the connection in a using block.