Milica Medic

Creando usando procedimientos almacenados CRUD

December 4, 2015 by
Un diseño de aplicación N-tier típico contiene muchas capas desde el cliente (Web o Windows) a la capa de Lógica de Negocios, a la Capa de Acceso de Datos y finalmente a la Capa de Almacenamiento.

N-tier application design

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:

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:

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.

CREATE procedure

Procedimientos READ

El procedimiento Leer (Read) lee los registros de la tabla basado en la llave primaria especificada en el parámetro de entrada:

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
    

READ procedure

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:

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:

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:

Choosing the Add New Item option in the Solution Explorer pane

Seleccione DataSet en la ventana Add New Item:

Selecting DataSet from the Add New Item window

Haga clic derecho en la ventana abierta y elija la opción Add->TableAdapter:

Choosing the Add TableAdapter option

En TableAdapter Configuration Wizard elija la conexión de datos y en la siguiente ventana elija la opción Create new stored procedures:

Choosing the Create new stored procedures option

En la siguiente ventana ingrese una sentencia SELECT para el procedimiento almacenado Read:

Eentering a SELECT statement for the Read stored procedure

En Advanced Options seleccione Generate Insert, Update y Delete statement, y las opciones Use optimistic concurrency y Refresh the data table:

The Advanced Options dialog

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:

Naming the stored procedures and clicking the Finish button

Use el botón Preview SQL Script para previsualizar el script y úselo para sus propios procedimientos:

The Preview SQL Script dialog

Aquí está el código para el procedimiento cusp_CustomerCreate abierto en SSMS:

/****** 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


Milica Medic

Milica Medic

In my spare time, I love spending time with friends and family. Going to the movies is a must if there is some new epic or sci-fi movie. In the summertime, I enjoy scuba diving and reading lots of books

View all posts by Milica Medic
Milica Medic
13,766 Views