Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
Forward Mapping Stored Procedures
Programmer's Guide > OpenAccess ORM Classic (Old API) > Programming With OpenAccess > Stored Procedures Support > Forward Mapping Stored Procedures

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.

Telerik OpenAccess ORM supports mapping of stored procedures. Each existing stored procedure can be reverse mapped to a static method and executed from the application code. In addition, custom stored procedures can be defined to replace the default insert/update/delete operations and provide stronger security . These stored procedures can be set global or local for a particular class.

All examples in this topic use the Northwind data model.

 

The process of modifying the database schema from already defined class model is called “Forward mapping”. The same name can be applied to the process of creating stored procedures in the database using the user definitions provided in the App.config file. These custom stored procedures replace the default mechanism of Telerik OpenAccess ORM for write operations. The usage of procedures can be specified global for all classes or only for a certain type. To enable global use of stored procedures for all insert, update and delete operations, use the Backend Configuration dialog or add these setting to the backend configuration section of the App.config file:

App.config Copy Code
<backendconfiguration id="mssqlConfiguration" backend="mssql">
  
<useStoredProceduresForInsert>true</useStoredProceduresForInsert>
  
<useStoredProceduresForUpdate>true</useStoredProceduresForUpdate>
  
<useStoredProceduresForDelete>true</useStoredProceduresForDelete >
</
backendconfiguration>

A separate node should be added for each write operation type. To override the global settings and deny the usage of stored procedures for a class, enable the Dynamic SQL generation in the relevant “class” node in the App.config file:

App.config Copy Code
<class name="Person">
  
<procedures>
 
<insert dynamicSql="true"/>
 
<update dynamicSql="true"/>
 
<delete dynamicSql="true"/>
  
</procedures>
</class>

Each stored procedure has one-to-one relationship with a class. Following this design, the stored procedure definitions should be set in the “procedures” subnode of the class element. The example below shows a definition of a stored procedure which updates the CategoryName property of the Category with CategoryID passed as parameter.

App.config Copy Code
<class name="Category">
<procedures>
  
<update name="UpdateCategoryName" create="true">
 
<parameter name="CategoryName" field="categoryName" />
 
<parameter name="CategoryID" field="categoryID" />
 
<code>UPDATE [Categories] SET [categoryName] = @CategoryName WHERE [categoryID] = @CategoryID </code>
  
</update>
</procedures>

</class>
The explicit stored procedure definition overrides the default mechanism used by Telerik OpenAccess ORM. The custom procedure will be used every time when an object of this type is being modified. You may not be able to update all class fields if they are not specified as parameters for the stored procedure.

Obtaining the last inserted value

While inserting new records, primary key columns can have their values generated by the server itself using mechanisms like ‘Identity column’ for MSSQL Server, triggers and sequences in Oracle etc. Telerik OpenAccess ORM needs to obtain this value after the execution of the procedure. This value can be obtained as a result of the procedure – with MSSQL Server or via an OUT parameter for backends like Oracle and MySql. For a user mapped procedure the ‘autoIncParam’ attribute can be used to specify which parameter carries this value.The 'autoIncParam' attribute can be specified only at the 'insert' procedure mapping node and if specified, the corresponding parameter node should be present.

 

Defined stored procedures will be stored to the database during building the project. To ensure that Telerik OpenAccess ORM is able to modify the database schema, set the “UpdateDatabase” property of the project to “True”.


For detailed information regarding the node specification, please read the Stored Procedures Support article as well.