How to: Expose Stored Procedures

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

This topic illustrates how to expose stored procedures or functions (domain methods) through a WCF Data Service.

When a stored procedure or database function is included in the domain model, you could create a function (domain method) for it. Adding a domain method allows you to call the corresponding stored procedure from your code. You could create a domain method that returns a single scalar value, a collection of the Persistent Types, or Complex Types, or no value. For more information, check out the Stored Procedures and Functions section.

Suppose you have a domain model containing a stored procedure named GetCarsByMake. It is mapped to a domain method, which returns a persistent type.

In order to expose the stored procedure you will have to create a service operation method. Service operations are endpoints that allow you to add business logic that can't be supported by the service RESTful interface. The following rules must be addressed when you create service operations:

• Service operations need to have a public modifier.
• Service operations should be marked with the WebGet attribute to enable invoking of HTTP GET requests or with the WebInvoke attribute to enable HTTP POST, PUT or DELETE requests.
• Service operations should return one of the following types - void, IEnumerable<T> or IQueryable<T>. The generic placeholder T should be either an entity from your domain model or a primitive type. If you want to return a single entity, you have to use the SingleResult attribute.

Service operations can only return persistent types. If a persistent type has a transient property, the value of that property will not be returned.

In this example, the service operation just invokes the GetCarsByMake domain method.

Note that the service operation is defined in a partial class, as it is recommended in the How to: Extend Data Service topic.

using System.Linq;
using System.ServiceModel.Web;
namespace WebApplication
{
   public partial class EntitiesModelService
   {
       [WebGet]
       public IQueryable<Car> LoadCarsByMake(string make)
       {
           return this.CurrentDataSource.GetCarsByMake(make).AsQueryable();
       }
   }
}

Imports System.Linq
Imports System.ServiceModel.Web
Partial Public Class EntitiesModelService
    <WebGet()>
    Public Function LoadCarsByMake(ByVal make As String) As IQueryable(Of Car)
        Return Me.CurrentDataSource.GetCarsByMake(make).AsQueryable()
    End Function
End Class

Additionally, you need to configure the service operation access rules in the InitializeService method. This method is located in the other partial file, generated by the Add Telerik Data Access Service wizard.

[System.ServiceModel.ServiceBehavior( IncludeExceptionDetailInFaults = true )]
public partial class EntitiesModelService : OpenAccessDataService<WebApplication.EntitiesModel>
{
   public static void InitializeService( DataServiceConfiguration config )
   {
       config.SetEntitySetAccessRule( "Cars", EntitySetRights.All );
       config.SetServiceOperationAccessRule( "LoadCarsByMake", ServiceOperationRights.All );
       config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
   }
}

<System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults:=True)> _
Partial Public Class EntitiesModelService
    Inherits OpenAccessDataService(Of WebApplication.EntitiesModel)
    Public Shared Sub InitializeService(ByVal config As DataServiceConfiguration)
        config.SetEntitySetAccessRule("Cars", EntitySetRights.All)
        config.SetServiceOperationAccessRule("LoadCarsByMake", ServiceOperationRights.All)
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3
    End Sub
End Class