Telerik OpenAccess ORM - Stored procedure execution made easier

Over the past several months we did get a lot of questions regarding executing stored procedures that return a result set and/or out parameters, being able to bind the results to the UI etc. The existing API worked fine in cases where the procedure returned rows of a persistent type, but if the returned columns did not map to a persistent type there was really no way to get back instances of a non-persistent type that mapped to the result set. We’ve been listening very keenly to the feedback from customers and with the Q2 2011 release we are glad to introduce a new API that solves these common customer use cases . This post will demonstrate one of these use cases – executing a stored procedure and binding the result to a grid.

For the purpose of this blog post we will execute  the ‘[dbo].[CustOrderHist]’ stored procedure which is part of the standard Microsoft SQL Server Northwind database.

Here is what the procedure looks like.

   1: CREATE PROCEDURE [dbo].[CustOrderHist] @CustomerID nchar(5)

   2: AS

   3: SELECT ProductName, Total=SUM(Quantity)

   4: FROM Products P, [Order Details] OD, Orders O, Customers C

   5: WHERE C.CustomerID = @CustomerID

   6: AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

   7: GROUP BY ProductName

To execute this stored procedure we will use a new method that is inherited by the generated context class – ‘ExecuteQuery<T>’. This method has 2 overloads – one that accepts a ‘System.Data.CommandType’ that specifies how the sql will be interpreted, and the other that defaults to ‘CommandType.Text’. Assuming that the shape of the result returned by the stored procedure does not map to any of the available persistent types in our domain model, we would need to define a CLR type that can hold the results. Here is what a simple class representing the ‘CustOrderHist’ results could look like

   1: public class CustOrderHist

   2: {

   3: public string ProductName { get; set; }

   4:  

   5: public int Total { get; set; }

   6: }

The only requirements for this CLR type are that -

• it should have a parameterless constructor so that the OpenAccess runtime can create instances of the type.
• the property names should match the column names that are returned by the procedure. The case is not considered here.
• the property types should be compatible with column types

In case a corresponding property for a column is not found the value of this column is ignored.

Let us now look at the code required to call this stored procedure.

   1: using (EntitiesModel context = new EntitiesModel())

   2: {

   3:     OAParameter customerId = new OAParameter("@CustomerID", "ALFKI");

   4: 

   5:     var result = context.ExecuteQuery<CustOrderHist>("CustOrderHist", CommandType.StoredProcedure, customerId);

   6: }

It’s as simple as that!

line 1 – We create and instance of the context which is required to execute the stored procedure.

line 3 – We create an instance of the OAParameter (which is a new type introduced in the new low level (ADO) API) and set the parameter name and value.

line 5 – We specify the CLR type which will hold the value of each row returned by the procedure, the stored procedure name and the parameters required to execute the stored procedure.

What you get back is completely materialized list of ‘CustOrderHist’ instances ready to be used/bound to a grid.

For sake of simplicity, we will bind the result to a GridView in a simple web application using the following code in the Page_Load method (here I assume that you’ve created a simple web application and added a GridView to it)

   1: this.GridView.DataSource = result;

   2: this.GridView.DataBind();

Here’s the final result.

So now you see how simple it is to execute a stored procedure or any sql that returns a result and materialize it to a CLR type. We’ve tried to keep the API as close to the standard ADO.NET way of working , so specifying the parameter names, it’s values and obtaining the OUT parameter values is exactly the same. You can just access the OUT parameter value after the method call.

Keep a look out for this space since we will demonstrate further interesting stuff like obtaining the OUT parameter values, return values, multiple result sets in upcoming posts.