The connection is already closed.

18 posts, 0 answers
  1. Dennis
    Dennis avatar
    3 posts
    Member since:
    Jan 2009

    Posted 28 Apr 2009 Link to this post

    Hi,

    how i have to handle this exception?

    Sample Code: (The Scope comes from the ObjectScopeProvider)
    1 //...  
    2 var result =  
    3     from b  
    4     in Scope.Extent<Category>()  
    5     where b.ID == id  
    6     select b;  
    7  
    8 return result.Single();  
    9 //... 

    Exception:
    Telerik.OpenAccess.Exceptions.DataStoreException wurde nicht von Benutzercode behandelt.
      Message="Error creating PreparedStatement: Telerik.OpenAccess.RT.sql.SQLException: Telerik.OpenAccess.Exceptions.InvalidOperationException: The connection is already closed. ---> Telerik.OpenAccess.Exceptions.InvalidOperationException: The connection is already closed.\r\n   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp..ctor(ConnectionImp conImp, String sql, IADOFactory factory, Boolean scrollable)\r\n   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.ConnectionImp.prepareStatement(String sql)\r\n   bei OpenAccessRuntime.Relational.conn.LoggingConnection.prepareStatementImp(String sql, Int32 resultSetType, Int32 resultSetConcurrency, Key key)\r\n   bei OpenAccessRuntime.Relational.conn.PreparedStatementPool.borrowPS(Key pkey)\r\n   bei OpenAccessRuntime.Relational.conn.LoggingConnection.prepareStatement(String sql)\r\n   --- Ende der internen Ausnahmestapelüberwachung ---\r\n   bei OpenAccessRuntime.Relational.conn.LoggingConnection.prepareStatement(String sql)\r\n   bei OpenAccessRuntime.Relational.conn.PooledConnection.prepareStatement(String sql)\r\n   bei OpenAccessRuntime.Relational.fetch.FetchSpec.createFetchResult(RelationalStorageManager sm, Connection con, Object[] param, Boolean forUpdate, Boolean forCount, Int64 fromIncl, Int64 toExcl, Int32 fetchSize, Boolean scrollable, Int32 maxRows, Int32 skip)\nSQL:\nSELECT [ID], [Name], [ParentCategoryID], [Type], [Url] FROM [Category] WHERE [ID] = ?         "
      Source="Telerik.OpenAccess"
      CanRetry=false
      StackTrace:
           bei Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
           bei Telerik.OpenAccess.RT.ExceptionWrapper.Throw()
           bei OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.fetchNextQueryResult(ApplicationContext context, RunningQuery runningQuery, Int32 skipAmount)
           bei OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.getNextQueryResult(QueryResultWrapper aQrs, Int32 skipAmount)
           bei OpenAccessRuntime.DataObjects.ForwardQueryResult.get_Item(Int32 indexParam)
           bei Telerik.OpenAccess.RT.ListEnumerator.setCurrent(Int32 _pos)
           bei Telerik.OpenAccess.RT.ListEnumerator.Move(Int32 relative)
           bei Telerik.OpenAccess.RT.ListEnumerator.MoveNext()
           bei Telerik.OpenAccess.Query.TypedEnumerator`1.System.Collections.IEnumerator.MoveNext()
           bei System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
           bei lambda_method(ExecutionScope )
           bei System.Linq.EnumerableExecutor`1.Execute()
           bei System.Linq.EnumerableQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)
           bei System.Linq.Queryable.Single[TSource](IQueryable`1 source)
           bei Telerik.OpenAccess.Query.ExtensionMethods.Single[T](IObjectScopeQuery`1 source)
           bei DataAccessLogic.Manager.CategoryManager.GetByID(Int32 id) in ...\Source\DataAccessLogic\Manager\CategoryManager.cs:Zeile 109.
           bei Controls_DocumentViewControl.get_SubCategory() in ...\Source\Web\Controls\DocumentViewControl.ascx.cs:Zeile 82.
           bei Controls_DocumentViewControl.get_SearchInfo() in ...\Source\Web\Controls\DocumentViewControl.ascx.cs:Zeile 52.
           bei Controls_DocumentViewControl.get_DataSource() in ...\Source\Web\Controls\DocumentViewControl.ascx.cs:Zeile 61.
           bei Controls_DocumentViewControl.Page_Load(Object sender, EventArgs e) in ...\Source\Web\Controls\DocumentViewControl.ascx.cs:Zeile 114.
           bei System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
           bei System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
           bei System.Web.UI.Control.OnLoad(EventArgs e)
           bei System.Web.UI.Control.LoadRecursive()
           bei System.Web.UI.Control.LoadRecursive()
           bei System.Web.UI.Control.LoadRecursive()
           bei System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
      InnerException:

  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 29 Apr 2009 Link to this post

    Hello Dennis,
    OpenAccess has a connection timeout for long running database connections. The connection to the server is normally used only for a short time and then returned to the connection pool.
    The default for the timeout is 2 minutes. You can change that in the backend configuration dialog.
    The error itself might be caused if you are not disposing the query results. In a case where not the complete result is red from the server the connection might be still open for a longer time. This can be avoided by disposing the query result before leaving the method.


    All the best,
    PetarP
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. DevCraft banner
  4. Dennis
    Dennis avatar
    3 posts
    Member since:
    Jan 2009

    Posted 29 Apr 2009 Link to this post

    Thx for the help. I found my mistake and can fix it now. I never dispose my scope after i change my datalayer concept.
  5. Vitaliy ***
    Vitaliy *** avatar
    1 posts
    Member since:
    Jun 2009

    Posted 30 Jun 2009 Link to this post

    Was really helpful for me!
  6. Mehul Patni
    Mehul Patni avatar
    2 posts
    Member since:
    Jul 2009

    Posted 04 Jul 2009 Link to this post

    Hi Dennis,

                     I am getting the same error, can you please tell me where I have to dispose the scope??

    Thanks
  7. Dennis
    Dennis avatar
    3 posts
    Member since:
    Jan 2009

    Posted 06 Jul 2009 Link to this post

    The Source of this Error was a open Query.
    var result =  
        Scope.GetSqlQuery(cmdBuilder.ToString(), typeof(Document),string.Empty).Execute();  
     
    // i don't dispose my result and get the timeout Error  
    result.Dispose(); 
    ---------------------------------------------------------------------------------------------------------------------------------------
    You can use a "using-block" to handle your Scope easy.
    using(IObjectScope scope = ObjectScopeProvider1.ObjectScope())  
    {  
        // do something  

    A good (but complex) sample you found here.
    http://www.telerik.com/support/kb/orm/general/northwind-wcf-demo.aspx
    Samplecode of the Project.
    1 public string CreateCustomers(NSV.NW.DataContracts.Customer customer)  
    2 {  
    3     string customerId = string.Empty;  
    4  
    5     using (IObjectScope scope = NSV.NW.OADataModel.ObjectScopeProvider.GetNewObjectScope())  
    6     {  
    7         NSV.NW.OADataModel.Customer oaCustomer = new NSV.NW.OADataModel.Customer()  
    8         {  
    9             CompanyName = customer.CompanyName,  
    10             ContactName = customer.ContactName,  
    11             ContactTitle = customer.ContactTitle,  
    12             Address = customer.Address,  
    13             City = customer.City,  
    14             Region = customer.Region,  
    15             PostalCode = customer.PostalCode,  
    16             Country = customer.Country,  
    17             Phone = customer.Phone,  
    18             Fax = customer.Fax  
    19         };  
    20  
    21         scope.Transaction.Begin();  
    22         scope.Add(customer);  
    23         scope.Transaction.Commit();  
    24  
    25         customerId = oaCustomer.CustomerID;  
    26    }  
    27  
    28    return customerId;  
    29
  8. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 09 Jul 2009 Link to this post

    Hi Mehul,
    what Denis provided should be sufficient to solve your problem. In addition I would like to point you to one of my previous posts in this thread where the connection timeout has been explained and how it can affect your application. If you have any further questions please do not hesitate to contact us.

    Sincerely yours,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  9. Robert Gibbins
    Robert Gibbins avatar
    6 posts
    Member since:
    Sep 2009

    Posted 14 Oct 2009 Link to this post

    Hello All,

    We have a similar problem:

                query = Scope.GetSqlQuery(sqlQuery, GetType(T), Params)
                Dim QueryResults As IQueryResult
                QueryResults = query.Execute(args)
                Try
                    For Each QueryResult In QueryResults
                        Results.Add(DirectCast(QueryResult, T))
                    Next
                Catch ex As Exception
                    QueryResults.Dispose()
                    QueryResults = Nothing
                    Throw
                End Try
                QueryResults.Dispose()
     


    If the sqlQuery contains an error the exception code is executed but a connection is held open with an active transaction.
    When this connection is reused by the application a 'connection is already closed' error is fired dispite setting the test connection on opening property in the openaccess configuration to true.

    Regards,

    Robert.
  10. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 15 Oct 2009 Link to this post

    Hi Robert,

    You can use a try-catch-finally construct and place the Dispose of the QueryResults in the finally block so it always gets called.

    Or even better as mentioned earlier in the post rewrite to use the using keyword (don't really know if this exists in VB).

    By the way:
    How is your object scope life-cycle handled. Do you create a new ObjectScope each time you perform queries?

    Regards

    HG


  11. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 16 Oct 2009 Link to this post

    Hello Robert Gibbins,

    What Henrik provided is the most possible reason for this exception. Can you please try to put the Dispose() in the finally block and see if that solves your problem.


    Greetings,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  12. Robert Gibbins
    Robert Gibbins avatar
    6 posts
    Member since:
    Sep 2009

    Posted 19 Oct 2009 Link to this post

    Hello Petar,

    I have modified the code to :-

    query = Scope.GetSqlQuery(sqlQuery, GetType(T), Params)
    Dim QueryResults As IQueryResult
    QueryResults = query.Execute(args)
    Try
     For Each QueryResult In QueryResults
      Results.Add(DirectCast(QueryResult, T))
     Next
    Finally
     QueryResults.Dispose()
     QueryResults = Nothing
    End Try

    The error remains the same. after the routine is executed with incorrect SQL and the connection is reused by the application a 'connection is already closed' error occurs

    In answer to Henrik's question Scope is initialised here:
        Public Shared ReadOnly Property Scope() As IObjectScope
            Get
                If _Scope Is Nothing Then
                    _Scope = ObjectScopeProvider.ObjectScope
                End If
                Return _Scope
            End Get
        End Property

    As you can see Scope is initialised directly from the objectscopeprovider class.

    The pages take a few seconds to load initially and the error occurs on subsequent postbacks some time later.

    Regards,

    Robert.
  13. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 19 Oct 2009 Link to this post

    Hi Robert,

    Ok, are you aware that the recommended best practice for web applications is to obtain the scope on each request and disposing it by the end of the request.
    On request start you can put it in a place (for example HttpContext.Current.Items) where you can obtain it later. During the request you get the object scope from the place where you stored it (for example HttpContext.Current.Items) but you do NOT dispose it. By the end of the request you dispose the objectscope.
    That is referreded to as the "one-thread-one-scope" approach. Some pseudo code might look like this:

    ...// Request start
      HttpContext.Items["threadscope"] = ObjectScopeProvider.GetNewObjectScope()

    // During request
      IObjectScope scope = HttpContext.Items["threadscope"] as IObjectScope;
    // use the scope, but do not dispose it

    // At the end of the request
      IObjectScope scope = HttpContext.Items["threadscope"] as IObjectScope;
    // Dispose it
      scope.Dispose();

    I am almost sure, your problem has something to do with disposing (or lack of disposing)... either on the scope or on the query result..

    Regards

    Henrik

  14. Robert Gibbins
    Robert Gibbins avatar
    6 posts
    Member since:
    Sep 2009

    Posted 21 Oct 2009 Link to this post

    Hello Henrik,

    I was aware of the best practice guidelines. Our project is quite mature and predates this advice by some time. I will look at making the changes you suggest, but they are not that simple for us as we currently manage our scope from within our DAL component.

    I am surprised that you need to dispose the scope if an error occurs in OpenAccess but not in normal operation.

    I was also somewhat confused as to why the error persisted even when the 'test connection before use' option is enabled.

    Do you think there may be a case for modifying the error handling in OpenAccess to 'clean up' more efficiently after an error?

    Kind Regards,

    Robert. 
     
  15. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 21 Oct 2009 Link to this post

    Hi Robert Gibbins,

    The typical cases when you could get such an exception are:
    1. You are not disposing the query results.
    2. In a case where not the complete result is read from the server, the connection might be still open for a longer time.
    3. In long running pessimistic transactions.

    The first two should be solved by disposing properly the query results. The third one can be solved by increasing the connection timeout for long running database connections as suggested previously in this thread.
    Please try the suggested solutions in this thread and let us know if they solve the problem. If the errors persist, please share as much information for your project as possible. That will help us resolve your problem.

    Best wishes,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  16. Scott Bradley
    Scott Bradley avatar
    5 posts
    Member since:
    Jan 2010

    Posted 16 Jun 2012 Link to this post

    Where does this change get made for Sitefinity 3.7?  I am having the same or similar problem  need fix ASAP
  17. Scott Bradley
    Scott Bradley avatar
    5 posts
    Member since:
    Jan 2010

    Posted 18 Jun 2012 Link to this post

    I have found where in the config to make these changes; however, it doesn't seem to be helping.  I am still getting these errors, even at 100 Current connections and longer timeout durations.  This is failing on a Telerik 3.7 (.Net 4.0) site implementation with the MallSoft ecommerce piece being the culprit.  Looking at their source, it appears that the connections are being disposed.

    The following is my config settings.

    Any ideas?  This is pretty hot!

    <backendconfigurations>
          <backendconfiguration id="mssqlConfiguration" backend="mssql">
            <mappingname>mssqlMapping</mappingname>
     <maxConAge>10000</maxConAge>
     <conTimeout>500</conTimeout>
     <maxActive>100</maxActive>
          </backendconfiguration>
        </backendconfigurations>
        <mappings current="mssqlMapping">
          <mapping id="mssqlMapping" />
        </mappings>
  18. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 20 Jun 2012 Link to this post

    Hi Scott,

    Is this failure caused by a module implemented by you or some of the modules shipped by Sitefinity? We haven't had any similar problems reported by other customers so far and I will have to ask you about all the details you can share in order to reproduce the issue.

    All the best,
    Petar
    the Telerik team
    OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
  19. Scott Bradley
    Scott Bradley avatar
    5 posts
    Member since:
    Jan 2010

    Posted 20 Jun 2012 Link to this post

    Petar, I believe this is isolated to the MallSoft Ecommerce plug-in.  The site opens pages fine, and even custom queries against the mallsoft data are fine, but if I have a mallsoft web part on the screen I will intermittently get that error.
Back to Top
DevCraft banner