Execute Stored Procedure With ADO.NET Data Services

August 16th, 2009 | 17 Comments | Posted in Programming

Introduction

In an earlier article I looked at how DO.NET Data Services can be used with ASP.NET. In this post I will talk about using ADO.NET Data Services to retrieve data via SQL Server stored procedure. Doing this is simple and I will follow a similar approach to hook everything up as I did in my earlier post.

I have created a stored procedure which retrieves data from Employees table in Northwind database based on city name. Here is the script I used to create the procedure.

CREATE PROCEDURE [dbo].[GetEmployeesByCity] 

    @City NVARCHAR(15)
AS

BEGIN

    SELECT *
    FROM Employees
    WHERE City = @City

END

Creating Data Model

ADO.NET Data Services sits between the client and a data source, so the first thing I’ll need is a data source. I will use Entity Framework to create my data source.

To make things simple I will select Employees table only for my Entity Data Model.

Mapping Stored Procedure in EDM

My objective here is to retrieve data from a stored procedure. To this I need to expose the procedure through my data layer which I have created using Entity Framework. Mapping a stored procedure in Entity Framework can be done by following these steps.

Open Model Browser (View –> Other Windows –> Model Browser). Right click on Stored Prodecures under NorthwindModel.Store and click Update Model from Database.

Select the stored procedure (GetEmployeesByCity in this case) and click Finish.

Go back to Model Browser and under stored procedure right click the stored procedure and click Create Function Import.

Select Employees entity as the return type and click OK.

Entity Model is now setup to execute the stored procedure and return results. It can be called like this.

NorthwindEntities entities = new NorthwindEntities();
var employees = entities.GetEmployeesByCity("London");

My goal however is to retrieve data via ADO.NET Data Services so I’ll start creating my service.

And Now With a Service

Because I’d like to access this data through my ADO.NET Data Service so the first thing I’ll do is create a service called NorthwindEmployeeService. I will add a new item to my project of type ADO.NET Data Service.

Visual Studio creates a blank service with some scaffolding for me to get started. First thing I need to do is let my service know about my data source class and then configure appropriate rights to my entities and operations. To be simple I will just allow everything. Below is the code for my service after making changes.

public class NorthwindEmployeeService : DataService<NorthwindEntities>
{
  // This method is called only once to initialize service-wide policies.
  public static void InitializeService(IDataServiceConfiguration config)
  {
    config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
    config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
  }
}

By default my service does not know that it should also work with a stored procedure. To do this i can write a method which will execute the procedure and return results. Such a method can look like this.

[WebGet]
public ObjectResult<Employees> GetEmployeesByCity(string cityName)
{
  NorthwindEntities entities = new NorthwindEntities();
  return entities.GetEmployeesByCity(cityName);
}

Method above tells the service to expose a GetEmployeesByCity method which can be called by clients. Note that the method is decorated with a WebGet attribute which indicates that this is a GET method which can be called by a web client.

Running Service and Calling Stored Procedure

To see my service in action I can hit F5

To see the results from the stored procedure I can use the following URL.

http://localhost:16147/NorthwindEmployeeService.svc/GetEmployeesByCity?cityName=’London’

Here I am passing in the parameter as a query string which is accepted by my service and appropriate results are returned.

Conclusion

In this article I used ADO.NET Data Services to execute a stored procedure and return results. I am not in favour of implementing a direct mapping between services and database and the design can be a little better whereby data layer can be sensibly abstracted away by service. However the idea was to demonstrate a concept. I hope you enjoyed reading this article.

Leave a Reply 9036 views, 1 so far today |

Tags: ADO.NET, ASP.NET, Data Services, LINQ, REST

Follow Discussion

17 Responses to “Execute Stored Procedure With ADO.NET Data Services”

Jack Says:
September 9th, 2009 at 5:52 pm
Can we still using $orderby, $top command when using stored procedure?
Deepak Says:
September 11th, 2009 at 1:57 pm
Hi Jack,

I have not tried using them but I don’t see any reason why you cannot. Busy with TechEd Australia these days otherwise I would have put an example together. Maybe for another post :)
Josef Says:
December 21st, 2009 at 7:33 pm
Following your example step by step I get the Error:
Type or Namespacename “ObjectResult” cannot be found.
Could you give me a hint ?
Thanks
Josef

