How to update data using stored procedure from the sql server.
In the following example we will use a stored procedure that is on the sql server and execute it using open access. For the purpose of this example we will need an SQL Procedure named "MyProcedure". The procedure should contain this code:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[MyProcedure] AS
UPDATE Categories SET CategoryName = 'CustomName' WHERE CategoryName = 'SeaFood'
Assuming we have the SQL stored procedure we can now use the reverse mapping wizard to create the static method that will execute the procedure. To create the method you need to open the reverse mapping wizard for the class library containing your mapped classes and switch to advanced view tab. In the advanced view tab there is a node called Stored Procedures, which contains all stored procedures on the sql server.
||If the stored procedures are missing you will need to use the merge schema changes tool to merge all the procedures from your sql server to your library class.|
When you click on a stored procedure in the stored procedures node you have several options available:
- Generate method - This property specifies whether this procedure will be created as a static method or not.
- Method name - This property specifies the name of the output static method that will execute the stored procedure.
- Result - From this property you can set the result that will be returned from the static method. 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.|
Once you select all the stored procedures you want to generate click Generate and Save config. All the procedures that have "Generate method" property set to true will be generated as a static methods and can be found in the StoredProcedures class in your class library.
Using auto generated Stored Procedures:
You can let Telerik OpenAccess ORM to auto generate some stored procedures for the most common scenarios(update,delete,insert). You can achieve this goal following those steps:
||For the purpose of this example we will auto generate stored procedure for inserting.|
1. Open the reverse map wizard and click on the Advanced View (TreeView) tab.
2. Choose the class for which you want to create a stored procedure.
3. Mark the Use stored procedure check box and on the insert procedure from the drop down list instead of Use Dynamic SQL choose Create Stored Procedures.
4. Click Generate and Save config.
5. Check if the Update Database property of your class library is set to true and if so rebuild your project.
||When you rebuild your project the stored procedure will be created on your sql server. |
6. Open the reverse mapping wizard again and do a merge to reflect the changes in the sql server. Merge the new procedures.
When you do so you will be able to find those procedures in the Stored Procedures node in the Advanced View(TreeView) tab. From this point forward you can proceed the same way as explained in the How to update data using stored procedure from the sql server.
Defining stored procedures using the app.config
For the purpose of this scenario we will use the app.config to generate a stored procedure that is not existing in the SQL server. To do so we will firstly need to enable the usage of stored procedures.This is done by specifying it in the backendconfiguration section in the app.config.
Your backendconfiruration should look something like this:
<backendconfiguration id="mssqlConfiguration" backend="mssql">
To define a stored procedure for a specific class we need to define it in the class itself in the app.config file. For the purpose of the following example we will define a Stored procedure for the Category class which will delete entries based on the category name passed as parameter.
This code needs to be written in between the opening and closing tag of the Category class.
<delete name="deleteCategoryMod" create="true">
<parameter name="CategoryName" field="categoryName" />
<code>DELETE FROM [Categories] WHERE [categoryName] = @CategoryName</code>
At this point you need to rebuild your project and start the reverse mapping wizard. Merge the changes so that your new stored procedures is added. The new stored procedure "deleteCategoryMod" should be in the Stored Procedure nod. Select it and change the Generate method property method to true. Click Generate and Save config. The new stored procedure is generated and its located in the StoredProcedure class in the class library. You can access it in your project with the following code