The Telerik OpenAccess ORM tool supports usage of stored procedures for retrieving and modifying data. Stored procedures can be used to retrieve, insert, update, and delete data.
Many data-aware applications rely on stored procedures to provide the following benefits:
- Security. Database users can be denied direct access to tables or other database objects. Database administrators need only grant execute permissions on stored procedures to create single entry points for data access. This significantly reduces the possible SQL injection risks. Input and output parameters configured to use default values enable strict parameter validation. Validation code in stored procedures can limit the available actions.
- Encapsulation. Complex data logic, explicit transactions and other database operations can be written once in stored procedure code and executed from multiple client applications. Errors, exceptions, and concurrency violations can be handled from server-side code, reducing the number of round trips from client applications. Stored procedure code can be modified without affecting client applications as long as the signature of the stored procedure remains unchanged.
- Performance. In some situations, there can be performance gains from using stored procedures. Modern database engines generally treat dynamic SQL statements as efficiently as statements in stored procedures; database administrators maintain more control over performance by enforcing the use of stored procedures.
 |
The Fetch Plans mechanism that allows optimization of the generated SQL will not work with the use of stored procedures. |
Stored procedures that return data are called using the IObjectScope.GetSqlQuery API. More on the topic is available in How to: Execute a Query Using a Stored Procedure
This topic describes the stored procedures used to update data through mappings specified in the mappings used by the Telerik OpenAccess ORM. Stored procedures could be used for insertion or deletion of persistent classes. More on the topic can be found in How to: Update Data Using a Stored Procedure.
Stored procedure definition on Telerik OpenAccess ORM
The stored procedures used by Telerik OpenAccess ORM are table-dependent. This means that there is 1-to-1 definition for table-to-stored procedure in the mapping. The stored procedures should be developed in a way that takes into consideration the characteristics mentioned previously.
All stored procedures that could be used for insertion, deletion or creation of persistent classes can be defined in the following way:
- Mapping an existing procedure
Only explicit definitions are supported currently
- Defining a stored procedure to be created in the mapping file.
Configuration Settings used with Stored Procedures
In order to use stored procedures with OpenAccess, you have to specify it in one of the following ways:
- Enable stored procedure usage globally. This has to be done for insert, update and delete separately.
| .config |
 Copy Code |
