How to: Create a Domain Method for a Stored Procedure

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.

Many databases use stored procedures and functions to perform predefined logic on database tables. Although one of the key features of Telerik Data Access is its ability to support LINQ queries, as well as build the commands for inserting, updating and deleting data, you may want to override these steps and use your own predefined stored procedures and functions. This topic describes how to use Telerik Data Access Visual Designer to create a domain method for a stored procedure. Recall that Choose Database Items Dialog in the Telerik Data Access New Domain Model Wizard allows you to select which database items will be included in the domain model. You could select tables, views, stored procedures and functions. When a stored procedure is included in the domain model, you have the option to create a domain method for it. Adding a domain method allows you to call the corresponding stored procedure directly from your application 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.

The insert, update, and delete operations of a persistent type can be mapped to stored procedures. For more information, see How to: Map CUD Operations to Stored Procedures.

To complete this walkthrough, you will need to create a new domain model based on the SofiaCarRental database.

When the Telerik Data Access New Domain Model Wizard generates an .RLINQ file from a database, it creates persistent types in the domain model for each table included in the model. The procedure below describes how to create a domain method for a stored procedure that returns a collection of persistent types.

To create a domain method for a stored procedure:

From the Model Schema Explorer, expand the Stored Procedures and Functions node.
Right-click a stored procedure and select Create Domain Method.

In this demo, a stored procedure named LoadCarsByMake is used. It is based on the SofiaCarRental database. The procedure takes a single @CarMake parameter and returns all cars
filtered by make.
```
CREATE PROCEDURE LoadCarsByMake(@CarMake varchar(50))
AS
BEGIN
  SELECT *
  FROM Cars
  WHERE Make = @CarMake
END
```
If you have already generated a domain model, you could include the LoadCarsByMake stored procedure by using the Update From Database Wizard.
The Domain Method Editor appears. Fill in the settings for the new function:
1. Specify a name for the generated method in the Domain Method Name text field. The default name of the generated method will be the same as the name of the stored procedure. For example, if the stored procedure is named "LoadCarsByMake", the generated method will be named LoadCarsByMake. In this demo, the function name is changed GetCarsByMake.
2. Specify the stored procedure for which you are creating a domain method in the Stored Procedures drop-down, e.g. LoadCarsByMake.
3. The next step is to specify one of the four basic return types: Scalar, Persistent, Complex, or None, and select the specific return type from the corresponding drop-down. In this demo, you are going to load cars from the database. The Car entity already exists in the domain model; it is mapped to the Cars table. So, select the Persistent Type option. From the available drop-down, select the Car persistent type.
Click OK to close the editor and create a method for the stored procedure. The new domain method will be added to your model. You can see all generated methods under the Domain Methods nodes in the Model Object Explorer.

What Just Happened?

When you click OK, the new GetCarsByMake method will be added to your context class. The signature of the generated method is:

public IEnumerable<StoredProceduresDemo.Car> GetCarsByMake(string carMake)
{
}

Public Function GetCarsByMake(ByVal carMake As String) As _
    IEnumerable(Of StoredProceduresDemoVB.Car)
End Function

How It Works?

The generated domain method internally uses the generic ExecuteQuery<T> method exposed by the OpenAccessContext. The ExecuteQuery<T> method executes the specified stored procedure and materializes the results to instances of T. For more information, please refer to How the Generated Methods Retrieve the Data.

Materialized persistent types are automatically attached to the context. Namely, if you modify the materialized Cars objects and call the SaveChanges method of the context, then all changes will be committed to the database.

public IEnumerable<StoredProceduresDemo.Car> GetCarsByMake(string carMake)
{
   OAParameter parameterCarMake = new OAParameter();
   parameterCarMake.ParameterName = "CarMake";
   parameterCarMake.Value = carMake;
   IEnumerable<StoredProceduresDemo.Car> queryResult = 
        this.ExecuteQuery<StoredProceduresDemo.Car>("LoadCarsByMake", 
            CommandType.StoredProcedure, parameterCarMake);

   return queryResult;
}

Public Function GetCarsByMake(ByVal carMake As String) As IEnumerable(Of StoredProceduresDemoVB.Car)
 Dim parameterCarMake As New Telerik.OpenAccess.Data.Common.OAParameter
 parameterCarMake.ParameterName = "CarMake"
 parameterCarMake.Value = carMake
 Dim queryResult As IEnumerable(Of StoredProceduresDemoVB.Car) = 
        Me.ExecuteQuery(Of StoredProceduresDemoVB.Car)("LoadCarsByMake", 
            CommandType.StoredProcedure, parameterCarMake)

 Return queryResult
End Function

The generated context method allows you to call the corresponding stored procedure from your code.

using (EntitiesModel dbContext = new EntitiesModel())
{
   IEnumerable<Car> bmwModels = dbContext.GetCarsByMake("BMW");
}

Using dbContext As New EntitiesModel()
 Dim bmwModels As IEnumerable(Of Car) = dbContext.GetCarsByMake("BMW")
End Using

Next Steps

Sometimes the shape of the result returned by the stored procedure may not map to any of the available persistent types in your domain model. In this case, you would need to define a custom CLR type that can hold the result. For more information about mapping non-persistent types to stored procedures and functions, check out the How to: Map a Domain Method Result to a Complex Type topic. The other two possible return types are:

Single Scalar Value - persistent and complex types are only one of the types that can be mapped to data returned from a stored procedure. The How to: Map a Domain Method Result to a Single Scalar Value topic demonstrates how to create a domain method for a stored procedure that returns a single scalar type.
None - this is the simplest case, i.e. the generated domain method does not return a value.

As you learnt from this example, the generated domain methods internally use the Telerik Data Access ADO API. Besides querying a Telerik Data Access Domain Model by using standard LINQ expressions, there is one more way you can query the domain model, and that is through the Telerik Data Access ADO API. Using LINQ queries is simple and easy. However, if you are concerned about the performance and comfortable writing ADO.NET like code, then the Telerik Data Access ADO API is perfect for you. For more information, refer to How the Generated Methods Retrieve the Data.