none
How to execute stored procedure using sp_executesql instead of EXEC (which is default) in WCF Data Service with Entity Framework RRS feed

  • Question

  • In my project , we are using WCF Data Service with Entity Framework to access the SQL server DB data from WPF client.

    We are accessing and executing a stored procedure(Get data operation) from WPF client, using WCF Data Service and Entity Framework (using Entity Model (.edmx)). In entity model, the Function Import of the SP returns a collection of type entity.

    Also using $top in the URI.

    The Final URI being executed is in the following format 

    https://somename/DataService.svc/REST/GetDataMethodName?language=false&IDs='2816,2484'&$filter=DataID gt 0&$top=30&Type='SomeType'

    When I run the sql profiler to check; I can see that EXEC is being used to execute the store procedure.

    1. How can I execute stored procedure using sp_executesql instead of EXEC?
    2. Another issue am facing is the stored procedure is getting recompiled multiple times, how can I restrict that?

    Any info regarding above will be helpful.

    Thanks!






    Monday, January 4, 2016 9:21 AM

Answers

  • >When I run the sql profiler to check; I can see that EXEC is being used to execute the store procedure.

    That may not actually be the case, if the access method is an RPC.

    >How can I execute stored procedure using sp_executesql instead of EXEC?

    Why would you care.

    >the stored procedure is getting recompiled multiple times

    A stored procedure will get recompiled in a number of useful circumstances, like updated statistics.  Why do you believe that you have a problem with recompilation?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, January 4, 2016 2:29 PM

All replies

  • Most likely, you have stop using the Data Service and switch over to a non Restful ASP.NET WCF Web service  that is using a DAL with the DAL in contact with EF.
    Monday, January 4, 2016 12:40 PM
  • >When I run the sql profiler to check; I can see that EXEC is being used to execute the store procedure.

    That may not actually be the case, if the access method is an RPC.

    >How can I execute stored procedure using sp_executesql instead of EXEC?

    Why would you care.

    >the stored procedure is getting recompiled multiple times

    A stored procedure will get recompiled in a number of useful circumstances, like updated statistics.  Why do you believe that you have a problem with recompilation?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, January 4, 2016 2:29 PM