martes, 17 de enero de 2012

Procedimientos almacenados en Entity Framework

Para terminar una mini-serie de post sobre el modelado de entidades en Entity Framework, hoy hablaremos sobre el uso que podemos realizar de los procedimientos almacenados en nuestro modelo del dominio.

Recuerda que además de este post, también están disponibles estos otros relativos al modelado en E

Centrándonos en el procedimientos almacenados, en EF se mapearán como funciones.

Las distintas opciones que tenemos disponibles en EF para trabajar con procedimientos almacenados son:

  • Devolver datos mapeados a entidades del modelo.
  • Devolver datos mapeados a un tipo complejo del modelo.
  • Devolver datos mapeados a tipos escalares.
  • Simplemente ejecutar el procedimiento sin devolver ningún valor.
  • Comandos INSERT, UPDATE y DELETE para una entidad del modelo.

Para los 3 primeros casos, utilizaremos el cuadro de diálogo “Agregar importación de función”.

Para el último caso (gestión del CRUD de la entidad) utilizaremos la ventana de “Detalles de la asignación”.

Mapear a entidades del modelo

En este caso lo que hacemos es mapear los resultados devueltos por el procedimiento almacenado a entidades previamente existentes en nuestro modelo.

Por ejemplo, en nuestro modelo tenemos una entidad llamada “Clientes” y hemos importado desde la base de datos un procedimiento almacenado llamado “GetClientes” que devuelve una lista de clientes.

ALTER PROCEDURE [dbo].[GetClientes]

AS

    SET NOCOUNT ON

    SELECT  IdCliente ,

            Nombre ,

            Direccion

    FROM    dbo.Clientes

 

clip_image001[4]

Al importar la función podemos verla en la ventana “Explorador de modelos

clip_image002[4]

El código para utilizar nuestra nueva función es muy sencillo:

Dim ctx As New TiendaEntities

Dim q As ObjectResult(Of Clientes) = ctx.GetClientes()

For Each r In q

    Console.WriteLine(r.Nombre)

Next

Console.ReadLine()


Quizás lo único reseñable cuando devolvemos tipos de entidad es que todos los campos de la entidad tienen que estar disponibles en el resultado devuelto por la función. En caso de faltar algún campo (da igual que el campo admita nulos, no sea PK, etc.), obtendremos un error similar al siguiente (donde por ejemplo hemos omitido el campo Direccion para forzar el error)

clip_image003[4]

Mapear a tipo complejo

En este caso el resultado devuelto por la función no es un tipo de entidad del modelo sino un tipo complejo.

El tipo complejo necesario para recibir los resultados podría o no estar definido previamente en el modelo. Esto es así porque en el propio cuadro de diálogo “Agregar importación de función”, tenemos disponible un par de botones que harán el trabajo sucio por nosotros. Estos botones son “Obtener información de columna” y “Crear nuevo tipo complejo”.

clip_image004[4]

Si optamos por crear un nuevo tipo complejo desde esta ventana, el nombre del mismo será <NombreFunción>_Result. Esto no debería preocuparnos en exceso, puesto que después podemos cambiar el nombre del nuevo tipo complejo desde la ventana “Explorador del modelo”. Por ejemplo, cambiaremos el nombre propuesto por “ClientePersonalizado”.

clip_image005[4]

De nuevo, utilizar la función es un código muy simple:

Dim ctx As New TiendaEntities

Dim q As ObjectResult(Of ClientePersonalizado) = ctx.GetClientes()

For Each r In q

    Console.WriteLine(r.Nombre)

Next

Console.ReadLine()

 

Mapear a valores escalares

En este caso, asumimos que el resultado devuelto por la función es una lista de valores escalares (aunque normalmente no será una lista sino un solo valor el devuelto por la función).

Nuestro procedimiento almacenado de ejemplo es el siguiente:

CREATE PROCEDURE [dbo].[GetIdCliente]

     @Nombre NVARCHAR(50)

AS

    SET NOCOUNT ON

    SELECT  IdCliente

    FROM    dbo.Clientes

    WHERE   Nombre = @Nombre

 

clip_image006[4]

clip_image007[4]

El código para llamar a la función es el siguiente:

Dim ctx As New TiendaEntities

Dim idCliente As Nullable(Of Integer) = ctx.GetIdCliente("Cliente 1").SingleOrDefault()

If idCliente.HasValue Then

    Console.WriteLine(idCliente.Value)

End If

Console.ReadLine()

 

INSERT, UPDATE y DELETE para una entidad del modelo

En este caso vamos a delegar estas 3 operaciones en procedimientos almacenados.

Nuestros procedimientos almacenados de ejemplo son:

CREATE PROCEDURE InsertClientes

    @Nombre NVARCHAR(50) ,

    @Direccion NVARCHAR(50)