|
<backendconfiguration id="mssqlConfiguration" backend="mssql">
<useStoredProceduresForInsert>true</useStoredProceduresForInsert>
<useStoredProceduresForUpdate>true</useStoredProceduresForUpdate>
<useStoredProceduresForDelete>true</useStoredProceduresForDelete> |
- Enable stored procedure usage globally, disable stored procedure usage for a specific class.
| app.config |
Copy Code |
|
<class name="Person">
<procedures>
<insert dynamicSql="true"/>
<update dynamicSql="true"/>
<delete dynamicSql="true"/>
</procedures>
</class> |
- Enable stored procedure usage per class. This can be done by just specifying the stored procedure node.
| app.config |
Copy Code |
|
<class name="Person">
<procedures>
<insert/>
<update/>
<delete/>
</procedures>
</class> |
For collections you can specify the insert, delete and clear stored procedures.
| app.config |
Copy Code |
|
<class name="Person">
<field name ="children">
<collection>
<procedures>
<add/>
<clear/>
<remove/>
</procedures>
</collection>
</field>
</class>
|
Second, when there is the need to have a finer control over the name of a stored procedure or its parameter names and types, you can specify that in the mapping configuration. The <procedures> element under the <class> or <collection> element has sub-elements to hold the configuration for the insert,update,delete (for class) or add,remove,clear (for collection) procedures. Each of the procedures can be configured to use a given name (with the name attribute), to be not created or maintained (with the create attribute) or not to be used (with the dynamicSql attribute). Also, the names and types of the parameters can be given for a procedure with the <parameter> sub-element. Furthermore you can use the <code> sub-element to specify the code that should be used when the stored procedure is maintained by OpenAccess.
When no name is given for a stored procedure, an auto-generated name is used. The name generator can be configured to obey a style for doing so. The name generator settings are available in the backend configuration dialog.
The names of the ‘children’ collection fields link table stored procedures are specified here; therefore no auto generated name is used for those procedures.
The deletion of the instances of class Person is done by the stored procedure named ‘deletePerson’, which also specified the parameter names, an additionally passed constant value and the used code. The procedure will be created and maintained when OpenAccess updates the database schema.
The insertion of instances of class Person into the database is done by a stored procedure named ‘insPerson’ which is specified to have just one parameter; the SP is not created/maintained by OpenAccess.
The update is specified as using no stored procedure, but dynamic SQL generation, the normal OpenAccess behavior. This way one can exclude SP usage on single action/class level.
Whether a SP is created/maintained by OpenAccess depends also on the value of the db-do-not-create-table value of the class or collection. The class or collection setting is the default value for the stored procedures under that configuration element, but can be altered on the SP level again with the create attribute.
Mapping node specification
The <procedures> node can be placed under the class, collection and map node. Is it inside the class node the sub nodes <insert>, <update> and <delete> are allowed, inside the collection or map node <add>, <remove> and <clear> are valid.
Valid Attributes for <insert><update><delete><add><remove><clear>:
- name: name of the stored procedure to generate
- create: true(default if node is present) if stored procedure should be created/updated during schema migration, false otherwise.
- dynamicSql: false (default if node is present) if the stored procedure should be used for the specific operation. True if dynamic SQL should be generated instead.
Inside the stored procedure type node can be <parameter> nodes with the following attributes
- name: name of the stored procedure parameter
- column: the name of the database column the parameter is related to.
Creating a stored procedure definition
There are two way of preparing the stored procedures definition - implicit and explicit.
- Implicitly: The table definition is used to derive the parameters definitions and the name of the stored procedure.
- Explicitly: The mapping information inside the mapping file is used to define the name, parameters and definition (the code) of the stored procedure.
|
The first approach allows all the required information, even the stored procedure contents to be generated automatically. It matches the forward mapping behavior where you just specify that you want to use stored procedures and all is automatically handled by OpenAccess. |
When implicit definition of stored procedures is used, default names are automatically generated for each procedure. If more fine control is required over the names, types and values used with the stored procedures, this is to can be accomplished using the mapping file to associate stored procedures with the classes and collection fields.
Stored procedure support in forward-mapping and reverse-mapping scenarios
In both scenarios Telerik OpenAccess ORM supports the use of stored procedures not only for returning data, but also for deleting, creating and updating persistent data in the database.
Forward Mapping Scenario
In order to use stored procedures, you will need to enable the feature by a backend configuration element.
| app.config |
Copy Code |
|
<useStoredProceduresForDelete>True</useStoredProceduresForDelete>
<useStoredProceduresForInsert>True</useStoredProceduresForInsert>
<useStoredProceduresForUpdate>True</useStoredProceduresForUpdate> |
|
You can use the backend configuration dialogs to set these settings as well. |
If the usage of stored procedures is not enabled (default), normal dynamic SQL generation takes place. The settings above define that all insert, update or delete operations should be handled using stored procedures. Stored procedures are generated together with the normal schema update and are used during runtime.
This example demonstrates a simple approach to using stored procedures inforwar-mapping scenario:
We have a simple persistent class named Person.
| C# |
Copy Code |
|
[Persistent]
public class Person
{
string fName;
public string FName
{
get { return fName; }
set { fName = value; }
}
string lName;
public string LName
{
get { return lName; }
set { lName = value; }
}
private IList<Person> children = new List<Person>();
public IList<Person> Children
{
get { return children; }
set { children = value; }
}
} |
| VB.NET |
Copy Code |
|
<Persistent> _
Public Class Person
Private fName_Renamed As String
Public Property FName() As String
Get
Return fName_Renamed
End Get
Set(ByVal value As String)
fName_Renamed = value
End Set
End Property
Private lName_Renamed As String
Public Property LName() As String
Get
Return lName_Renamed
End Get
Set(ByVal value As String)
lName_Renamed = value
End Set
End Property
Private children_Renamed As IList(Of Person) = New List(Of Person)()
Public Property Children() As IList(Of Person)
Get
Return children_Renamed
End Get
Set(ByVal value As IList(Of Person))
children_Renamed = value
End Set
End Property
End Class |
The following mapping excerpt defines some stored procedures to be used with the Person class and its link table for the ‘children’ field. And this is how the mapping file looks for this class:
| app.config |
Copy Code |
|
<class name="Person">
<field name="children">
<collection>
<procedures>
<clear name="myclear"/>
<add name="myadd"/>
<remove name="myremove"/>
</procedures>
</collection>
</field>
<procedures>
<delete name="deletePerson" create="true" >
<parameter name="PersonID" field="person_id" />
<extension key="db-type" value="integer" />
</parameter>
<code>DELETE FROM [person] WHERE [person_id] = @para2Name </code>
</delete>
<insert name="insPerson" create="false">
<parameter name="lastName" field="l_name" />
<parameter name="firstName" field="f_name" />
<parameter name="nationality" field="nationality" />
<parameter name="personId" field="person_id" />
<parameter name="defaultNationality" constant="US" mode="in"/>
</insert>
<update dynamicSql="true" />
</procedures>
</class> |
The configuration settings above define three stored procedures for the link table used to hold the values for the ‘children’ field. The procedures myclear, myadd and myremove are using the parameter definitions and names as derived from the link table definition, so basically only the name of the stored procedures are defined.
A more complete example shows the definition of a stored procedure named deletePerson which is used to delete instances of the Person class. The procedure has two parameters, one that uses the primary key of the class (sufficient to delete an instance in the database), and another parameter that passes an integer value 12 to the database server.
In addition to the user defined parameters the style in which the stored procedure code is shown here; note that the code itself is highly dependent on the SQL dialect used.
Reverse Mapping Scenario
With the Reverse Mapping wizard it is possible to select existing stored procedures from the database to be used for each mapped class or collection. The settings are available at the class and collection mapping dialogs.
The reverse mapping wizard reads the available stored procedure definitions and displays them under the ‘Stored Procedures’ node. These procedures can be called directly using the IObjectScope.GetSqlQuery or can be associated to a table to perform CRUD operations.
For the purpose of this example, the Person class defined previously is used in the reverse scenario. We have a table ‘Person’ with 3 columns – ‘person_id’, ‘f_name’ , ‘l_name’ and a join table to store the collection field ‘children’. The database also has stored procedures defined for CRUD operations on these tables.
When the wizard is executed against the database these tables and procedures are read and listed under the Tables and Stored Procedures nodes. The user can map the tables to a class and a collection to have a ‘Person’ class with a ‘children’ collection as shown in the picture below:
Each table that is mapped to a class, collection or map can use stored procedures, by checking the ‘Use Stored Procedures’ check box available at the table level.
For tables that use stored procedures, the ‘Stored Procedure Mapping’ grid displays the required procedures i.e. the minimum parameters the stored procedures should have, to perform CRUD operations on the table.
These required parameters are calculated based on the mappings specified by the user – the columns that are mapped to fields, primary-key(s) of the table, optimistic concurrency column etc. Initially all the operations are mapped to use dynamic SQL, the default. If the user wants to instruct OA to use a stored procedure instead he has to map the procedure he wants to use to the corresponding required parameters. The list of stored procedures read from the database are available in the ‘Mapped Parameters’ column. Once a stored procedure is selected from the list, its parameters are made available for mapping.
In the image below you can observe that we have mapped stored procedures to perform ‘DELETE’ and ‘UPDATE’ operations on the ‘Person’ table. Based on the calculations performed by the wizard the DELETE operation for the ‘Person’ table requires the primary-key column(s) – in this case, ‘person_id’. We have the ‘sp_oa_del_person’ stored procedure that matches the parameter requirements and hence it is mapped to the DELETE operation. Similarly the UPDATE operation is mapped to the ‘sp_oa_upd_person’ table.
Any procedure that is mapped to an operation should provide all required parameters for the operation. It should use the exactly the same number of parameters with the matching type.
Note that the INSERT operation is not mapped to any stored procedure and will hence use dynamic SQL.
|
Once the sources are generated for the class the appropriate entries for using the procedures (mentioned above in the Forward approach) are made in the app.config file. |
Generating static methods for executing Stored Procedures
The user can generate static methods in order to execute Stored Procedures in the database. The stored procedures are listed under the ‘Stored Procedures’ node. The user can select the procedure for which he wants to generate a static method and specify certain properties. The procedures are generated in a static class called ‘StoredProcedure’. This class is created using a template file – templateStoredProcedure.cs/vb .
The user can specify the name of the method to be generated and also the result of the query. By default the result is set to ‘object[]’ which is the case for a projection query. If the stored procedure returns records of a certain persistent type, this type can be selected from the list of mapped types.
Only types that are mapped in the table mapping are available in this list. A preview of the code that will be generated is displayed below the property grid.
This code is also generated using templates – storedProcedure.vm, for procedures that return an ‘object[]’ and – storedProcedureType.vm , for procedures that return a list of a persistent type.
|
If a stored procedure is regenerated, changes made to the existing static method are overwritten. |
Template Files
storedprocedure.vm
| C# |
Copy Code |
|
public static IQueryResult $methodName(IObjectScope scope$paramDecls)
{
IQuery query = scope.GetSqlQuery("$(sqlExpression)",null,"$(sqlParams)");
IQueryResult res = query.Execute(new object[] {$paramNames});
int a = res.Count;//Actually executes the query
return res;
} |
This template file is used to generate a static method in C# for executing a stored procedure that returns an object[]:
- $methodName – is replaced with the method name specified in the property grid.
- $paramDecls – The value of this variable is calculated by OA based on the number and type of parameters required by the stored procedure. According to the SQL type the corresponding .NET type is used as the formal parameter for the static method.
- $sqlExpression – This variable is also calculated by OA and it contains the valued required by GetSqlQuery in order to execute the procedure.
- $sqlParams – This contains SQL type for the parameter and the parameter name as it appears in the store procedure.
- $paramNames – the names of the formal parameters that are passed to the Execute method.
storedProcedureType.vm
| C# |
Copy Code |
|
public static IEnumerable<$type> $methodName(IObjectScope scope$paramDecls)
{
Query<$type> query = scope.GetSqlQuery<$type>("$(sqlExpression)","$(sqlParams)");
QueryResultEnumerable<$type> res = query.ExecuteEnumerable(new object[]{$paramNames});
return res;
} |
This template file is used to generate a static method in C# for executing a stored procedure that returns a list of Persistent type instances:
- $type – The Full name of the Persistent type that is returned.Can be specified in the property grid.
- $paramDecls – The value of this variable is calculated by OA based on the number and type of parameters required by the stored procedure. According to the SQL type the corresponding .NET type is used as the formal parameter for the static method.
- $sqlExpression – This variable is also calculated by OA and it contains the valued required by GetSqlQuery in order to execute the procedure.
- $sqlParams – This contains SQL type for the parameter and the parameter name as it appears in the store procedure.
- $paramNames – the names of the formal parameters that are passed to the Execute method.