La Capa de Almacenamiento consiste en objetos SQL Server y de base de datos. La Capa de Acceso a Datos es el código escrito por el cliente en un lenguaje como C#, VB, VB.NET, Java, PHP, etc. La Capa de Acceso a Datos se comunica con la Capa de Almacenamiento de Datos para realizar operaciones CRUD. CRUD representa un acrónimo para operaciones de base de datos de Crear, Leer (Read), Actualizar (Update) y Eliminar (Delete). La comunicación entre las dos capas podría ser en forma de sentencias SQL ad hoc como INSERT, SELECT, UPDATE y DELETE. El enfoque de procedimientos almacenados obvia estas sentencias SQL y usa sólo la sentencia EXECUTE en procedimientos almacenados.
¿Por qué CRUD?
Hay muchas razones para usar procedimientos almacenados para realizar operaciones CRUD en lugar de sentencias ad-hoc:
Desempeño
Después de la primera ejecución de un procedimiento almacenado, el plan de ejecución de procedimientos es almacenado en el caché de procedimientos de SQL Server y reutilizado para todas las invocaciones del procedimiento almacenado.
Cuando cualquier sentencia SQL es ejecutada en SQL Server, el motor relacional primero buscará a través del caché de procedimientos para verificar que un plan de ejecución existente para la sentencia SQL especificada existe y reutilizará cualquier plan existente, ahorrando el error de codificar, la optimización y los pasos de recompilación para la sentencia SQL. Si el plan de ejecución no existe, lo cual es el caso con las sentencias SQL ad-hoc, SQL Server generará un nuevo plan de ejecución para la consulta.
Desacopla el código SQL desde las otras capas de la aplicación
Removiendo las sentencias SQL desde el código de la aplicación, todo el SQL puede ser mantenido en la base de datos y nada más que las invocaciones de los procedimientos almacenados en la aplicación cliente. Usar procedimientos almacenados para encapsular el acceso a la base de datos es también una manera efectiva de disminuir el acoplamiento de bases datos.
Previene ataques de inyección SQL
Usar procedimientos almacenados en lugar de concatenación de cadenas para construir consultas dinámicas desde los datos de entrada del usuario para todas las sentencias SQL reduce la posibilidad de ataques de inyección SQL porque todo lo colocado en un parámetro está entre comillas en el proceso.
Procedimientos almacenados CRUD
Hay algunas convenciones comunes de nombramiento para diferencia entre procedimientos CRUD de otros procedimientos almacenados en la base de datos incluyendo:
- El prefijo debería diferir del prefijo usado para otros procedimientos almacenados definidos por el usuario.
- Usar el nombre de la tabla antes del prefijo asegura que los procedimientos CRUD para la misma tabla estén agrupados juntos
- El nombre del procedimiento debería terminar con el nombre de la operación CRUD que lo implementa
Para actualizar el esquema de base de datos después de añadir procedimientos CRUD, primero identifique la entidad de base de datos para la cual los métodos CRUD serán implementados. Usaremos la tabla Customer para mostrar la implementación de las operaciones CRUD usando procedimientos almacenados:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[Customer]( [CustomerID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [FirstName] [varchar](20) NULL, [LastName] [varchar](20) NULL, [Email] [varchar](20) NULL, [PhoneNumber] [int] NULL ) |
Las operaciones CRUD son implementadas por cuatro procedimientos almacenados:
Procedimientos CREATE
El procedimiento Create realiza la sentencia INSERT, la cual creará un nuevo registro. Tiene un parámetro para cada columna en la tabla:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
IF OBJECT_ID('cusp_CustomerCreate') IS NOT NULL BEGIN DROP PROC usp_CustomerCreate END GO CREATE PROCEDURE usp_CustomerCreate @FirstName varchar(20), @LastName varchar(20), @Email varchar(20), @PhoneNumber int AS BEGIN INSERT INTO Customer ( FirstName, LastName, Email, PhoneNumber) VALUES ( @FirstName, @LastName, @Email, @PhoneNumber) SET @CustomerID = SCOPE_IDENTITY() SELECT FirstName = @FirstName, LastName = @LastName, Email = @Email, PhoneNumber =@PhoneNumber FROM Customer WHERE CustomerID = @CustomerID END |
La línea SET @CustomerID = SCOPE_IDENTITY() captura el valor de identidad. La función SCOPE_IDENTITY() retorna el último valor de identidad insertado en una columna de identidad en el mismo alcance (un procedimiento almacenado, un desencadenador, una función o lote). Dos sentencias están en el mismo alcance si ellas están en el mismo procedimiento almacenado, función o lote.
Procedimientos READ
El procedimiento Leer (Read) lee los registros de la tabla basado en la llave primaria especificada en el parámetro de entrada:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
IF OBJECT_ID('cusp_CustomerRead') IS NOT NULL BEGIN DROP PROC cusp_CustomerRead END GO CREATE PROC cusp_CustomerRead @CustomerID int AS BEGIN SELECT CustomerID, FirstName, LastName, Email, PhoneNumber FROM Customer WHERE (CustomerID = @CustomerID) END GO |
Procedimientos UPDATE
El procedimiento Actualizar (Update) realiza una sentencia UPDATE en la tabla basado en la llave primaria para un registro especificado en la cláusula WHERE de la sentencia. Al igual que el procedimiento Crear, tiene un parámetro para cada columna en la tabla:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
IF OBJECT_ID('cusp_CustomerUpdate') IS NOT NULL BEGIN DROP PROC cusp_CustomerUpdate END GO CREATE PROC cusp_CustomerUpdate @CustomerID int, @FirstName varchar(20), @LastName varchar(20), @Email varchar(20), @PhoneNumber int AS BEGIN UPDATE Customer SET FirstName = @FirstName, LastName = @LastName, Email = @Email, PhoneNumber = @PhoneNumber WHERE CustomerID = @CustomerID END GO |
Procedimientos DELETE
El procedimiento Eliminar (Delete) elimina una fila especificada en la cláusula WHERE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
IF OBJECT_ID('cusp_CustomerDelete') IS NOT NULL BEGIN DROP PROC cusp_CustomerDelete END GO CREATE PROC cusp_CustomerDelete @CustomerID int AS BEGIN DELETE FROM Customer WHERE CustomerID = @CustomerID END GO |
Generando procedimientos CRUD usando Visual Studio
Haga clic derecho en la carpeta de la aplicación en el panel Solution Explorer y elija la opción Add->New Item:
Seleccione DataSet en la ventana Add New Item:
Haga clic derecho en la ventana abierta y elija la opción Add->TableAdapter:
En TableAdapter Configuration Wizard elija la conexión de datos y en la siguiente ventana elija la opción Create new stored procedures:
En la siguiente ventana ingrese una sentencia SELECT para el procedimiento almacenado Read:
En Advanced Options seleccione Generate Insert, Update y Delete statement, y las opciones Use optimistic concurrency y Refresh the data table:
Las opciones Generate Insert, Update y Delete statement generan sentencias Insert, Update y Delete basadas en la sentencia Select especificada.
La opción Use optimistic concurrency no cloquea un registro cuando se lo está leyendo porque no hay bloqueo de registros y por tanto ningún requerimiento de recursos de servidor adicionales usando concurrencia optimista pueden mejorar el desempeño. También, las conexiones al servidor pueden servir a un gran número de clientes en menos tiempo porque una conexión persistente al servidor de base de datos no es requerido para mantener bloqueos de registros.
En la siguiente ventana, nombre los procedimientos almacenados y haga clic en el botón Finish:
Use el botón Preview SQL Script para previsualizar el script y úselo para sus propios procedimientos:
Aquí está el código para el procedimiento cusp_CustomerCreate abierto en SSMS:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
/****** Object: StoredProcedure [dbo].[cusp_CustomerCreate] Script Date: 26-Mar-14 7:17:03 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[cusp_CustomerCreate] ( @FirstName varchar(20), @LastName varchar(20), @Email varchar(20), @PhoneNumber int ) AS SET NOCOUNT OFF; INSERT INTO [Customer] ([FirstName], [LastName], [Email], [PhoneNumber]) VALUES (@FirstName, @LastName, @Email, @PhoneNumber); SELECT CustomerID, FirstName, LastName, Email, PhoneNumber FROM Customer WHERE (CustomerID = SCOPE_IDENTITY()) GO |
Recursos útiles
- Microsoft Application Architecture Guide – Design Fundamentals
- CRUD, Only When You Can Afford It
- My stored procedure “best practices” checklist
Traductor: Daniel Calbimonte
View all posts by Milica Medic
- Particionamiento de tablas de bases de datos en SQL Server - December 4, 2015
- Creandousando procedimientos almacenados CRUD - December 4, 2015