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()
scope.Transaction.Begin()
Dim product As New AlphabeticalListOfProduct() With {.CategoryID = 4, .ProductName = "NewProduct"}
scope.Add(product)
scope.Transaction.Commit()
product = Nothing
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()
scope.Transaction.Begin()
scope.Remove(product)
scope.Transaction.Commit() |