c# - SQL Connection Limit and managing Entities -

so, manage large web application , set unique method of handling entity framework data context caching across different events on page. had seen before there 2 methods managing context across pages in entity framework:

• create static context shared connections.
• use connection disposable within events.

because our data changes rapidly me want use static context started disposable connections within each event. became problematic since our pages robust , led lot of overhead opening , closing contexts. also, limited amount of caching within page since have bring data in fresh every event. decided go new had never seen done open 1 context each request , hold open entire request. gave best flexibility between both methods , code included below:

public class frontendpage : system.web.ui.page {       private pagecontext _context;     public database.databaseentities context     {                  {             if (_context == null)                 _context = new pagecontext();             return _context.context;         }     } }  public class pagecontext {      public database.databaseentities context;     public pagecontext()     {         context = database.databaseentities();     } }  

this led new , interesting problem. started getting intermittent error connecting database. once refresh application pool, goes away. thing find explain entity connection method of handling db context not disposing correctly , leaving open connections when garbage collection fails clear them out. after day of uses connection limit sql server , results in following error.

named pipes provider, error: 40 - not open connection sql server server error in '/' application. access denied description: unhandled exception occurred during execution of current web equest. please review stack trace more information error , originated in code.  exception details: system.componentmodel.win32exception: access denied  source error:  unhandled exception generated during execution of current web request. information regarding origin , location of exception can identified using exception stack trace below. 

since i've update schedule recycling application pool down 4 hour increments, not permanent solution. because of way cache, want find way make method of 1 context per request work because perfect application, need means make sure open contexts closed correctly.

is there better way handle or means of making sure connection closed after request completed use prevent leaving connections hanging?

or error being caused else?

so, managed figure out best solution this. added page unload event custom page class checks if there open context , closes connection out. @ point makes best method i've found of handling connection because offers greatest balance of holding cache , making sure objects date.

protected void page_unload(object sender, eventargs e) {     if (_context != null)     {         if (_context.context.connection.state == connectionstate.open)             _context.context.connection.close();         _context.context.dispose();         _context.context = null;         _context = null;     } }