locked
How to call a stored procedure (EF 4.0 and WCF DS/OData) RRS feed

  • Question

  • Hi

    I have created a WCF Data Service that has an EF model that is currently based on just a stored proc. The stored proc has been turned into a Function Import called AccountsByIntroducerID and I am able to write code like this against it:

      public List<AccountsByIntroducerID> FetchAccounts(int introducerID)
    
      {
    
       AccountsContext accountsContext;
    
       List<AccountsByIntroducerID> accounts = null;
    
    
    
       try
    
       {
    
        accountsContext = new AccountsContext();
    
        var query = from d in accountsContext.AccountsByIntroducerID(introducerID)
    
           select d;
    
    
    
        accounts = query.ToList();
    
       }
    
       catch (EntityCommandExecutionException xcp)
    
       {
    
        Debug.WriteLine(xcp.InnerException.Message);
    
        throw xcp.InnerException;
    
       }
    
       catch (Exception xcp)
    
       {
    
        Debug.WriteLine(xcp.InnerException.Message);
    
        throw xcp;
    
       }
    
    
    
       return accounts;
    
      }
    
    

    How can I expose the stored proc as an OData URI? When I navigate to the WCF DS using http://localhost:3199/AccountsData.svc/ I get:

    <?xml version="1.0" encoding="utf-8" standalone="yes"?>
    
    <service xml:base="http://localhost:3199/AccountsData.svc/" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:app="http://www.w3.org/2007/app" xmlns="http://www.w3.org/2007/app">
    
     <workspace>
    
     <atom:title>Default</atom:title>
    
     </workspace>
    
    </service>
    
    

    If I try this http://localhost:3199/AccountsData.svc/AccountsByIntroducerID, I get a "The webpage cannot be found" error.

    I am new to all of this stuff so I am not sure if I have explained things well. But I hope the intent is clear: to be able to use OData to get at the data returned by the stored procedure that is represented by a Function Import in my EF model.

     

     

     


    Regards Amir
    • Edited by amir tohidi Tuesday, June 1, 2010 2:50 PM Added error message
    Tuesday, June 1, 2010 2:48 PM

Answers

  • Hi Amir,

     You'll need to expose the Service Operation in the Data Service by setting the ServiceOperationRights flag in the InitializeService method of the Data Service.

    http://msdn.microsoft.com/en-us/library/system.data.services.serviceoperationrights(v=VS.100).aspx

    Also, you need the [WebGet] attribute on the FetchAccounts method in the Data Service.


    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    • Marked as answer by amir tohidi Wednesday, June 2, 2010 8:37 AM
    Tuesday, June 1, 2010 3:45 PM
    Moderator
  • Hi,

    Well you BING for "msdn WCF Data Services Service Operation", the first link is what you're looking for.

    The way to pass parameters to service operations is through query options. So let's assume the parameter is called entityID, then the URI would look like:

    /AcountsByIntroducerID?entityID=1234

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by amir tohidi Wednesday, June 2, 2010 8:36 AM
    Tuesday, June 1, 2010 4:14 PM
    Moderator

All replies

  • Hi Amir,

     You'll need to expose the Service Operation in the Data Service by setting the ServiceOperationRights flag in the InitializeService method of the Data Service.

    http://msdn.microsoft.com/en-us/library/system.data.services.serviceoperationrights(v=VS.100).aspx

    Also, you need the [WebGet] attribute on the FetchAccounts method in the Data Service.


    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    • Marked as answer by amir tohidi Wednesday, June 2, 2010 8:37 AM
    Tuesday, June 1, 2010 3:45 PM
    Moderator
  • Thanks Phai. I am now able to call my function and get data.

    I have another function, but that one take an integer parameter. When I try http://localhost:3199/AccountsData.svc/AccountsByIntroducerID(1234) I get the error web page not found.

    What URI do I use for this one?

    Also, where can I learn more about this stuff as I don't even know what to search for in Google.


    Regards Amir
    Tuesday, June 1, 2010 4:07 PM
  • Hi,

    Well you BING for "msdn WCF Data Services Service Operation", the first link is what you're looking for.

    The way to pass parameters to service operations is through query options. So let's assume the parameter is called entityID, then the URI would look like:

    /AcountsByIntroducerID?entityID=1234

    Thanks,


    Vitek Karas [MSFT]
    • Marked as answer by amir tohidi Wednesday, June 2, 2010 8:36 AM
    Tuesday, June 1, 2010 4:14 PM
    Moderator
  • Hi Vitek

    Thanks! It is working now.

    Also, I now know what to search for!

     


    Regards Amir
    Wednesday, June 2, 2010 8:36 AM
  • Hi,

    If I use Stored procedure for get selected valuse from Table ( Ex : Select FirstName. LastName, Address From Student)

    How can I get that values from SP into Entity framework without manually create new custom entity and mapping that entity to SP through Function importing in Medel.

    Is any way to assign return value from SP into Object type varible or var type varible without mapping entity ?

    Thx

     

     

     

    Thursday, September 30, 2010 6:54 AM
  • Were you able to get this to work? I have a similar issue - I return a single int from my SP and need to call that SP from my WCF data service.

     

    Thanks.

     

    Robin

    Thursday, October 28, 2010 3:14 PM
  • It is possible to return simple types from a ServiceOperation without having a specific Object mapped in your entity model. What you do within the ServiceOperation is up to you, such as connecting directly to a SP. I've got some that use a connection to a different Entity Model from the one the Data Service is connected to.

           [WebGet]
            public int showInt()
            {
                return 1;
            }
    
            [WebGet]
            public List<string> showStrings()
            {
                List<string> myList = new List<string>();
                myList.Add("Hello");
                myList.Add("on the");
                myList.Add("client side!");
    
                return myList;
             }
    

    Hope this helps!

    Thursday, February 2, 2012 4:25 PM
  • You're better of Googling than Binging.
    Tuesday, November 20, 2012 4:08 PM