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
-
Dept
-
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
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