I just wonder if I do:
Session["MyDbConnection"] = new SqlConnection("connectionString");
(Session["MyDbConnection"] as SqlConnection).Open();
I can also do this when I navigate to another page?:
(Session["MyDbConnection"] as SqlConnection).Close();
I'm not sure if that's possible, but even if it is, it's a bad idea. SqlConnection is an IDisposable and should only ever be used as a local variable inside a using statement.
When disposed, it's closed automatically and the connection pool can be used.
Note the following paragraph from SQL Server Connection Pooling page on Microsoft docs:
Caution
We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either theCloseorDisposemethods of theConnectionobject, or by opening all connections inside ausingstatement in C#, or aUsingstatement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool.
The correct way to use SqlConnection is this:
using(var con = new SqlConnection(connectionString))
{
using(var cmd = new SqlCommand(commandText, con)
{
// Add parameters if needed:
// cmd.Parameters.Add("name", SqlDbType.<some SqlDbType Member>).Value = <some value>
// Execute your command here and do whatever you need with it.
}
}
will it instantly close the connection once the user navigates to other page? No. Each web request is independent.