When developing data access applications with relational databases, we will often use stored procedures to encapsulate some frequently used queries. We can also gain performance improvements by using stored procedures (compared to using raw SQL queries).
Then, can we also take advantages of database stored procedures in our WCF Data Services which expose data from relational database? Absolutely yes! In this recipe, we will discuss how to expose data entities from relational database via stored procedures.
The service here will expose two stored procedures from the Northwind database. They are the CustOrdersOrders procedure (return Order list of a given customer) and the Ten Most Expensive Products procedure. The following are the raw signatures of these two stored procedures:
ALTER PROCEDURE [dbo].[Ten Most Expensive Products] AS ... ALTER PROCEDURE [dbo].[CustOrdersOrders] @CustomerID nchar(5) AS ...
The source code for this recipe can be found in the \ch01\ODataSPServiceSln\ directory.
1. Create a new ASP.NET Empty Web Application.
2. Create the ADO.NET Entity Framework data model and include the stored procedures together with the tables.
We can select the database tables, views, and stored procedures we want in the Choose Your Database Objects dialog (see the following screenshot). In this case, we need to select all tables and two stored procedures.
3. Add Function Import for the stored procedures in the Data Model class.
4. Open the EF designer by double-clicking on the generated data model
(.edmxfile in Visual Studio Solution Explorer).5. Right-click on the designer surface and fire the Function Import... context menu (see the following screenshot).
6. In the Add Function Import dialog, specify the detailed information of the target stored procedure we want to import.
The following screenshot shows the import settings for the CustOrdersOrders procedure:
The return value of the previous stored procedure mapping function is a custom complex object. You can create this complex data type (based on the columns returned in the stored procedure) by using the Create New Complex Type button at the bottom of Add Function Import dialog (see the following screenshot).
7. Add custom operations in the WCF Data Service class, which directly invokes the stored procedure mapping functions imported in the previous step.
The following code snippet shows the custom operations definition in the sample WCF Data Service:
[WebGet] public IQueryable<ExpProductObj> GetTop10ExpensiveProducts() { return this.CurrentDataSource. GetTop10ExpensiveProducts().AsQueryable(); } [WebGet] public IQueryable<CustomerOrderObj> GetOrdersByCustomer(string custID) { return this.CurrentDataSource. GetOrdersByCustomer(custID).AsQueryable(); }8. Launch the service and invoke the stored procedure based operations in the web browser.
The following screenshot shows the web browser output by invoking the
GetOrdersByCustomeroperation in the sample service:
To use stored procedures in WCF Data Service (using the ADO.NET Entity Framework data model as data source), we need to import stored procedures as functions in the generated EF data model class. In this sample, we create some custom data types for the return value of each stored procedure mapping function. This is because in most cases, the returned data columns from a given stored procedure don't exactly match a complete data entity type (corresponding to the target database table).
In addition to the imported functions on EF data model class, we also need to add custom operations within the WCF Data Service class. These operations simply delegate the operation call to the corresponding stored procedure mapping functions.
When calling a service operation mapping to a void stored procedure (which does not return any value), we can simply use the URL address of the operation (relative from the service base address). For stored procedures that take some input parameters, we can supply the parameters by using query strings in the operation URL (as shown in the previous GetOrdersByCustomer operation sample).