How to use Stored Procedure in WCF Data Service

No.of Views6337
Bookmarked0 times
Downloads 
Votes0
By  Dhananjay Kumar   On  16 Apr 2011 11:04:58
Tag : WCF , Miscellaneous
In this article, I am going to explain how to use the stored procedures in WCF data service to retrieve records from the different datasource such as Sql server and Oracle.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

In this article, I am going to explain how to use the stored procedures in WCF data service to retrieve records from the different datasource such as Sql server and Oracle.In order to use the stored procedure,

Image Loading

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

Image Loading

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. 

Image Loading

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

Image Loading

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

Image Loading

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. 

Image Loading

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

Image Loading

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, 

Image Loading

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

 
Sign Up to vote for this article
 
About Author
 
Dhananjay Kumar
Occupation-Software Engineer
Company-Infosys Technolgies,Pune
Member Type-Gold
Location-India
Joined date-20 Jul 2009
Home Page-http://dhananjaykumar.net/
Blog Page-http://dhananjaykumar.net/
Dhananjay Kumar is Microsoft MVP on connected system. He blogs at http://dhananjaykumar.net/ . You can follow him http://twitter.com/debugmode_/ and reach him at dhananjay.25july@gmail.com
 
 
Other popularSectionarticles
Comments
By:mlhDate Of Posted:10/6/2012 7:03:34 AM
tech lead
Hi, very interesting article, thanks followed instructions, but getting PLS-00306: wrong number or types of arguments in call to xxxx my sp is exact same shape as yours. What did I missed?
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top