locked
How to execute stored procedure from ADO.NET Data Services? RRS feed

  • Question

  • Hi,

    I have a CLR stored procedure which takes xml string as input. 
    The stored procedure does some processing on this xml and gives me the modified xml string as the output.

    Can you please tell me how to call this stored procedure using an ado.net data service?


    Thanks & Regards,
    Rajesh
    Rajesh
    Friday, May 22, 2009 11:40 AM

Answers

  • Hi Rajesh,
     The URI has limit on its length.
     If you want to pass in the parameter using the URI , then make a batch query to that service operation .
     This way , the lengthy URI will be part of the body of the request and will not run into the limit of the URI for the request.
     Here are some examples of how to make Batch queries using the client library.

     Batching Queries using the ADO.NET Data Services CLient library
    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    Wednesday, May 27, 2009 11:02 PM
    Moderator

All replies

  • Hi RajeshPotineni

    What are you using with data services, entity framework, linq to sql or custom provider?



    Daniel Portella - http://undocnet.blogspot.com - This posting is provided "AS IS" with no warranties, and confers no rights. If this post has answered your question please mark as the answer and if it is helpful do like wise.
    Friday, May 22, 2009 11:58 AM
  • We are using Entity Framework
    Rajesh
    Friday, May 22, 2009 12:53 PM
  • Is your stored procedure mapped to an entity?

    Data Services will only return entities thata re part of the entity model, if your stored procedure doesnt return a entity that is part of it data services will not allow you to use it on a service operation.

    For things like this I suggest that you use a wcf service or web service containing your function calls to your stored procedures. You have to understand that Data Services was not intented to work that way. I urge you to read an old post from myself where Pablo Castro gave me a really good explantion of what data service is all about.



    Daniel Portella - http://undocnet.blogspot.com - This posting is provided "AS IS" with no warranties, and confers no rights. If this post has answered your question please mark as the answer and if it is helpful do like wise.
    Friday, May 22, 2009 1:01 PM
  • This is not entirely correct, as it is possible to build service operations that return single primitive types, or collections of primitive types (in fact, a service operation is currently the ONLY way to do the latter in a data service).

    The problem is that there is no built-in support for this in the client library, so you would need to parse the resulting xml/json on your own (though something like XLinq would work just fine).

    So, in theory, you could build such a service operation, but sending/receiving xml might be tricky, as service operation parameters currently have to be sent via the URI.

    Overall, for your particular scenario, I think Daniel's suggestion of using a WCF service to perform this operation outside of ADO.NET Data Services may be the easiest option to implement.
    Matt Meehan, ADO.NET Data Services (Astoria)
    Friday, May 22, 2009 3:33 PM
    Moderator
  • Awesome, should have known that, I didnt read the line on the msdn library correlty kinda missed the part about primitive types. My apologies RajeshPotineni.
     
    Yeah Matt Meehan - MSFT could you post here two examples of a service operation one returning a single primitive type and one returning a collection of primitive types.

    Daniel Portella - http://undocnet.blogspot.com - This posting is provided "AS IS" with no warranties, and confers no rights. If this post has answered your question please mark as the answer and if it is helpful do like wise.
    Friday, May 22, 2009 4:19 PM
  • These are pretty trivial examples, but they illustrate the point:
            // single primitive value in payload
            [WebGet]
            [SingleResult]
            public IQueryable<int> SingleInt(string param)
            {
                string[] x = new string[] { param };
                return x.AsQueryable();
            }


    Uri: "http://host/service.svc/SingleInt?param=5"

    Xml output:
      <?xml version="1.0" encoding="utf-8" standalone="yes" ?> 
      <SingleInt p1:type="Edm.Int32" xmlns:p1="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
      xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">5</SingleInt > 
    Json output:
    { "d" : {
    "SingleInt": 5
    } }


            // multiple primitive values in payload
            [WebGet]
            public IQueryable<string> MultipleStrings(string first, string second)
            {
                string[] x = new String[] { first, second };
                return x.AsQueryable();
            }

    Uri: "http://host/service.svc/MultipleStrings?first='Hello'&second='World'"

    Xml output:
    <?xml version="1.0" encoding="utf-8" standalone="yes" ?> 
    <MultipleStrings xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">
      <element>Hello</element> 
      <element>World</element> 
    </MultipleStrings>

    Json output:
    { "d" : [
    "Hello", "World"
    ] }

    Matt Meehan, ADO.NET Data Services (Astoria)
    Friday, May 22, 2009 4:58 PM
    Moderator
  • Fantastic Matt Meehan - MSFT yes very simple

    Daniel Portella - http://undocnet.blogspot.com - This posting is provided "AS IS" with no warranties, and confers no rights. If this post has answered your question please mark as the answer and if it is helpful do like wise.
    Friday, May 22, 2009 7:33 PM
  • Thanks Matt.

    But I have one question here. Let's say I have a service operation like the one you described and it takes a parameter of type string.
    I would like to pass huge text into this string. The Uri text limitation would not allow me to pass this text in the Uri and it will throw an error.

    How can I achieve this?


    Thanks,


    Rajesh
    Monday, May 25, 2009 9:53 AM
  • Hi Rajesh,

    Unfortunately, ADO.NET Data Services does not support sending arguments to a service operation in anything but the URI.

    I would recommend that, for this particular scenario, you take Daniel's advice and hook something in at the WCF layer. I don't have any experience doing this, however, so I can't provide an example.
    Matt Meehan, ADO.NET Data Services (Astoria)
    Tuesday, May 26, 2009 4:57 AM
    Moderator
  • Hi Rajesh,
     The URI has limit on its length.
     If you want to pass in the parameter using the URI , then make a batch query to that service operation .
     This way , the lengthy URI will be part of the body of the request and will not run into the limit of the URI for the request.
     Here are some examples of how to make Batch queries using the client library.

     Batching Queries using the ADO.NET Data Services CLient library
    Phani Raj Astoria http://blogs.msdn.com/PhaniRaj
    Wednesday, May 27, 2009 11:02 PM
    Moderator