Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
How to: Use Views and Stored Procedures to Encapsulate the CRUD Operations.
Programmer's Guide > OpenAccess ORM Classic (Old API) > OpenAccess Tasks > Working with Objects > How to: Use Views and Stored Procedures to Encapsulate the CRUD Operations.

Glossary Item Box

This documentation article is a legacy resource describing the functionality of the deprecated OpenAccess Classic only. The contemporary documentation of Telerik OpenAccess ORM is available here.

In order to provide stronger security, database views can be used instead of real tables. A view can expose only a few table columns or combine columns from many tables. The goal of using views is to expose only the data needed for particular operation and nothing additional. Telerik OpenAccess ORM provides the ability to reverse map views to classes.

As the views may contain columns from multiple related tables, write operations cannot be always handled automatically by the ORM software. In such situations custom stored procedures can be used to define the write operations on views. The role of the O/R Mapper here is just executing the stored procedure and passing the updated fields. This way no SQL is generated dynamically and the data management is encapsulated on the server.

To illustrate the process, the following stored procedures are used to define Create/Update/Delete operations on the “Alphabetical list of products” view of the Northwind database:

SQL Copy Code
CREATE PROCEDURE [dbo].[InsertProduct]
( @CategoryID
INT = NULL,
@CategoryName nvarchar(15)
= NULL,
@Discontinued
BIT = NULL,
@ProductID
INT,
@ProductName nvarchar(40)
= NULL,
@QuantityPerUnit nvarchar(20)
= NULL,
@ReorderLevel
SMALLINT = NULL,
@SupplierID
INT = NULL,
@UnitPrice money
= NULL,
@UnitsInStock
SMALLINT = NULL,
@UnitsOnOrder
SMALLINT = NULL )

AS
INSERT INTO [Products]
([CategoryID] , [Discontinued] , [ProductName] ,
 [QuantityPerUnit] ,  [ReorderLevel] , [SupplierID] ,
 [UnitPrice] , [UnitsInStock] , [UnitsOnOrder] )

VALUES
(@CategoryID , @Discontinued , @ProductName ,
 @QuantityPerUnit , @ReorderLevel , @SupplierID ,
 @UnitPrice ,@UnitsInStock , @UnitsOnOrder )
SQL Copy Code
CREATE PROCEDURE [dbo].[UpdateProduct]
( @CategoryID
INT = NULL,
@CategoryName nvarchar(15)
= NULL,
@Discontinued
BIT = NULL,
@ProductID
INT,
@ProductName nvarchar(40)
= NULL,
@QuantityPerUnit nvarchar(20)
= NULL,
@ReorderLevel
SMALLINT = NULL,
@SupplierID
INT = NULL,
@UnitPrice money
= NULL,
@UnitsInStock
SMALLINT = NULL,
@UnitsOnOrder
SMALLINT = NULL ,
@OLD_CategoryID
INT = NULL,
@OLD_CategoryName nvarchar(15)
= NULL,
@OLD_Discontinued
BIT = NULL,
@OLD_ProductName nvarchar(40)
= NULL,
@OLD_QuantityPerUnit nvarchar(20)
= NULL,
@OLD_ReorderLevel
SMALLINT = NULL,
@OLD_SupplierID
INT = NULL,
@OLD_UnitPrice money
= NULL,
@OLD_UnitsInStock
SMALLINT = NULL,
@OLD_UnitsOnOrder
SMALLINT = NULL )
AS
UPDATE [Products]
SET [CategoryID] = @CategoryID ,
[Discontinued]
= @Discontinued ,
[ProductName]
= @ProductName ,
[QuantityPerUnit]
= @QuantityPerUnit ,
[ReorderLevel]
= @ReorderLevel ,
[SupplierID]
= @SupplierID ,
[UnitPrice]
= @UnitPrice ,
[UnitsInStock]
= @UnitsInStock ,
[UnitsOnOrder]
= @UnitsOnOrder
WHERE [ProductID] = @ProductID
AND (([CategoryID] IS NULL AND @OLD_CategoryID IS NULL) OR ([CategoryID] = @OLD_CategoryID))
AND (([Discontinued] IS NULL AND @OLD_Discontinued IS NULL) OR ([Discontinued] = @OLD_Discontinued))
AND (([ProductName] IS NULL AND @OLD_ProductName IS NULL) OR ([ProductName] = @OLD_ProductName))
AND (([QuantityPerUnit] IS NULL AND @OLD_QuantityPerUnit IS NULL) OR ([QuantityPerUnit] = @OLD_QuantityPerUnit))
AND (([ReorderLevel] IS NULL AND @OLD_ReorderLevel IS NULL) OR ([ReorderLevel] = @OLD_ReorderLevel))
AND (([SupplierID] IS NULL AND @OLD_SupplierID IS NULL) OR ([SupplierID] = @OLD_SupplierID))
AND (([UnitPrice] IS NULL AND @OLD_UnitPrice IS NULL) OR ([UnitPrice] = @OLD_UnitPrice))
AND (([UnitsInStock] IS NULL AND @OLD_UnitsInStock IS NULL) OR ([UnitsInStock] = @OLD_UnitsInStock))
AND (([UnitsOnOrder] IS NULL AND @OLD_UnitsOnOrder IS NULL) OR ([UnitsOnOrder] = @OLD_UnitsOnOrder))
UPDATE [Categories]
SET [CategoryName] = @CategoryName
WHERE [CategoryID] = @CategoryID
AND (([CategoryName] IS NULL AND @OLD_CategoryName IS NULL) OR ([CategoryName] = @OLD_CategoryName))
SQL Copy Code
CREATE PROCEDURE [dbo].[DeleteProduct]
( @ProductID
INT )

