Stored Procedures and Functions

Stored Procedures and Functions - Overview

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

The CRUD Operations chapter is focused on querying the Telerik Data Access Domain Model and using domain classes to add, update, and delete data. It also provides a good background on the different methods used to query the Telerik Data Access Domain Model using LINQ. By default, Telerik Data Access performs operations directly against database tables. Given the strengths of LINQ, many developers still prefer to use stored procedures for executing database logic, such as (C)reate, (R)ead, (U)pdate and (D)elete operations.

Many applications use stored procedures or database functions to gain benefits such as security, performance and encapsulation. Database administrators need only to grant execute permissions on stored procedures. Thus a single data access entry point could be created. Store procedures may significantly reduce SQL injection attacks. For example, you could put validation code in stored procedures and limit the available actions. Another benefit of using stored procedures is the possibility for encapsulation of business logic. Complex data logic and explicit transactions could be written once in stored procedure code and executed from multiple applications. You could handle errors and concurrency violations in stored procedure code, reducing the number of round-trips from your application to the server. Also, stored procedures could be modified without requiring a re-compilation of the client application.

The topics in this section illustrate how Telerik Data Access utilizes stored procedures and database functions. You will learn how to override the default behavior of Telerik Data Access and use stored procedures for CRUD operations.

Domain Method Editor

The Domain Method Editor simplifies the setup and use of stored procedures and functions in Telerik Data Access applications. When a stored procedure or database function is included in the domain model, you could create a domain method for it. Adding a domain method allows you to call the corresponding stored procedure/function from your code. You could create a domain method that returns a single scalar value, a collection of the Persistent Types, or Complex Types, or no value. Check out the following topics, which are entirely dedicated to the Domain Method Editor.

Map CUD (Create, Update and Delete) Operations to Stored Procedures

Telerik Data Access generates a class derived from OpenAccessContext that represents the container in the conceptual model. This class exposes a SaveChanges method that triggers updates to the underlying database. By default, Telerik Data Access performs CUD operations directly against database tables. However, you have the option to use your own SQL statements for CUD, i.e. to use stored procedures. The application code you use to create, update, and delete types is the same whether you use stored procedures to update the database or not. The How to: Map CUD Operations to Stored Procedures topic describes how to use the Telerik Data Access Visual Designer to map the insert, update, and delete operations of a persistent type to stored procedures.

Low Level (ADO) API

Besides querying a Telerik Data Access Domain Model by using standard LINQ expressions, there is one more way you can query the domain model, and that is through the Telerik Data Access ADO API. Using LINQ queries is simple and easy. However, if you are concerned about the performance and comfortable writing ADO.NET like code, then the Telerik Data Access ADO API is perfect for you. The Telerik Data Access ADO API is an extensive topic and it is covered with a lot of examples in the Low Level (ADO) API section. However, in the How the Generated Methods Retrieve the Data topic you will understand how stored procedures are executed by using the Telerik Data Access ADO API.