AS

    INSERT  INTO [Tienda].[dbo].[Clientes]

            ( [Nombre], [Direccion] )

    VALUES  ( @Nombre, @Direccion )

GO

 

CREATE PROCEDURE UpdateClientes

    @IdCliente INT ,

    @Nombre NVARCHAR(50) ,

    @Direccion NVARCHAR(50)

AS

    UPDATE  dbo.Clientes

    SET     Nombre = @Nombre ,

            Direccion = @Direccion

    WHERE   IdCliente = @IdCliente

GO

 

CREATE PROCEDURE DeleteClientes

     @IdCliente INT

AS

    DELETE  FROM dbo.Clientes

    WHERE   IdCliente = @IdCliente

GO


Para asignar los procedimientos almacenados a las operaciones adecuadas hay que utilizar la ventana “Detalles de la asignación”.

clip_image009[4]

Si hemos tenido el cuidado de llamar de igual forma a los parámetros y a las propiedades de la entidad, la asociación será automática, en caso contrario tocará hacerla a mano campo a campo.

Lo cierto es que en este momento ya funcionará correctamente el INSERT, UPDATE y DELETE de “Clientes”, pero… no del todo. En el caso de INSERT y asumiendo que el campo IdCliente es autonumérico, después del grabar los cambios en EF la entidad no actualizará el campo IdCliente porque no lo conoce. Para resolver esto hay que utilizar la sección “Result Column Bindings” que nos permite asignar valores devueltos por el procedimiento almacenado de vuelta a la entidad. En nuestro caso, el nuevo código del procedimiento almacenado para INSERT es el siguiente (fijarse en que en además de llevar a cabo la operación de inserción, también devolvemos el nuevo valor de identidad).

CREATE PROCEDURE [dbo].[InsertClientes]

@Nombre NVARCHAR(50),

@Direccion NVARCHAR(50)

AS

INSERT INTO [Tienda].[dbo].[Clientes]

           ([Nombre]

           ,[Direccion])

     VALUES

           (@Nombre,

           @Direccion)

 

SELECT SCOPE_IDENTITY() AS NuevoIdCliente


Y en la ventana “Detalles de la asignación” y en “Result Column Bindings” para INSERT:

clip_image011[4]

Por último, cabe mencionar que si utilizamos un procedimiento almacenado para algunas de las operaciones CRUD, el resto de operaciones CRUD también tendrán que ir por la vía de los procedimientos almacenados. Es decir, “O todo va por procedimientos o nada va por procedimientos”.

Imagina que tenemos asignados procedimientos para INSERT y UPDATE, pero no para DELETE. Obtendremos el siguiente error que nos informa de que esperaba una función para DELETE que no ha podido encontrar:

clip_image013[4]

Espero que este resumen te haya servido para entender el papel que pueden jugar los procedimientos almacenados en EF.

Un saludo!

6 comentarios:

  1. Hola felicidades por tu blog, apenas comienzo con EF y me surge una duda, estoy utilizando un procedimiento almacenado con varios Joins el cual me genera un resultado que no corresponde con ninguna entidad definida previamente, por lo tanto el conjunto de resultados lo convierto en un Tipo Complejo y lo cargo en un grid, mi duda es: ¿al modificar los datos en el grid que tiene el databind con el tipo complejo, se actualizaran los datos en la base de datos?

    Saludos. Alejandro

    ResponderEliminar
  2. Hola Alejandro:
    Yo creo que no. Básicamente porque el tipo complejo que crea el wizard para el procedimiento almacenado no tiene ningún mapeo contra las tablas de tu bd, sino contra un procedimiento almacenado, y por eso no sabe donde tiene que grabar.
    Creo (no lo he probado) que podrías crear una entidad con todos los campos de tu base de datos y mapear las propiedades de la entidad a campos de tus tablas (fíjate que puedes hacer una entidad que grabase en varias tablas). De todas formas, para que EF grabe, esa entidad (que además viene de joins) tendría que tener las PK de todas las tablas y además todos los campos que no admiten nulos.
    Esta semana intentaré hacer una prueba porque ya me has picado con el asunto ;-)
    Un saludo y gracias por comentar.

    ResponderEliminar
  3. Hola una consulta estoy realizando un procedimiento almacenado desde sql con el campo Image .. y al importarlo como Complejo ,, luego luego en el sistema me sale error , que no se puede convertir a bytes. espero una ayuda.. . espero una ayuda gracias .D
    raulpacori@gmail.com

    ResponderEliminar
  4. Exelente blog, me ayudo bastante, saludos

    ResponderEliminar
  5. Saludos, excelente explicación el único detalle fue que lo realizaste en VB.

    ResponderEliminar
  6. Muchas gracias. Excelente guía para el uso de Entity Data model con procedimientos almacenados.

    ResponderEliminar