How to: Map CUD Operations to 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 describes how to use the Telerik Data Access Visual Designer to map the insert, update, and delete operations of a persistent type to stored procedures.

Telerik Data Access generates a class derived from OpenAccessContext that represents the container in the conceptual model. This class exposes a SaveChanges() method that triggers updates to the underlying database. These updates can use SQL statements that are specified by the developer. The application code you use to create, update, and delete types is the same whether you use stored procedures to update the database or not.

For the purpose of this demo a domain model with one class (named Category) will be used. The Category class is mapped to the corresponding table in the database (Categories).

Mapping the Insert, Update, Delete Operations to a Stored Procedure

To map the insert, update, delete operations to a stored procedure:

1. Select the target type in the Telerik Data Access Visual Designer. Open the Mapping Details Editor. The mapping information for the selected type is loaded in the Mapping Details Editor.
2. Navigate to the CUD Mappings view.
3. The view contains three tab pages - respectively for the Create, Update, and Delete operations.

4. Check the Map Procedure option for the desired operation.

   

5. Save your changes.

Telerik Data Access automatically creates a new stored procedure and adds it to the storage part of your domain model. All stored procedure parameters are automatically mapped to the corresponding properties of the class.

Note that there are specific rules for the names of the stored procedure. For example, the stored procedure for insert operation of the category entity is named sp_oa_ins_categories. The names of the CUD stored procedures are automatically generated by Telerik Data Access and cannot be modified.

Updating Your Database Schema

Creating CUD mappings for your entity in the Visual Designer is only the first step. The CUD stored procedures are created and added to the domain model. But they don't exist in the database. The second step is to update your database by using the Update Database from Model wizard:

1. Right-click on an empty area in the Visual Designer and select Update Database From Model.
2. The Update Database from Model wizard appears. If there is no database connection specified for the domain model, the Setup Database Connection Dialog appears. Here you need to specify a connection to your database. Click Next to continue.
3. The next page is the Select Changes Dailog . Here you need to select which changes to apply to your database schema, as well as to select the updating strategy. If your database is already created (e.g. you are doing Database First Scenario), then select the Migrate Database strategy. The Migrate Database option allows you to migrate your existing database to the latest state of the model. If your database is not created (initialized) yet (e.g. you are doing Model First Scenario), then select the Create Database strategy. Click Next to proceed.
4. On the Summary Page, you could set deployment options, preview and execute the generated DDL script.

The code-snippet below shows you the generated script for the CUD stored procedures for the Category entity.

-- Generating stored procedure for Telerik Data Access.
CREATE PROCEDURE [sp_oa_ins__categories] ( @Picture image = NULL, 
                                           @Description ntext = NULL, 
                                           @CategoryName nvarchar(15) )  
AS
INSERT INTO [Categories] ( [Picture] , [Description] , [CategoryName] ) 
VALUES ( @Picture , @Description , @CategoryName )
SELECT scope_identity();
RETURN
GO
-- Generating stored procedure for Telerik Data Access.
CREATE PROCEDURE [sp_oa_upd__categories] ( @Picture image = NULL, 
                                           @Description ntext = NULL, 
                                           @CategoryName nvarchar(15), 
                                           @CategoryID INT , 
                                           @OLD_CategoryName nvarchar(15) )  
AS
UPDATE [Categories] 
SET [Picture] = @Picture , [Description] = @Description , [CategoryName] = @CategoryName 
WHERE [CategoryID] = @CategoryID AND [CategoryName] = @OLD_CategoryName;
RETURN
GO
-- Generating stored procedure for Telerik Data Access.
CREATE PROCEDURE [sp_oa_del__categories] ( @CategoryID INT )  AS
DELETE FROM [Categories] WHERE ( [CategoryID] = @CategoryID );
RETURN
GO