Stored Procedure in WCF Data Service

In this article we will explore use of a Stored Procedure with a WCF Data Service. 

You can read Introduction to WCF Data service and ODATA here.

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

 

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

 

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. 

 

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.

 

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

  

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

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.   

In the above function: 

1. An instance of the Entity class is created. 
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 the full source code of the service class is shown 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 the browser:

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

Now to call Stored Procedure at Client side: 

 

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

For reference the full source code of the client is shown 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);
 
        }
    }
}

The output we are expecting is shown below. 

 

We have seen in this article how we can call a Stored Procedure using a WCF Data Service.