Timeout in Stored Procedures in WCF Data Services

[Valerio]

Hi, i have a problem with stored procedures when using WCF.

No error when querying a Telerik Data Access Domain Model.

To make things easier i create a simple stored procedure, just to get a server date/time.

CREATE PROCEDURE dbo.ServerDateTime
AS
BEGIN
  select SYSDATETIME()
END

After mapping a domain method result to a single scalar value, i can run the code below with no problem, when querying a Telerik Data Access Domain Model.

using (EntitiesModel dbContext = new EntitiesModel())
{
    for (int n = 0; n < 10; n++)
    {
        DateTime result = dbContext.ServerDateTime();
        Console.WriteLine("Server time: {0}", result);
    }
}

In WCF i crated a create a service operation method and changed the service operation access rules in the InitializeService method.

public partial class EntitiesModelService : OpenAccessDataService<SPinWCFdal.EntitiesModel>
{
  [WebGet]
  public DateTime ServerDateTime()
  {
    return this.CurrentDataSource.ServerDateTime();
  }
}

public static void InitializeService(DataServiceConfiguration config)
{
  config.SetEntitySetAccessRule("Table1", EntitySetRights.All);
  config.SetServiceOperationAccessRule("ServerDateTime", ServiceOperationRights.All);
  config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
}

When a execute the code below, even in a browser (using the url) it works just a few times, 8 or 9 e becames very slow geting timeouts.

// http://localhost:63187/EntitiesModelService.svc/ServerDateTime()

EntitiesModel dataManager = new EntitiesModel(new Uri("http://localhost:63187/EntitiesModelService.svc/"));

for (int n = 0; n < 10; n++)
{
  DateTime result = dataManager.Execute<DateTime>(new Uri(string.Format("{0}ServerDateTime()", dataManager.BaseUri))).FirstOrDefault();
  Console.WriteLine("Server time: {0}", result);
}

Thank you

[Valerio]

Hi.

I believe it is a bug...
It have some problem when accessing EntitiesModelService. (this.CurrentDataSource)

If i change this:

public partial class EntitiesModelService : OpenAccessDataService<SPinWCFdal.EntitiesModel>
{
  [WebGet]
  public DateTime ServerDateTime()
  {
    return this.CurrentDataSource.ServerDateTime();
  }
}

for this:

public partial class EntitiesModelService : OpenAccessDataService<SPinWCFdal.EntitiesModel>
{
  [WebGet]
  public DateTime ServerDateTime()
  {
    DateTime result = DateTime.Now;
    using (EntitiesModel dbContext = new EntitiesModel())
    {
      result = dbContext.ServerDateTime();
    }

    return result;
  }
}

It is working...

I do not know why, but calling "this.CurrentDataSource" works just 9 times, after that...

Thank you

[Kaloyan Nikolov]

Hello Valerio,

Each store procedure or function call is wrapped in a transaction, It seems that you are not commuting the transaction at the end of the call. This should be done by invoking either SaveChanges() or ClearChanges() - respectively if your procedure is performing CUD operations or not. The code should be something like this:

public partial class EntitiesModelService : OpenAccessDataService<SPinWCFdal.EntitiesModel>
{
  [WebGet]
  public DateTime ServerDateTime()
  {
    var res = this.CurrentDataSource.ServerDateTime();
    this.CurrentDataSource.SaveChanges();
    return res;
  }
}

In your example below the transaction is not committed and thus the connection stays open for the TransactionTimeout (usually 2 minutes) and  you easily reach the connection maximum (10 if not specified different number).

Your workaround is valid as well, in this case when the context is disposed the transaction is rolled back automatically and the connection is returned to the connection pool. However I would suggest you to use explicit SaveChanges() call as in this case you do not create additional context instance. 

Also you should consider the life-cycle of the Service instance, if it is per request then you can safely use the this.CurrentDataSource instance to call stored procedures but if it is singleton then would be better to use short living context as in your example. 

I hope this helps. Should you have any additional question do not hesitate to get back to us. 


Regards,
Kaloyan Nikolov
Telerik

 

 

[Valerio]

Thanks Kaloyan, it was very helpfull.

Have a nice day. ;)