Timeout in Stored Procedures in WCF Data Services

4 posts, 1 answers
  1. Valerio
    Valerio avatar
    4 posts
    Member since:
    Apr 2014

    Posted 16 Jun 2014 Link to this post

    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
  2. Valerio
    Valerio avatar
    4 posts
    Member since:
    Apr 2014

    Posted 18 Jun 2014 Link to this post

    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
  3. DevCraft banner
  4. Answer
    Kaloyan Nikolov
    Admin
    Kaloyan Nikolov avatar
    118 posts

    Posted 18 Jun 2014 Link to this post

    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
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  5. Valerio
    Valerio avatar
    4 posts
    Member since:
    Apr 2014

    Posted 18 Jun 2014 in reply to Kaloyan Nikolov Link to this post

    Thanks Kaloyan, it was very helpfull.

    Have a nice day. ;)
Back to Top