In this article we will explore, how could we use Stored Procedure with WCF Data Service?

You can read Introduction to WCF Data service and ODATA here

To use Stored Procedure, at time of creation of Data Model, select Stored Procedure as part of Data Model.

clip_image001

Now open EDMX file and right click on that. Select Add and then select Function Import.

clip_image002

Popup window will be open.

1. Give Function import name

2. Choose Stored procedure from drop down

3. Choose the Entity type Stored Procedure is returning.

clip_image003

Stored Procedure we are selecting is GetStudentGrades and it is returning one or more entities of StudentGrade.

After clicking OK you can see columns are mapped

clip_image005

You can see in model browser that GetStudentGrades has been listed in Function Imports section and it is having one input parameter StudentD.

clip_image006

We can see now that Stored Procedure has been mapped to Entity model and can be exposed as WCF Data Service.

Next step we need to create a function in DataService class. This function will return list of entities.Client will call this function to execute Stored Procedure. clip_image008

In above function,

1. Creating instance of Entity class.

2. Calling the Function on this object. We need to search function import name to call on object of entity class.

3. Function is having one input parameter to pass as parameter to execute stored Procedure.

For Reference full source code of service class is as below,

WcfDataService.svc.cs


using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Data.Services.Common;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;

namespace WebApplication6
{
 public class WcfDataService1 : DataService<SchoolEntities>
 {

 public static void InitializeService(DataServiceConfiguration config)
 {

 config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
 config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
 config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
 }

[WebGet]
 public List<StudentGrade> GetStudentGrade(string studentId)
 {
 SchoolEntities entities = new SchoolEntities();
 return entities.GetStudentGrades(Convert.ToInt32(studentId)).ToList();
 }
 }
}

To call the Stored Procedure in browser

http://localhost:14469/WcfDataService1.svc/GetStudentGrade?studentId=’2

Now to call Stored Procedure at Client side

clip_image010

We need to pass the exact URL of the function in service class executing Stored Procedure.

For Reference full source code at client is as below,

Program.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Services;
using System.Data.Services.Client;
using ConsoleApplication1.ServiceReference1;

namespace ConsoleApplication1
{
 class Program
 {
 static void Main(string[] args)
 {

DataServiceContext context = new DataServiceContext(new Uri("http://localhost:14469/WcfDataService1.svc/"));

 List<StudentGrade> lstStudentsGrade = context.Execute<StudentGrade>
 (new Uri("http://localhost:14469/WcfDataService1.svc/GetStudentGrade?studentId='2'")).ToList();

 foreach (var r in lstStudentsGrade)
 {
 Console.WriteLine("Student :" + r.StudentID.ToString() +" Grade : " +  r.Grade);

}
 Console.ReadKey(true);

}
 }
}

Output we are expecting is as below,

clip_image012

We saw in this article, how we could call a Stored Procedure using WCF Data Service.

Advertisements

7 thoughts on “Stored Procedure in WCF Data Service

  1. I wonder why they don’t expose the WebGet as a function call on the client side context? In otherwords, why do we have to call it via the Uri.

    Secondly, I wonder why we have to stuff around creating a wrapper function with [WebGet]. Why isn’t that generated once you do a FunctionImport.

    Thirdly, I wonder why we have to stuff around with creating Function imports. Why doesn’t it just assume you want the stored procedure as a function import and do it automatically? Given that you need to explicitly SetServiceOperationAccessRule() to expose the function?

  2. I’d pounding my head for awhile on why datasvcutil did not generate any stored procedure in the client proxy file.
    This is great! save me from dumping the idea of using store procedure from proxy. I was going to just use the wcf data contract after frustration. thanks for your posting. Very valuable.

  3. “http://localhost:14469/WcfDataService1.svc/GetStudentGrade?studentId=’2′”) didn’t work for me

    any suggestions?

  4. Hi Dhananjay, I stumbled upon your article while I was searching more information about this subject. Could you please point me into the right direction concerning the following?

    You mentioned ” The next step we need to do is create a function in DataService class. This function will return list of entities. The client will call this function to execute the Stored Procedure.”

    How does a simple function looks like when the stored procedure returns a XML set instead of a list of entities? I am new to this WCF subject and would like to get more knowledge on this subject.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s