Using Stored Procedures for CRUD

Telerik OpenAccess ORM generates dynamic SQL to perform CRUD operations. If for some reason you are not comfortable using dynamically generated SQL or your company policies allow data manipulation only via stored procedures, fear not. Telerik OpenAccess ORM supports CRUD operations via stored procedures too – currently only for MS Sql Server, but soon for the other backends also.

 Stored procedure usage in Telerik OpenAccess ORM can be classified into 2 categories –

• Mapping existing stored procedures and instructing Telerik OpenAccess ORM to use them instead of generating dynamic SQL
• Letting Telerik OpenAccess ORM generate and use the required stored procedures based on your mapping. 

The focus of this blog post is exactly on discussing the both approaches.

 Use existing stored procedures

To use existing stored procedures (sp) you first need to:

1. use the ‘Reverse mapping’ wizard to read the procedures from the database. All stored procedures read are listed under the ‘Stored Procedures’ node. You can directly call a procedure using the ‘IObjectScope.GetSqlQuery’ method or map a procedure to a CRUD operation on a table so that Telerik OpenAccess ORM uses your stored procedure to perform the operation.
2. Let us consider the ‘Products’ table from the Northwind database with default mappings generated by the wizard. This would result in a ‘Product’ class. Assume you have a procedure ‘sp_insert_product’ that inserts records in the Product table. To map this sp to the ‘INSERT’ operation of the Product table you need to select the ‘Products’ table node and check the ‘Use Stored Procedures’ checkbox. What you now see in the ‘Stored Procedure Mapping’ grid is the required procedure that is calculated by Telerik OpenAccess ORM, on the left, and your procedure – sp_insert_product, mapped on the right. Telerik OpenAccess ORM tries to find a suitable procedure based on the requirements and if any procedure fits the requirement it will try and map it automatically. Please review the mapping carefully as the parameter mapping might not always be right. You can adjust the parameter or entire procedure mapping if required.
3. If you do not have an existing procedure that can be mapped you can select the ‘<Create Stored Procedure>’ option and Telerik OpenAccess ORM will create and use the required stored procedure for you.
4. Note that if certain procedures have less parameters than required, Telerik OpenAccess ORM will filter them and so you won’t see them in the list of available procedures.
5. Once you have mapped your procedures to CRUD operations on a table the next steps is to generate the corresponding classes. This will generate the appropriate mapping entries in the app.config file.
6. Only one more step and you are ready to go. The ‘Update database’ project property (available by pressing F4 after the project is selected) is set to ‘False’ by the Reverse mapping wizard. You need to set this to ‘True’ after generating the classes and rebuild the project. Telerik OpenAccess ORM will create the required stored procedures, if any, as the post-build step.

 

 Notice in the screenshot above that the ‘sp_insert_product’ stored procedure has been read from the database and is mapped to the INSERT operation on table ‘Product’ and for the UPDATE operation Telerik OpenAccess ORM will create the required stored procedure for you.
You can now use the stored procedures to perform CRUD operations. Use the API as always and Telerik OpenAccess ORM will now call your stored procedures instead of dynamic SQL. 

 Letting Telerik OpenAccess ORM generate the required procedures

Until now we discussed mainly the “Schema First” approach. What if you have forward mapped your class model or so called “Model First” approach? Well, things work pretty much the same way in this case too. Telerik OpenAccess ORM needs the right mapping entries in the app.config file in order to generate the right objects (tables, procedures etc) on the database. The ‘Forward Mapping’ wizard does not currently support specifying the mapping for stored procedures but this can be easily done by editing the app.config file.

Let us consider the same example again – a ‘Product’ class with the following basic mapping.

The mapping allows for a lot of flexibility when it comes to generating stored procedures. You can specify just the name of the procedure and let Telerik OpenAccess ORM generate the rest or you can specify the name and order of all the individual procedure parameters and also the PL/SQL code that makes up the procedure.

Let us look at the basic mapping required to create stored procedures for the ‘Product’ class

This basic mapping instructs Telerik OpenAccess ORM to create 3 procedures for the insert, update and delete operations. All the procedure details like it’s name, parameter name, types and the procedure code are all calculated by Telerik OpenAccess ORM. The delete stored procedure name is explicitly specified in the mapping.

In case you want Telerik OpenAccess ORM to use stored procedures for the insert and delete operations but not for the update, all you need to do is specify ‘True’for the ‘dynamicSql’ attribute as follows

and it will continue to use dynamic sql to update Product instances.

If you want to fine-tune things further, you can have a look at the documentation for more details on how to customize the stored procedure generation even more. So go ahead and give it a shot.