Stored Procedure in ADO.NET Data Service

Objective:

Objective of this article is to explain, how to use stored procedure with ADO.Net Data Service.
For other articles on ADO.NET Data Service, follow these links

Introduction of ADO.NET Data Service
Working with ADO.NET Data Service

Explanation of Database

For Sample, here database DJ is being used. DJ database is containing two tables

1. Dept
2. Emp

Dept



Emp



Database Diagram


There is Stored Procedure called Get Data. This SP is retrieving all the records from Emp table.

Stored Procedure look more or less like, below

USE [dj]
GO
/****** Object:  StoredProcedure [dbo].[GetData]    Script Date: 05/14/2009 12:12:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetData]
      -- Add the parameters for the stored procedure here

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
      SELECT * from Emp
END

Objective of this article is to use this Stored Procedure (GetData) in ADO.NET Data Service.

Step 1

Create a new project as web application and give any name. here name is StoredProcedureTesting.



Step 2

Add Data Model.

To do so, add new item and click on Data tab then select ADO.NET Entity Model.

Give any name. Here name is csharp.edmx.





Select database from drop down list. If database in not listed there, create new connection and then select database.

Here database dj is being selected. Schema of database is discussed above.

Give any name to Entity. Here name is storedproceduretestingEntities.





Select the entire table and click on Stored Procedure tab and from there select GetData stored procedure as well. Give any name to model, here it is storedproceduretestingModel



So final edmx file created will look more or less like below.



Step 3

• Right click on edmx file. Select Add then Function Import.
  
  
   
• Give Function Import name as of Stored Procedure name. here GetData
   
• Select Return type. Stored procedure is returning here Emp, so select Emp.
  
  
   
• In model browser of csharp.edmx , under FunctionImport tab GetData is listed.
  
  
  
  
• In solution explorer, Right Click on csharp.edmx and select open with
  
  
  

A dialog box will appear. Select XML Editor from there then click on OK.



In Confirmation Box, select YES.

Explanation of markup .edmx file

Markup of edmx file contains three parts or segments.






On selecting stored procedure in entity model, by default below code will get added in SSDL part of markup of .edmx
ssdl segment

<Function Name="GetData" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" />

Entire SSDL of edmx is,

csdl segment

<FunctionImport Name="Getdata" EntitySet="Emp" ReturnType="Collection(storedproceduretestingModel.Emp)" />

In above XML

Name -> name of the stored procedure

EntitySet -> Entity (Table) on which Stored Procedure is working

ReturnType -> Type of the Return data from Stored Procedure.

If procedure required any parameter, then markup of parameters will be added inside <FunctionImport> like below.

FunctionImport Name ="GetData" EntitySet ="Emp" ReturnType ="Collection(storedproceduretestingModel.GetData)">
 <Parameter Name =""
</FunctionImport>

msl segment

<FunctionImportMapping FunctionImportName="Getdata" FunctionName="storedproceduretestingModel.Store.GetData" />

Up to here Stored Procedure is mapped in entity model.

Step 4

Creating Service

Right click on project and add new item. Add ADO.NET Data Service. Give any name here name is DataService.cs



After this, click on DataService.svc and modify as below

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

namespace StoredProcedureTesting
{
    public class DataService : DataService<storedproceduretestingEntities>
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(IDataServiceConfiguration2 config)
        {
            // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
            // Examples:
             config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
        }
    }
}

Step 5

Testing Service

Right click on DataService and view in browser to test it.



Step 6

Calling stored procedure in ADO.NET Data service

[WebGet]
        public List<Emp> GetData()
        {
            storedproceduretestingEntities ent = new storedproceduretestingEntities();
            return ent.Getdata().ToList();
        }

Explanation of code

Create a new method in DataService class.

Create instance of entities class.

Call the stored procedure which is imported as function on instance of entity class.

Complete code for DataService class is as below

DataService.svc.cs

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

namespace StoredProcedureTesting
{
    public class DataService : DataService<storedproceduretestingEntities>
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(IDataServiceConfiguration2 config)
        {
            // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
            // Examples:
             config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
        }

        [WebGet]
        public List<Emp> GetData()
        {
            storedproceduretestingEntities ent = new storedproceduretestingEntities();
           return ent.Getdata().ToList();

         }
    }
}

Step 7

Testing Stored Procedure in browser

Run the service in browser.

Give GetData function name in browser to run the stored procedure. Let service is hosted on server 2989 then run stored procedure as
http://localhost:2989/DataService.svc/GetData  , output would be like below



Step 8

Consuming Stored Procedure at client

Here console application is client which is going to consume stored procedure. Here I am adding client in same solution of service, by right clicking and adding new project then selecting console application from the Windows tab.

Add reference to the client



Add Service Reference , just click Discover in solution ( if client and service is in same solution else copy paste URL of service there)



Add namespace

using System.Data.Services.Client;
using Client.ServiceReference1;

Here Client is name of the test project.

Program.cs

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

namespace Client
{
    class Program
    {
        static void Main(string[] args)
        {
            DataServiceContext context = new DataServiceContext(new Uri("http://localhost:2989/DataService.svc/"));
            IEnumerable<Emp> empResult = context.Execute<Emp>(new Uri("http://localhost:2989/DataService.svc/GetData"));
            foreach (Emp e in empResult)
            {
                Console.WriteLine(e.EmpName + e.EmpId + e.Dept);

            }

            Console.Read();

        }
    }
}

Output



Conclusion

This article explained about, how to use stored procedure in ADO.NET Data Service.

Future scope

To do inspection of, how to use stored procedure for CUD operation. Till then
Happy Coding