Remember all this is in the context of the .Net ecosystem.
Developers sometimes want to "optimize" their code to re-use their connection objects. Given the context of this question, this is almost always a mistake.
ADO.Net has a feature called Connection Pooling. When you create and open a new connection object, what you're really doing is requesting a connection from a pool. When you close a connection, you return it to the pool.
It's important to understand the objects we use directly in code: SqlConnection, MySqlConnection, OleDbConnectio, etc, are all just wrappers around a real underlying connection managed by ADO.Net, and the ADO.Net real connections are much "heavier" and more expensive from a performance standpoint. It's these underlying objects that have worries like authentication, network transit, encryption, and those things far outweigh the small amount of memory in the object you actually see in your own code.
When you try to re-use your connection object, you break the ability for ADO.Net to effectively manage the important underlying connections. You gain efficiency in the small thing at the expense of the much bigger thing.
Re-using a connection across an application or http request can also force you to accidentally serialize something that might otherwise be able to run in parallel, and become a performance bottleneck. I've seen this happen in real applications.
In the case of the web page example here, where you at least only keep the small connection for the duration of a single http request/response, you could gain even more efficiency by evaluating what queries you run in your request pipeline, and try getting them down to as few separate requests to the database as possible (hint: you can submit more than one query in a single SQL string, and use DataReader.NextResult()
or check different tables in a DataSet
to move between them).
In other words, rather than thinking in terms of re-using one connection for an application or http request vs one connection per query, think in terms of one connection for each time you call out to the database... each round trip. Then try to minimize the number of connections by minimizing the number of those trips. In this way you can satisfy both goals.
But that's just one kind of optimization. There's also optimizing programmer time, and gaining effective code re-use. Developers don't want to write the same boilerplate code over and over again just to get a connection object that's open and ready-to-use. It's not only tedious, it's a way to introduce bugs into a program.
Even here, though, it's generally better to have one connection per query (or round trip). There are other patterns you can use to help avoid re-writing the same boilerplate code. Here is one example I like, but there are many others.