Opening a database connection once or with each database transaction?

Question

I am currently creating webportal with ASP.NET, which is heavily dependent on database usage. In principle, every request (almost every: P) GET from any user will lead to a request to the database from a web server.

Now I'm really new to this, and performance really worries me. Due to my lack of experience in this area, I really do not know what to expect.

My question is to use ADO.NET, would it be wiser to select a static connection open from the web server to the database, and then check the integrity of this connection to the server before each request to the database? “Or is it better for me to open the connection before each request, and then close it?”

In my head, the first option will be better, since you save the acknowledgment time, etc. before each request, and you save memory both in the database and on the server side, since you have only one connection, but are there any failures in this approach? Can two requests be sent simultaneously to potentially destroy each other's integrity or to mix the returned data set?

I tried searching everywhere here and on the Internet to find some best practices about this, but no luck. The closest thing I realized was: it’s safe to keep database connections open for a long time , but it seems more suitable for distributed systems where you have more than one database user, whereas I only got my web server ..

Answer 1

.

, . , .

- ...

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

Answer 2

ADO.NET . , , , . . , , using.

Answer 3

. ADO.NET , . , 2-, 3- , , .

, .

Answer 4

, . ?

, Cache , ( ), .

Answer 5

ADO.NET . , , .

Answer 6

, . .

, , . , .

Answer 7

. , .

Answer 8

, Connection Pooling - , ( , , ), , , .

, , ( ). , ADO.NET .

Answer 9

, , , - , ? ? ?

But overall, yes, it’s wiser to have an open connection that you have been maintaining for some time than to re-open the database connection every time; depending on the type of connection, network problems and the phase of the moon, it may take most of a second or more to make the initial connection; if your workload is such that you expect more than GET every five seconds or so, you will be happier with a persistent connection.