La Escuela del Programador

En este artículo haré un ejemplo de como utilizar un procedimiento almacenado de una base de datos existente utilizando la estrategia Code First. Para eso necesitaremos bajar Entity Framework Power Tools Beta 2 que son poderosas herramientas para trabajar con Entity Framework 5 en VS 2010 y VS 2012

Una vez que bajamos las Power Tools, eso agregará la posibilidad de hacer ingeniería reversa contra una base de datos existente y generará todas las clases que mapearán con las tablas de la base de datos y la clase contextual que hereda de DBContext, de manera que podremos utilizar la estrategia CodeFirst contra una base datos ya existente

Para el ejemplo que haremos a continuación utilizaremos la vieja y conocida base de datos Northwind y en particular usaremos el procedimiento almacenado que viene con dicha base de datos llamado CustOrdersDetails Para saber que hace ese procedimiento almacenado, aquí les muestro el script que crea dicho stored procedure:

CREATE PROCEDURE [dbo].[CustOrdersDetail] @OrderID int
AS
SELECT ProductName,
    UnitPrice=ROUND(Od.UnitPrice, 2),
    Quantity,
    Discount=CONVERT(int, Discount * 100), 
    ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * 
                                 Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

Como indica su nombre este procedimiento da los detalles de una orden dado su Id. O sea devuelve cada ítem que ha sido vendido en dicha orden: Muestra el nombre del producto, el precio unitario de ese producto, la cantidad de ítems vendidos, el descuento que se ha aplicado (si se aplica), y finalmente un campo calculado que es el monto total gastado, luego de aplicar la formula "precioUnitario * cantidad - descuento"

Haremos un nuevo proyecto de consola en C#, como vemos en la figura 1:

Figura 1 - Creamos un nuevo proyecto de consola con Visual Studio y lo llamamos EFCodeFirstCallStoredProcedures

Utilizando NuGet agregamos el paquete Entity Framework 5.0 y una vez instaladas las Entity Framework Power Tools Beta 2, esto agregará un nuevo menú contextual al hacer click con el botón derecho del mouse sobre el nombre del proyecto: Entity Framework y dentro aparecerá la opción Reverse Engineer Code First, como se vé en la figura 2:

Figura 2 - Las EF Power Tools agregan la posibilidad de crear el modelo conceptual a partir de una base de datos existente, utilizando ingeniería reversa

Al pulsar la opción de ingeniería reversa, nos saldrá una ventana, para establecer la conexión a la base de datos ya existente, como se ve en la figura 3:

Figura 3 - Ventana Propiedades de la Conexión, para crear las clases del modelo conceptual a partir de ingeniería reversa

Luego de unos instantes, y a través de ingeniería reversa, se crearán las clases a partir de cada tabla y vistas de la base de dotos Northwind, como se ve en la figura 4:

Figura 4 - Ventana Propiedades de la Conexión, para crear las clases del modelo conceptual a partir de ingeniería reversa

Es importante destacar que EF utilizando la API de DBContext y Code First no importa automáticamente los procedimientos almacenados y es por eso que deberemos programar todo lo necesario para poder utilizar los procedimientos almacenados de una base de datos

Como decía al comienzo de este artículo, para este ejemplo usaremos el procedimiento almacenado llamado CustOrdersDetails

Lo primero que debemos crear es una clase que represente la entidad que devuelve el procedimiento almacenado, para ello agregamos una nueva clase al proyecto y la llamamos: CustOrdersDetail_Result, agregándolo el siguiente código:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCodeFirstCallStoredProcedures
{
    public class CustOrdersDetail_Result
    {
        public string ProductName { get; set; }
        public decimal UnitPrice { get; set; }
        public short Quantity { get; set; }
        public int Discount { get; set; }
        public decimal ExtendedPrice { get; set; }
    }
}

Es importante notar el mapeo entre los tipos de datos .NET de esta clase y los tipos de datos definidos en SQL para cada columna que devuelve el procedimiento almacenado, como queda demostrado en la siguiente tabla:

Ahora, dentro de nuestra clase contextual, que ha creado la herramienta de ingeniería reversa, llamada NorthwindContext, agregamos un método que mapee con el procedimiento almacenado CustOrdersDetail y lo llamamos GetDetailsByOrderId, como se ve a continuación:

        public IEnumerable<CustOrdersDetail_Result>
                      GetDetailsByOrderId(int id)
        {
            return this.Database.SqlQuery<CustOrdersDetail_Result>
                                    ("CustOrdersDetail @OrderID",
                new SqlParameter("@OrderID",id));
        }

Como se observa en el método de arriba, se trata de un método público que devuelve un Enumerable de elementos del tipo CustOrdersDetails_Result y que admite como parámetro el Id de la orden a buscar los detalles.

En el cuerpo del método simplemente retornamos lo que devuelve el procedimiento almacenado CustOrdersDetails, pasándole como parámetro el id de la orden pasada al método. Para hacer esto, usamos la propiedad Database de la clase DbContext (en nuestro caso la clase derivada NorthwindContext). Como el código está precisamente dentro de esa clase, hacemos referencia a ella, utilizando el operador this.

Usamos el método SqlQuery(TElement)(String, Object[]) de la clase Database, para crear una consulta SQL que devuelve elementos de un tipo genérico. En nuestro caso: SqlQuery<TElement>(String, Object[]). El tipo TElement puede ser cualquier tipo cuyas propiedades mapeen con las columnas retornadas por la consulta, o también puede ser un tipo primitivo simple.

Finalmente en nuestra clase Program.cs escribimos el siguiente código:

using EFCodeFirstCallStoredProcedures.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCodeFirstCallStoredProcedures
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Write("Ingrese el ID de una orden
                           (entre 10248 y 10625): ");
            var strOrderId = Console.ReadLine();
            var orderId = Int32.Parse(strOrderId);
            using (NorthwindContext db = new NorthwindContext())
            {
                Console.WriteLine("La orden {0}, tiene los siguientes 
                                    productos con este detalle:", orderId);
                foreach(var reg in db.GetDetailsByOrderId(orderId))
                {
                    Console.WriteLine("{0}", reg.ProductName);
                    Console.WriteLine("      {0} items de ese producto a un 
                       precio unitario de ${1:0.00}.",
                       reg.Quantity,reg.UnitPrice);
                    Console.WriteLine("      Al aplicarle un descuento del {0}%, 
                       da un total de ${1:0.00}", 
                       reg.Discount, reg.ExtendedPrice);
                    Console.WriteLine();
                }
            }
        }
    }
}

Al ejecutar este programa obtenemos la salida que se observa en la figura 5:

Figura 5 - Salida del programa, al ser ejecutado.