CODEWITHSUNDEEP
c#asp.netsql-server
aj go
aj go

Reputation: 714

SqlConnection object into ASP.NET SessionState object

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();

Upvotes: 0

Views: 145

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82504

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 the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement 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.
    }
}

Upvotes: 1

Related Questions