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.
22 Responses to Execute Stored Procedure With ADO.NET Data Services
Leave a Reply
Connect with me
-
Can we still using $orderby, $top command when using stored procedure?
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 :)
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);
}
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
Hi Josef. I’m happy that you found a solution. Thanks for your comment.
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–
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
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
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.
Hi Monte,
What does your URL look like? Can you post it here?
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.
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.
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?
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
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)
Monte,
I could not get your solution to compile.
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
hi there thanks for the article
i was wondering if it is possible to return a boolean result which is the correction operation of the stored procedure
i am consuming the service in a viewmodel in silverlight application and want to parse the result of operation in the client callback method
service is
[webget]
public bool dostuffstoredprocedure(string arg1,string arg2)
{
private bool theboolresult= false;
try
{
CurrentDataSource.SetPassword(arg1, arg2);
theboolresult = true;
}
catch( Exception Ex)
{
// log Ex here
theboolresult=false;
}
return theboolresult;
}
Hi Paul,
I have not tried your example but I’m certain that returning a boolean result should be okay.
Hi thanks i can reurn the boolean result correct but in silverlight ( sorry this question is more sivlerlight based) because of the Asynch calls to beginexecute and endExecute
all resultants need to be converted to a resultant queryable type
i have the operation working fine the
beginexecute is fired
the stored procedure fires in the database
the callback happens
BUT i am unable to convert the iasynch result to a boolean operator?
Hi Paul,
I have not worked with Silverlight much but by looking at documentation for IAsyncResult I can see that it only returns the status of an async operation. However the method signature for EndExecute is
public IEnumerable EndExecute(
IAsyncResult asyncResult
)
What do you see in the IEnumerable returned back to you? Could the value you are looking for be in it?
Thanks. I followed your steps, but my “Add Function Import” is scalar with string result. I could not find a way to execute my stored procedure with couple parameters. I had worked on this for all day and could not find solution for my ASP.NET MVC3 app to receive HTTP POST from client and return data against our SQL database. If you have any solution, please lead me. Thanks.