PRESENTACION

Un procedimiento almacenado de SQL SERVER es una colección lógica de declaraciones, es decir un conjunto de consultas empaquetadas con el fin de realizar de forma repetida una determinada tarea. El desarrollador de una aplicación ASP.NET con MVC solo tiene que escribir la declaración una vez y esto ayuda en reducir la cantidad de código y los recursos necesarios por parte del servidor.

PUNTOS IMPORTANTES

Los procedimientos almacenados son de los objetos que más se utilizan en una base de datos, en este post voy a explicar las ventajas que aporta utilizarlos a la hora de desarrollar una aplicación.

Ventajas para el desarrollador

Los desarrolladores no tienen por qué escribir las mismas consultas varias veces, y por lo tanto esto supone un ahorro de tiempo importante.

Reutilizabilidad

Si por ejemplo el desarrollador necesita introducir una modificación, esto lo puede hacer desde el procedimiento almacenado sin volver a tocar la consulta en multitud de sitios.

Segregación

A veces las empresas exigen mantener la parte lógica del negocio separada de la aplicación principal, de esta forma no es necesario recompilar toda la aplicación después de realizar cambios sobre la lógica de recuperación de datos.

Optimización

La compilación de los procedimientos almacenados se hace una sola vez a la hora de crearlos. De esta forma, se aumenta la velocidad de ejecución porque el proceso de compilación no se vuelve a ejecutar salvo si se introduzcan modificaciones sobre el propio procedimiento.

Seguridad

Utilizar procedimientos almacenados evita que la aplicación tenga acceso directo a las tablas, esto ofrece una capa de seguridad que evita manipular los registros dentro de estas tablas usando inyecciones de SQL.

 

COMO LO VAMOS A HACER

Este post se dedica a explicar los procedimientos almacenados de selección, y por lo tanto vamos a ver los dos posibles casos que podríamos encontrar.

Procedimiento almacenado de selección sin parámetros de entrada

En la aplicación MVC de mensajería que vamos a crear necesitaremos un procedimiento almacenado para recuperar, desde la base de datos, todas las posibles situaciones en las que se puede encontrar un pedido, nominalmente el estado.

       CREATE PROCEDURE LISTAR_ESTADOS AS
       SELECT DISTINCT ESTADO_ENTREGA FROM PEDIDOS
       GO

Podemos ejecutar este procedimiento almacenado desde Microsoft SQL Server Management Studio usando la línea de comando EXECUTE LISTAR_ESTADOS o simplemente EXEC LISTAR_ESTADOS para conseguir una muestra de los datos que vamos a recuperar desde nuestra aplicación.

 

Procedimiento almacenado de selección con parámetros de entrada

En la aplicación MVC necesitaremos también un procedimiento almacenado para recuperar los pedidos que tengan un determinado estado esto es, el estado que elija el usuario.

El símbolo “@” se usa como prefijo para todos los parámetros dentro de un procedimiento almacenado.

       CREATE PROCEDURE PEDIDOS_ESTADO (@ESTADO nvarchar(40)) AS
       SELECT P.ID_PEDIDO, P.ESTADO_ENTREGA, P.FECHA_CREACION, P.DESTINATARIO, P.DIRECCION, P.CODIGO_POSTAL, P.TELEFONO, M.APELLIDOS FROM PEDIDOS P
       INNER JOIN MENSAJEROS M ON P.ID_MENSAJERO = M.ID_MENSAJERO
       WHERE P.ESTADO_ENTREGA = @ESTADO
       GO

Este procedimiento almacenado acepta un parámetro de entrada llamado ESTADO, tenemos que pasarle siempre este parámetro para poder ejecutarlo.

 

Modificar o eliminar un procedimiento almacenado

Podemos usar el comando ALTER PROCEDURE para modificar un procedimiento almacenado ya creado, así como el comando DROP PROCEDURE si lo que deseamos es eliminarlo.

 

DESARROLLO

En este post vamos a crear una aplicación de mensajería que nos muestre los pedidos con un estado de entrega determinado. Vamos a trabajar sobre dos tablas: Pedidos y Mensajeros.

  • La página tiene que tener un desplegable que recupera todos los posibles estados.
  • Una vez que el usuario elija un estado y pulse el botón, le tiene que salir una lista con los pedidos en este estado.

Lo primero será crearnos dos tablas, el tipo de relación entre las dos es de una-a-muchos.

CREATE TABLE MENSAJEROS(
       ID_MENSAJERO INT PRIMARY KEY,
       APELLIDOS NVARCHAR(40),
       TELEFONO NVARCHAR(9)
       )

 

CREATE TABLE PEDIDOS(
       ID_PEDIDO INT PRIMARY KEY,
       ESTADO_ENTREGA NVARCHAR(40),
       FECHA_CREACION DATETIME,
       DESTINATARIO NVARCHAR(40),
       DIRECCION NVARCHAR(255),
       CODIGO_POSTAL NVARCHAR(5),
       TELEFONO NVARCHAR(9),
       ID_MENSAJERO INT FOREIGN KEY REFERENCES MENSAJEROS(ID_MENSAJERO)
       )

Luego vamos a crear dos procedimientos, uno que no admita parámetros de entrada y otro que sí.

       CREATE PROCEDURE LISTAR_ESTADOS AS
       SELECT DISTINCT ESTADO_ENTREGA FROM PEDIDOS
       GO

       CREATE PROCEDURE PEDIDOS_ESTADO (@ESTADO nvarchar(40)) AS
       SELECT P.ID_PEDIDO, P.ESTADO_ENTREGA, P.FECHA_CREACION, P.DESTINATARIO, P.DIRECCION, P.CODIGO_POSTAL, P.TELEFONO, M.APELLIDOS FROM PEDIDOS P
       INNER JOIN MENSAJEROS M ON P.ID_MENSAJERO = M.ID_MENSAJERO
       WHERE P.ESTADO_ENTREGA = @ESTADO
       GO