AS
DELETE FROM [Products]
WHERE [ProductID] = @ProductID

When the stored procedures are defined in the database, you can start with the mapping. This article describes only the process of enabling the usage of views with stored procedures. More information on how to map views can be found in the How to: Reverse Map and Use Database Views with Telerik OpenAccess ORM topic.

To assign a stored procedure to each write action, open the Reverse mapping wizard and select the view from the list on the left. Then enable the “Use Stored Procedures” checkbox.

A grid presenting the available write operations show up. There is a combobox next to each operation, which contains the stored procedures that match the required parameters. Select the relevant stored procedure from the list and it will be used instead of the auto-generated SQL. Make sure that each parameter is correctly mapped to a column.

There are two additional options that can be selected – “Use Dynamic SQL” and “Create Stored Procedure”. The first one will preserve the default mechanism of creating SQL dynamically for the chosen operation. The second will force Telerik OpenAccess ORM to automatically generate a stored procedure and store it in the database. Note that if you use this option, the UpdateDatabase property of the project should be set to True.

Click on the “Save Config” button and the mapping is done. Then the class can be used as usual:

C# Copy Code
IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();
//create new product
scope.Transaction.Begin();
AlphabeticalListOfProduct product =
new AlphabeticalListOfProduct()
{
   CategoryID = 4,
   ProductName =
"NewProduct"
}
;
scope.Add(product);
scope.Transaction.Commit();
product =
null;
//retrieve and update the product
scope.Transaction.Begin();
product = (from p
in scope.Extent<AlphabeticalListOfProduct>()
          where p.ProductName ==
"NewProduct"
          
select p).FirstOrDefault();
product.ProductName =
"ChangedProduct";
product.CategoryID = 3;
product.CategoryName =
"New" + product.CategoryName;
scope.Transaction.Commit();
//delete the product
scope.Transaction.Begin();
scope.Remove(product);
scope.Transaction.Commit();
VB.NET Copy Code
Dim scope As IObjectScope = ObjectScopeProvider1.GetNewObjectScope()
'create new product
scope.Transaction.Begin()
Dim product As New AlphabeticalListOfProduct() With {.CategoryID = 4, .ProductName = "NewProduct"}
scope.Add(product)
scope.Transaction.Commit()
product = Nothing
'retrieve and update the product
scope.Transaction.Begin()
product = (From p In scope.Extent(Of AlphabeticalListOfProduct)() _
           Where p.ProductName = "NewProduct" _
           Select p).FirstOrDefault()
product.ProductName = "ChangedProduct"
product.CategoryID = 3
product.CategoryName = "New" & product.CategoryName
scope.Transaction.Commit()
'delete the product
scope.Transaction.Begin()
scope.Remove(product)
scope.Transaction.Commit()