[[WebGet]
public ObjectResult GetEmployeesByCity(string cityName)
{
NorthwindEntities entities = new NorthwindEntities();
return entities.GetEmployeesByCity(cityName);
}
Josef Says:
December 21st, 2009 at 8:45 pm
I’have found a solution to make it running:

using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Data.Objects; <— NEW ADDED
using System.Linq;
using System.ServiceModel.Web;
using System.Web;

Thanks for your helpful article. Examples with stored procedures (populated as webservices) are rare !
Josef
Deepak Says:
December 21st, 2009 at 9:52 pm
Hi Josef. I’m happy that you found a solution. Thanks for your comment.
Manindra Says:
December 26th, 2009 at 7:44 pm
I create [WebGet] method in service .But i am not able to find it in my silverlight application.Please help me how can i call this in
our silver light application.
Thanks–
Vinod Suri Says:
February 3rd, 2010 at 5:35 pm
Hello,

I am new to ADO.NET Data Services.
I couldn’t figure out how can i call a stored procedure using ado.net data services in which the result set comes from a query using a JOIN. I mean using JOIN in query and returning data from multiple tables.

Can u provide some example.

Thanks
JoeCJoec Says:
March 12th, 2010 at 1:02 am
Could you please, put some code example about calling webmethod since Silvelight Web Project (dont using WFC).

I have updated the Web reference and I can list tables and view, buy How I can call de WebMethod

All best and thank you very much by your support
Monte Baker Says:
July 3rd, 2010 at 2:43 am
Deepak,

I have tried to follow your example and I am able to build both the service and EF model with the stored procedure mapped, however when i browse the service in IE i am unable to access the method in the URL (Page not Found).

Any guidance you could provide would be helpful.
Deepak Says:
July 5th, 2010 at 10:09 am
Hi Monte,

What does your URL look like? Can you post it here?
Monte Baker Says:
July 7th, 2010 at 3:06 am
My base service URL is “http://localhost:24905/EntityBackOffice2010.svc/” which works fine. the webget URL is “http://localhost:24905/EntityBackOffice2010.svc/GetEmployeeSchedule?sDate=’7/1/2010’” which returns page not found. below is my svc code.

namespace EntityBackOffice2010_R2.Web
{
public class EntityBackOffice2010 : DataService
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(IDataServiceConfiguration config)
{
config.SetEntitySetAccessRule(“*”, EntitySetRights.AllRead);
config.SetServiceOperationAccessRule(“*”, ServiceOperationRights.All);
}

[WebGet]
public ObjectResult GetEmployeeSchedule(DateTime sDate)
{
EntityBackOfficeEntities entities = new EntityBackOfficeEntities();
return entities.GetEmployeeSchedule(sDate);
}
}
}

everything builds correctly and if I access the enity that the proc is mapped to “EmployeeSchedule” it returns data just fine, I have even tried to achieve this with WCF Data Services with the exact same result.
Deepak Says:
July 7th, 2010 at 12:56 pm
Monte,

Last time I looked at this I found that datetime parameters could only be specified as a filter. For example:

http://localhost:20000/MyService/Employees?$filter=BirthDate eq datetime’1948-12-08′

I am not sure if this has been fixed now.
Monte Baker Says:
July 8th, 2010 at 5:11 am
I have tried your exact solution using the GetEmployeesByCity Procedure in the NorthWind database. I am able to view the method when I do not include a parameter “NorthwindEmployeeService.svc/GetEmployeesByCity”, but it fails when I try to add city name “NorthwindEmployeeService.svc/GetEmployeesByCity?cityName=’London’”,I recieve a “The webpage cannot be found” error . My code now exactly matches your sample. does this provide any guidance as to what could be wrong?
Deepak Says:
July 8th, 2010 at 10:21 am
Hi Monte,

This is turning into an interesting problem. Is it possible for you to send me your code. I can have a look and respond. my email is k a p o o r d e e p a k AT g m a i l . c o m
Monte Baker Says:
July 16th, 2010 at 1:56 am
Were you ever able to figure out what I was doing wrong. I never heard back from you (either that or your email was redirected to my spam folder)
Deepak Says:
July 19th, 2010 at 2:27 pm
Monte,

I could not get your solution to compile.
Les Prigmore Says:
August 18th, 2010 at 5:13 am
OK, This is all looking good and I understand it completely and thank you for covering it so thouroughly. Now, using Silverlight 4, can you please, please, please show me the code to put the result set from “GetEmployeesByCity” into a DataGrid? Thank you so much in advance…..Les Prigmore