CRUD with security token

[Grigoriev]

I have quite complex  model with lots of related entities which is mapped to real tables and several stored procedures for general purposes such as search, authentication and so on. According to new security requirements i need to map all my CRUD operations with tables to stored procedures. For example for table TBL1 i have 4 procedures:
   select_TBL1(string securityToken)
   update_TBL1(string securityToken, ...)
   delete_TBL1(string secutiryToken, ...)
   insert_TBL1(string securityToken, ...)
where securityToken is session identifier for user, stored procedures provide some security checks for this identifier.

So, i have some exact questions:
1.Can i map READ operation for table to stored procedure.
2.Can i map CUDs for tables to stored procedues with custom not reserved names such as sp_oa_ins_TBL1 and so on.
2.Can i provide implicit parameter in procedures which is not mapped to any table column.

Can you please advise me how to solve this task.
Thank you.

[Doroteya]

Hello Sergey,

Please find the answers to your questions, as follows:

1. Can i map READ operation for table to stored procedure
The Mapping Details Editor does not support mapping to a default Read operation for a given class, since its result most commonly needs to meet certain criteria. In other words, you need to filter and/or shape the result. Generally, for reading operations, Telerik OpenAccess ORM offers either the IQueryable<T> endpoints of the context (e.g. public IQueryable<Customer> Customers) or using domain methods based on custom stored procedures in the database.

2. Can i map CUDs for tables to stored procedures with custom not reserved names such as sp_oa_ins_TBL1 and so on 
The scenario the generated CUD stored procedures are involved in is the following: usually, when you add/update/delete a record, OpenAccess ORM generates a SQL statement for the record and executes it against the database on SaveChanges(). If you map a CUD stored procedure to the class, OpenAccess ORM will use it for the corresponding operation instead of generating the appropriate SQL statement every time. During this process, OpenAccess ORM relays very much on the naming convention of the mapped stored procedure. Here, however, you can find a feature request for giving more control to the developers on the names of these default procedures. Voting about it may bring it one step closer to implementation.

3. Can i provide implicit parameter in procedures which is not mapped to any table column
The generated CUD stored procedures do not allow modification of the parameters they were generated with. In other words, even if you modify the table they are mapped to, you will need to re-generate the procedures. You can use any number of parameters of any type in custom stored procedures and map them to domain methods.

I hope you find this feasible. If you have additional questions or need help with the scenario you implement, do not hesitate to get back to us.



Regards,
Doroteya
Telerik