The connection is already closed.

[Dennis]

Hi,

how i have to handle this exception?

Sample Code: (The Scope comes from the ObjectScopeProvider)

	//...
	var result =
	from b
	in Scope.Extent<Category>()
	where b.ID == id
	select b;
	return result.Single();
	//...

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:

[PetarP]

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.

[Dennis]

Thx for the help. I found my mistake and can fix it now. I never dispose my scope after i change my datalayer concept.

[Vitaliy ***]

Was really helpful for me!

[Mehul Patni]

Hi Dennis,

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

Thanks

[Dennis]

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.

	public string CreateCustomers(NSV.NW.DataContracts.Customer customer)
	{
	string customerId = string.Empty;
	using (IObjectScope scope = NSV.NW.OADataModel.ObjectScopeProvider.GetNewObjectScope())
	{
	NSV.NW.OADataModel.Customer oaCustomer = new NSV.NW.OADataModel.Customer()
	{
	CompanyName = customer.CompanyName,
	ContactName = customer.ContactName,
	ContactTitle = customer.ContactTitle,
	Address = customer.Address,
	City = customer.City,
	Region = customer.Region,
	PostalCode = customer.PostalCode,
	Country = customer.Country,
	Phone = customer.Phone,
	Fax = customer.Fax
	};
	scope.Transaction.Begin();
	scope.Add(customer);
	scope.Transaction.Commit();
	customerId = oaCustomer.CustomerID;
	}
	return customerId;
	}

[PetarP]

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.

[Robert Gibbins]

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.

[IT-Als]

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

[PetarP]

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.

[Robert Gibbins]

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.

[IT-Als]

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

[Robert Gibbins]

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. 
 

[PetarP]

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.

[Scott Bradley]

Where does this change get made for Sitefinity 3.7?  I am having the same or similar problem  need fix ASAP

[Scott Bradley]

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>

[PetarP]

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

[Scott Bradley]

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.