locked
How to expose a stored procedure via WCF data services and consume it in WPF client application? RRS feed

  • Question

  • I have a simple stored proc which returns an int32 (select max(ID) from customer) - how can I expose that via EF4+WCF data services and use it from a WPF client?

    There is lot of info about mapping a SP as a function in EF4 but the next step of exposing it in WCF dataservice and using in a client seems to be elusive. A 'How to...." for this will be very greatly appreciated.

     

    Thanks

     

     

    • Moved by Alex LiangModerator Wednesday, November 10, 2010 2:28 AM Move to a more appropriate forum for better response (From:ADO.NET Entity Framework and LINQ to Entities)
    Monday, October 25, 2010 3:42 AM

All replies

  • Hi,

    You can create a service operation in your WCF data service which will expose the FunctionImport. Here are two posts I wrote about service operations and how to use them:
    http://blogs.microsoft.co.il/blogs/gilf/archive/2008/11/14/service-operations-adding-business-logic-to-a-data-service.aspx
    http://blogs.microsoft.co.il/blogs/gilf/archive/2008/11/14/consuming-data-services-service-operations.aspx

    Here is a post that explain how to create a service operation on top of a LINQ to SQL model: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/12/16/ado-net-data-services-part-2-using-service-operations-with-webget.aspx 
    The implementation can be adjust also to EF.

    I hope it will help you.


    Gil Fink
    Monday, October 25, 2010 6:01 AM
  • Gil - thanks for the info. My situation is slightly different. I do not have an 'entity' being retured from my Stored proc - just a single int32. So my SP is quite simple - 'Select max(customerID) from Customer'

    In EF4.0 I map the  SP to a function called maxID and expose the variable in my customer.svc.cs file like this

    [WebGet]

     

     

    public IQueryable<int?> maxID()

    return this.CurrentDataSource.maxContactID().AsQueryable();

    and hitting the corresponding URL (

    http://localhost:2125/CustomerDataService.svc/maxID") works - brings back this XML -

    <?xml version="1.0" encoding="utf-8" standalone="yes" ?>

    - <maxID xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">
      <element p2:type="Edm.Int32" xmlns:p2="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">12</element>
      </maxID>
    See that '12' there?
    But when I do this in my code I get an error

    IQueryable<int?> _MaxIDs = DS.CreateQuery<int?>("maxID");

    MessageBox.Show(_MaxIDs.ToList()[0].ToString());

    The second line errors out with "The XML element contains mixed content."

    Any ideas what I am doing woring?

    Thanks.

     

    Tuesday, October 26, 2010 1:36 PM
  • Hi RobinRaul,

    If you map the sp to a function, a new method is added to the automatically generated context class, something like:

    public ObjectResult<Nullable<global::System.Int32>> maxID()
    {
        return base.ExecuteFunction<Nullable<global::System.Int32>>("maxID");
    }

    You can call the method directly in your code using an instantiated context, for example:

    TestEntities context = new TestEntities();
    ObjectResult<int?> res = context.maxID();
    Console .WriteLine(res.First().Value);
        

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, October 27, 2010 6:17 AM
    Moderator
  • Yes - that is true - I can do it if I direclty work with EF. But if I am exposing my EDMX via a WCF data service then how do I do it? The 'maxID' is directly not avialable in the context in WPF client using the data aservice. So based on all the other research it looks like I have to manually expose the maxID function by adding a [webget] function to my svc file which I did (see my previous reply) but I am having trouble getting it to work.

    Do you have a quick sample of a SP exposed through WCF dataservice and consumed in a WPF client?

     

    Thanks for your help.

     

     

    Wednesday, October 27, 2010 2:50 PM
  • Hi RobinRaul,

    Please refer to this :

    http://dotnet.dzone.com/news/exposing-stored-procedure-wcf

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 28, 2010 10:42 AM
    Moderator
  • Alex -

    But the example in the link is still returning an "entity". In my case the SP is returing a simple integer. And I am not able to get it to work via WCF service. In this case (see my previous reply) I have exposed the SP as a mapped function called "maxID" and I see that  that it is working because when I go the URL

    http://localhost:2125/CustomerDataService.svc/maxID

    I get this reponse (correct value 12 is coming back)

    <?xml version="1.0" encoding="utf-8" standalone="yes" ?>

    - <maxID xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">
      <element p2:type="Edm.Int32" xmlns:p2="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">12</element>
      </maxID>
    BUT in my code when I do this

    string

     

     

    DataserviceURL2 = http://localhost:2125/CustomerDataService.svc/maxID;

     

     

    IEnumerable<int?> _MaxIDs = DS.Execute<int?>(new Uri(DataserviceURL2));

     

     

     

    MessageBox.Show(_MaxIDs.First().Value.ToString());

    I get this error on last line

    "Error processing response stream. The XML element contains mixed content."
    So looks like the XML being returned is not correct.
    Any ideas?
    Friday, October 29, 2010 11:51 AM
  • Hi RobinRaul,

    I am moving this thread to ADO.NET Data Services forum for better support. Thanks.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, November 10, 2010 2:24 AM
    Moderator
  • Hi Robin,

    Were you able to find an answer for your issue?

    Thanks,

    Cathy Miller

    Microsoft Online Community Support

    Friday, December 10, 2010 5:08 PM
    Moderator
  • Hi Cathy,

    I have the similar problem, I don´t find how to get it to work, could you help me?

    Thursday, February 23, 2012 6:29 PM
  • Hi DavidFdz,

    While WCF Data Services can expose store procedure via EF function call using WebGet attribute, there is no easy way to consume the data on the client end without using your own method to de-serialize the data. 

    MVC 4 WebAPI addresses the issue by giving the ability to send any IQueryable / IEnumerable thru REST.



    Haroon

    Tuesday, March 6, 2012 6:52 AM