The CRUD Operations chapter is focused on querying the OpenAccess 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 OpenAccess Domain Model using LINQ. By default, OpenAccess ORM 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 OpenAccess ORM utilizes stored procedures and database functions. You will learn how to override the default behavior of OpenAccess ORM 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 OpenAccess ORM 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
OpenAccess ORM 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, OpenAccess ORM 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 OpenAccess Visual Designer to map the insert, update, and delete operations of a persistent type to stored procedures.
Low Level (ADO) API
Besides querying an OpenAccess Domain Model by using standard LINQ expressions, there is one more way you can query the domain model, and that is through the OpenAccess 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 OpenAccess ADO API is perfect for you. The OpenAccess 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 OpenAccess ADO API.