CODEWITHSUNDEEP
asp.netsql.netsql-server
cooolstaff
cooolstaff

Reputation: 75

Is that possible that dispose all connections to SQL Server when session end method asp.net

Is there any method to close/dispose existing SQL Server connections when users session end in ASP.NET, because I get that error I also use Entity Framework in my application

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Upvotes: 0

Views: 1094

Answers (4)

Ajay
Ajay

Reputation: 6590

This could be because the pool of SQL Connections is exhausted (this is different from the app pool.) You can check that by increasing the pool size through the connection string:

Integrated Security=SSPI;Initial Catalog=northwind;Max Pool Size=100;

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063734

Firstly, forget about sessions. Your connections should not be tied to the session at all - if they are, there is a problem. If the issue is that your EF contexts are tied to the session: then again, I'd say you're doing it very wrong.

There are (IMO) two reasonable scopes for connections in a web app:

  • per call-site - i.e. where you obtain the connection whenever you need it (perhaps multiple times per request) and immediately dispose it before exiting the same method. This is usually achieved via using blocks.
  • per request - where you hold the request open on the request and re-use it, then close / dispose it in the end of each request. This can be achieved using the global context and the end-request event.

Upvotes: 1

Szymon
Szymon

Reputation: 43023

Instead of trying to keep the connection for a longer period of time and closing it at the end, you should open and close connections each time you need to do a job in SQL Server.

Connection pooling is efficient in modern databases and there is no need to try to keep connection open to long. This is especially true in situations like websites which won't scale well if you keep your connections open too long.

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 157088

Use the Session_End in Global.asax

protected void Session_End(object sender, EventArgs e)
{
    // End connection
}

Upvotes: 0

Related Questions