En nuestro proyecto creamos un contexto haciendo clic sobre la carpeta Models>Add>Class y elegir LINQ TO SQL este nuevo objeto lo llamamos Mensajeria:

Arrastramos los dos procedimientos desde Server Explorer hacia nuestro contexto de datos recién creado Mensajeria.

En View creamos una nueva zona que se llama Procedimientos, en la que crearemos una vista llamada EstadoPedidos.cshtml

En la carpeta Models creamos el modelo ProcedimientosModels.cs y le metemos dentro el código para recuperar el resultado de la ejecución de los procedimientos almacenados:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ResultadoProcedimientos.Models
{
    public class ProcedimientosModels
    {
        MensajeriaDataContext contexto;
        //CREAMOS EL CONSTRUCTOR DENTRO DE LA CLASE
        public ProcedimientosModels()
        {
            this.contexto = new MensajeriaDataContext();
        }

        public List<LISTAR_ESTADOSResult> GetEstados()
        {
            var consulta = this.contexto.LISTAR_ESTADOS().ToList();
            if (consulta.Count() == 0)
            {
                return null;
            }
            else
            {
                return consulta;
            }
        }

        public List<PEDIDOS_ESTADOResult> PedidosEstado(String Estado)
        {
            var consulta = this.contexto.PEDIDOS_ESTADO(Estado).ToList();
            if (consulta.Count() == 0)
            {
                return null;
            }
            else
            {
                return consulta;
            }
        }
    }
}

 

En la carpeta Controllers creamos el controlador ProcedimientosController.cs y le metemos dentro el código para pasar los datos desde el modelo hacia la vista, en el ViewBag pasaremos a la vista una lista con todos los posibles estados mientras que los pedidos los vamos a enviar en un modelo después de recibir un Post de la misma vista.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ResultadoProcedimientos.Models;

namespace ResultadoProcedimientos.Controllers
{
    public class ProcedimientosController : Controller
    {
        ProcedimientosModels model;
        public ProcedimientosController()
        {
            this.model = new ProcedimientosModels();
        }
        // GET: Procedimientos
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult EstadoPedidos()
        {
            List<LISTAR_ESTADOSResult> listaestados = this.model.GetEstados();
            ViewBag.Estados = listaestados;
            return View();
        }

        [HttpPost]
        public ActionResult EstadoPedidos(String Estado)
        {
            List<LISTAR_ESTADOSResult> listaestados = this.model.GetEstados();
            ViewBag.Estados = listaestados;
            List<PEDIDOS_ESTADOResult> pedidosestado = this.model.PedidosEstado(Estado);
            return View(pedidosestado);
        }
    }
}

Finalmente añadimos código en la vista EstadoPedidos.cshtml para visualizar los datos que hemos recuperado tras la ejecución de los procedimientos almacenados:

 

@{
    ViewBag.Title = "EstadoPedidos";
}

@model List<ResultadoProcedimientos.Models.PEDIDOS_ESTADOResult>

@{
    //LENGUAJE C#
    List<ResultadoProcedimientos.Models.LISTAR_ESTADOSResult> listaestados = ViewBag.Estados;
}

<h2>Control de pedidos</h2>

<form method="post">
    <div>
        <label>Elige el estado para consultar pedidos: </label>
        <select name="Estado" class="form-control">
            @foreach (var e in listaestados)
            {
                <option value="@e.ESTADO_ENTREGA">@e.ESTADO_ENTREGA</option>
            }
        </select>
        <button type="submit" class="btn-success">
            Mostrar pedidos
        </button>
    </div>
</form>

<hr />
@if (Model != null)
{
    <table border="1">
        <tr>
            <th>ID pedido</th>
            <th>Estado de entrega</th>
            <th>Fecha de creación</th>
            <th>Nombre destinatario</th>
            <th>Dirección</th>
            <th>Código postal</th>
            <th>Teléfono</th>
            <th>Mensajero</th>
        </tr>

        @foreach (var p in Model)
        {
            <tr>
                <td>@p.ID_PEDIDO</td>
                <td>@p.ESTADO_ENTREGA</td>
                <td>@p.FECHA_CREACION</td>
                <td>@p.DESTINATARIO</td>
                <td>@p.DIRECCION</td>
                <td>@p.CODIGO_POSTAL</td>
                <td>@p.TELEFONO</td>
                <td>@p.APELLIDOS</td>
            </tr>
        }
    </table>
}

FINALIZACION

Por las ventajas que ofrecen, los procedimientos almacenados son muy populares y se usan frecuentemente por los desarrolladores. Sin embargo, habrá que mantener un registro de todos los procedimientos almacenados que tengan relación entre sí, es posible que a la hora de realizar cambios sobre uno te encuentras con la necesidad de cambiar a otros también.

En este Post hemos hablado de los procedimientos almacenados de selección ¿y si creamos procedimientos almacenados para realizar INSERT, DELETE o UPDATE?

¿Qué utilizad nos pueden aportar procedimientos con parámetros de salida OUTPUT y cuáles son sus ventajas?

Un saludo,

 

Descripción: Los procedimientos almacenados son de los objetos más fiables en un sistema de BBDD, esta materia intenta explicar cómo usarlos con MVC.
Autor: Mohamed El Haloui
Curso: Microsoft MCSD Web Applications + SharePoint Apps
Centro: Tajamar
Año académico: 2016-2017
https://www.linkedin.com/in/melhaloui