Update masivo desde ASP.NET a SQL sever con LINQ to object.

Hola, alguna vez me encontré con este problema y he visto que no he sido el único en encontrarme en esta situación:

Gridview en ASP.NET con múltiples registros que se deben actualizar cuando hago clic un botón.

Se puede hacer fácilmente realizando un recorrido por todas las filas (gridviewrow) del gridview y creando una sentencia SQL por cada fila.

Aunque esta es una solución, no presenta el mejor rendimiento, teniendo en cuenta que si son 20 filas se realizarían 20 conexiones a la base de datos, y si hay 20 usuarios conectados al sistema, serías 400 conexiones (:S) y así sucesivamente…

A continuacón mediante un ejemplo lo mas ilustrado posible voy a proponer una solución a esto de la siguiente manera:

1. Con Linq to object creo un XML con la información de todo el gridview.
2. Creo un procedimiento almacenado en SQL server 2005 (aplica para 2008 y espero que para subsiguientes)  que:
   1. Reciba como parámetro el XML
   2. actualice los registros existentes
   3. agregue los registros nuevos

Para ello voy a realizar la el ejercicio con pequeño inventario de lo que hay en mi cocina.

Creación de la base de datos

una base de datos que llamaré inventario con una tabla de productos en la cual tengo tres campos: idproducto, producto, cantidad.

 

El código para crear la bd y la tabla:

USE [inventario]
GO
/****** Object:  Table [dbo].[Productos]    Script Date: 02/15/2010 16:00:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Productos](
    [idProducto] [int] IDENTITY(1,1) NOT NULL,
    [Producto] [varchar](20) NOT NULL,
    [Cantidad] [float] NOT NULL CONSTRAINT [DF_Productos_Cantidad]  DEFAULT ((0)),
CONSTRAINT [PK_Productos] PRIMARY KEY CLUSTERED
(
    [idProducto] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Ahora, voy a agregar a manera de ejemplo 10 productos con su respectiva cantidad (aprovecho que hice mercado el fin de semana :D ):

 

Creación del sitio web

ahora, voy a crear un nuevo sitio web con VS 2008 en el cual voy a leer la información de mi inventario:

1. Creo un gridview con los tres campos (Nótese que el campo cantidad es un TemplateField en el cual tengo en el ItemTemplate un textbox donde voy mantener la cantidad) :

<asp:GridView runat="server" ID="GrdInventario" AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="IdProducto" HeaderText="IdProducto" />
        <asp:BoundField DataField="Producto" HeaderText="Producto" />
        <asp:TemplateField HeaderText="Cantidad">
            <ItemTemplate>
                <asp:TextBox ID="txtCantidad" runat="server" Text='<%# Bind("Cantidad") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

2. Agrego un botón para actualizar:

<br />
<asp:Button ID="BtnActualizar" runat="server" Text="Actualizar" />

3.En el pageload mediante un datareader lleno el gridview:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            'Utilizar el connectionString del webconfig:
            Dim cnnStr As String = ConfigurationManager.ConnectionStrings("InventarioConn").ConnectionString
            'usar una conexion a la base de datos
            Using cnn As New SqlConnection(cnnStr)
                cnn.Open()
                Try
                    Using cmd As SqlCommand = New SqlCommand("select idproducto, producto, cantidad from productos", cnn)
                        cmd.CommandType = CommandType.Text
                        Using dr As SqlDataReader = cmd.ExecuteReader()
                            GrdInventario.DataSource = dr
                            GrdInventario.DataBind()
                        End Using
                    End Using
                Catch ex As Exception
                    Throw ex
                Finally
                    cnn.Close()
                End Try
            End Using
        End If
    End Sub

4. No olvidar utilizar los namespace de System.Data y System.Data.SqlClient

Imports System.Data.SqlClient
Imports System.Data

Ahora, después de aplicar autoformato al gridview y un título,  al ejecutarse deberia verse algo asi:

Nótese:

• El botón actualizar todavía no hace nada.
• La columna cantidad muestra las celdas como textbox

Procedimiento almacenado que actualiza la tabla

Hasta aquí todo normal, ahora voy a crear un procedimiento almacenado que reciba como parámetro de tipo xml.

El XML que debe recibir el procedimiento almacenado es así:

<productos>
    <producto id="12" cantidad="5"></producto>
    <producto id="13" cantidad="12"></producto>
    <producto id="14" cantidad="15"></producto>
</productos>

El procedimiento será asi:

CREATE PROC [usp_ActualizarInventario]
    @Variables xml
AS
--actualizar los que esten en la tabla

UPDATE [Productos]
    SET 
        [Cantidad] = T.Item.value('@cantidad',  'float')
    FROM
        [productos] p
        join    @Variables.nodes('productos/producto') AS T(Item) on (
                p.idProducto=T.Item.value('@idproducto',  'int')
                )
                    ;

Nótese:

• Se realiza un update con un join donde la tabla productos se actualiza con la información del XML para cada registro donde el idproducto de la tabla corresponda al idproducto del XML
• En el mismo procedimiento almacenado se puede crear una sentencia Insert para los registros nuevos (por el momento solo lo realizaré con el update)

Crear el XML en la página ASP.NET utilizando Linq to object

Ahora de regreso a nuestro proyecto vamos a invocar el procedimiento almacenado creado para actualizar la tabla con lo que modifiquemos en la página, pero primero vamos a crear el contenido que se debe ir en el parámetro @Variables del procedimiento almacenado:

1. En el evento clic del botón Actualizar vamos a crear una variable que nos almacene el xml (no olvide utilizar el namespace System.Linq)

Dim xmlVariables As XElement
xmlVariables = <productos>
                   <%= From fila As GridViewRow In GrdInventario.Rows _
                       Select <producto id=<%= fila.Cells(0).Text %>
                                  cantidad=<%= CType(fila.FindControl("txtCantidad"), TextBox).Text %>>
                              </producto> %>
               </productos>

Nótese:

• Mi lenguaje de programación es Visual Basic
• Aprovechando lo anterior se puede crear la estructura xml directamente digitando esta como se desea.
• Con linq to object extraje el id del producto y la cantidad respectiva de cada producto
• Utilizando el Debuger del Visual Studio, podemos observar el contenido de la variable (con la estructura que debe recibir el parámetro del SQL Server):

 

2. ahora que ya tenemos en una variable el xml que vamos a enviar al procedimiento almacenado simplemente lo ejecutamos:

Dim CadenaConexion As String = ConfigurationManager.ConnectionStrings("InventarioConn").ConnectionString
Using cnn As New SqlConnection(CadenaConexion)
    cnn.Open()
    Try
        Using cmd As New SqlCommand("usp_ActualizarInventario", cnn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Variables", xmlVariables.ToString())
            Dim result As Integer = cmd.ExecuteNonQuery()
            Response.Write("Registros Actualizados: " & result)
        End Using
    Catch ex As Exception
        Throw ex
    Finally
        cnn.Close()
    End Try
End Using

3. Ejecutamos y listo: Viajes a la base de datos 1, registros actualizados 15.

Saludos

 

ROBINSON MOSCOSO PEREZ