Colabora.NET: Inserción Cabecera-Detalle con XML, ejemplo práctico

Introducción

En marzo de 2006 escribi un artículo sobre la posibilidad de insertar cabeceras-detalles utilizando XML en SQL 2000 (http://www.elguille.info/colabora/NET2006/sebaconte_UtilizacionCabeceraDetalle.htm), por ese motivo, creo imprescindible terminar de cerrar la idea con una pequeña aplicación en VB.NET 2005 a fin de poder utilizar el ejemplo.

Problemática

Simularemos un módulo de carga de pedidos para un cliente. para esto crearemos un formulario simple con los datos de cabecera : Cliente - Nro de Pedido y Fecha, todos estos datos en este caso lo haremos con texto y sin validaciones ya que el objetivo es utilizar el XML para el alta. Y en la parte inferior esta la carga de los items del pedido que se mostrarán a medida que se carguen en un listview, al confirmar la operación se insertará en una sola transacción tanto la cabecera del pedido como su detalle, para luego ser visualizado en un treeview.

Primero debemos crear los scripts de las tablas, utilizaremos solo 2, pedido e itempedido.

CREATE TABLE [dbo].[Itempedido] (
    [IdItempedido] [int] IDENTITY (1, 1) NOT NULL ,
    [IdPedido] [int] NOT NULL ,
    [articulo] [varchar] (255) NULL ,
    [cantidad] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Pedido] (
    [IdPedido] [int] IDENTITY (1, 1) NOT NULL ,
    [Cliente] [char] (50) NULL ,
    [NroPedido] [char] (50) NULL ,
    [fecha] [datetime] NULL
) ON [PRIMARY]
GO

Luego en base a lo explicado en el artículo de marzo, crearemos un stored procedure que reciba como parámetro el XML para dar de alta ítems de pedidos.

El XML poseerá el siguiente formato:

<?xml version="1.0"?>
<ROOT>
<Pedido Cliente="Nombre Cliente" Numero="Numero Pedido(numérico)"
	Fecha="fecha Pedido Formato MM/DD/YYYY">
<ItemPedido Articulo="articulo 1" Cantidad="1"/>
 <ItemPedido Articulo="articulo 2" Cantidad="2"/> <Pedido>
<ROOT>"

Los TAGS que poseemos para procesar son los siguientes

"<?xml version="1.0"?> : Dentro de este TAG definimos la versión y luego veremos la codificación

<ROOT> la cabecera del XML

<Pedido> Contiene cada cabecera de los pedidos

<ItemPedido> Contiene el detalle de cada pedido

En base al formato del XML crearemos el siguiente stored procedure.

CREATE Procedure AltaCabeceraDetalleXML
@XML text
as
DECLARE @hDoc int
Begin transaction/*Preparamos el document XML en base al texto XML*/
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
Declare @idpedido int
/*Aqui insertamos en la tabla pedido desde el XML leyendo el TAG pedido*/
Insert into Pedido
SELECT * FROM OPENXML(@hDoc, N'ROOT/Pedido'/) with (Cliente char(50) )
/*Aqui rescatamos el id asignado al pedido para utilizarlo en los detalles */
Select @idPedido =@@scope_identity
If @@error <>0
Rollback Tran
Else
/*En esta parte insertamos los detalles desde el XML, e incluimos la variable @idPedido para
insertar en el campo impedido*/
Insert into ItemPedido
SELECT @idPedido,* FROM OPENXML(@hDoc, N'ROOT/Pedido/ItemPedido'/) 
with ( Articulo varchar(255) , Cantidad int )
/*Removemos la representacion interna del documento XML*/
EXEC sp_xml_removedocument @hDoc
If @@error <>0
Begin
Rollback Tran
Return 55000
End
Else
Commit Tran
GO

Con estos elementos poseemos la estructura necesaria para realizar las inserciones Cabecera Detalle transaccionada, nos resta realizar una aplicación que genere el string XML y lo envíe como parámetro al sp antes creado.

Restricciones del XML

Los caracteres: >, < , " , ' , & , deben reemplazarse al generar el string XML según la siguiente tabla:

Caracter Reemplazo en el string

> ">"

< "<"

& "&"

' "'"

" "&quote;"

Así si nuestro XML contiene alguno de estos caracteres antes de enviarlo al stored deben ser reemplazados. Luego en el código observaremos como se resuelven los acentos y caracteres mas allá del código 127 que es el default. Debido a esto se puede implementar o un reemplazo de todos los caracteres cuyo código sea mayor a 127 o bien cambiar la codificación de envío del XML en el tag <?xml version="1.0"?> agregando lo siguiente encoding=" iso-8859-1" con lo que el tag quedaría <?xml version="1.0" encoding=" iso-8859-1"?>. La función ArmarXML del código de ejemplo es clara y abarca distintas situaciones. También debemos recordar que el XML es case sensitive es decir diferencia minúsculas de mayúsculas.

Aplicación para utilizar

Para clarificar el concepto desarrolle la siguiente mini aplicación, en realidad se trata de un form que nos permite ingresar para la cabecera:

una fecha
un nombre de cliente
un numero de Pedido

Y para el detalle 2 campos (Articulo y cantidad) que alimentarán un listview, luego de completar los datos presionamos Aceptar y realiza la inserción para mostrarla en el treeview.

También he agregado 4 radio buttons con el objetivo de mostrar como se comporta el XML dependiendo de la codificación que elijamos y si utilizamos el reemplazo de caracteres especiales o la codificación ISO-8859-1.

A continuación les presento todo el código explicando mas que nada la función ArmarXML la cual es llamada por el evento click del botón Aceptar, que en base a la opción de codificación realizará los reemplazos necesarios.

Private Function ArmarXML(ByVal Parametro As Integer) As String

    Dim PrimerCentro As Boolean
    Dim strXml As String
    Dim Contador As Integer
    Dim items As Integer
    Dim i As Integer
    Dim d As Integer
    Dim Cabecera As String
    Dim icontador As Integer
    icontador = 0
    Contador = 1
    items = 1
    strXml = ""
    Cabecera = ""
    'Validaciones
'ESTOS CARACTERES SIEMPRE HAY QUE REEMPLAZARLOS
txtcliente.Text = Replace(txtcliente.Text, "'", "&apos;")
txtcliente.Text = Replace(txtcliente.Text, Chr(34), "&quot")
txtcliente.Text = Replace(txtcliente.Text, ">", "&gt;")
txtcliente.Text = Replace(txtcliente.Text, "<", "&lt;")
txtcliente.Text = Replace(txtcliente.Text, "&", "&amp;")

For i = 0 To ListView1.Items.Count - 1
    For d = 0 To 1
        ListView1.Items(i).SubItems(d).Text = _
		Replace(ListView1.Items(i).SubItems(d).Text, "'", "&apos;")
        ListView1.Items(i).SubItems(d).Text = _
		Replace(ListView1.Items(i).SubItems(d).Text, Chr(34), "&quot")
        ListView1.Items(i).SubItems(d).Text = _
		Replace(ListView1.Items(i).SubItems(d).Text, ">", "&gt;")
        ListView1.Items(i).SubItems(d).Text = _
		Replace(ListView1.Items(i).SubItems(d).Text, "<", "&lt;")
        ListView1.Items(i).SubItems(d).Text = _
		Replace(ListView1.Items(i).SubItems(d).Text, "&", "&amp;")
    Next d
Next i
    Select Case Parametro
        Case 1 
	' Texto sin codificacion ISO,
	' reemplazamos los caracteres alfabeticos comunes invalidos
	' Recorro todos la cabecera para reemplazar los caracteres inválidos"

	Cabecera = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>"
	txtcliente.Text = Replace(txtcliente.Text, "ñ", 
        "&#241;")
	txtcliente.Text = Replace(txtcliente.Text, "á", 
        "&#224;")
	txtcliente.Text = Replace(txtcliente.Text, "é", 
        "&#233;")
	txtcliente.Text = Replace(txtcliente.Text, "í", 
        "&#237;")
	txtcliente.Text = Replace(txtcliente.Text, "ó", 
        "&#243;")
	txtcliente.Text = Replace(txtcliente.Text, "ú", 
        "&#250;")
	txtcliente.Text = Replace(txtcliente.Text, "Á", 
        "&#193;")
	txtcliente.Text = Replace(txtcliente.Text, "É", 
        "&#201;")
	txtcliente.Text = Replace(txtcliente.Text, "Í", 
        "&#205;")
	txtcliente.Text = Replace(txtcliente.Text, "Ó", 
        "&#211;")
	txtcliente.Text = Replace(txtcliente.Text, "Ú", 
        "&#218;")
	txtcliente.Text = Replace(txtcliente.Text, "Ñ", 
        "&#209;")
	txtcliente.Text = Replace(txtcliente.Text, "¿", 
        "&#191;")

	
	' Recorro todos los items del listview para reemplazar los caracteres inválidos"

For i = 0 To ListView1.Items.Count - 1
	For d = 0 To 1
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "ñ", 
                "&#241;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "á", 
                "&#224;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "é", 
                "&#233;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "í", 
                "&#237;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "ó", 
                "&#243;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "ú", 
                "&#250;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "Á", 
                "&#193;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "É", 
                "&#201;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "Í", 
                "&#205;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "Ó", 
                "&#211;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "Ú", 
                "&#218;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "Ñ", 
                "&#209;")
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "¿", 
                "&#191;")
		' agregando " encoding="iso-8859-1" al XML
		' se resuelve lo de arriba pero quedan caracteres "
		'que(pueden) traesr problemas como el de diametro "Ø"
		ListView1.Items(i).SubItems(d).Text = _
			Replace(ListView1.Items(i).SubItems(d).Text, "Ø", 
                "&#216;")

	Next d
Next i

Case 2
' Similar al  anterior pero reemplazamos los caracteres > a 127 por su codigo 

For icontador = 128 To 255
	txtcliente.Text = Replace(txtcliente.Text, Chr(icontador), "&#" _
		& icontador.ToString & ";")
	ListView1.Items(i).SubItems(d).Text = _
		Replace(ListView1.Items(i).SubItems(d).Text, _
		Chr(icontador), "&#" & icontador.ToString & ";")
Next

Cabecera = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>"

        Case 3' No reemplazamos ningun caracter
            Cabecera = "<?xml version=" & Chr(34) & "1.0" & Chr(34) _
            & " encoding=" & Chr(34) & "iso-8859-1" & Chr(34) & "?>"

        Case 4' Reemplazamos algun caracter que la codificacion ISO no acepte (me ha pasado)
            Cabecera = "<?xml version=" & Chr(34) & "1.0" & Chr(34) _
            & " encoding=" & Chr(34) & "iso-8859-1" & Chr(34) & "?>"
            ListView1.Items(i).SubItems(d).Text = _
		Replace(ListView1.Items(i).SubItems(d).Text, "Ø", 
            "&#216;")
            txtcliente.Text = Replace(txtcliente.Text, "Ø", 
                "&#216;")

    End Select
    'Todos los items del listview tienen reemplazados los caracteres reservados
    ' y aquellos que XML no entiende
    'Procedemos a armar el XML

    'Cabecera observerse que para representar las "(comillas)
    ' utilizamos el codigo ascii Chr(34)"
    strXml = strXml & "<Pedido Cliente=" & Chr(34) & txtcliente.Text _
    & Chr(34) & " Numero=" & Chr(34) _
    & txtPedido.Text & Chr(34) & " Fecha=" & Chr(34) & dtpPEdido.Value.Month.ToString & "/" _
    & dtpPEdido.Value.Day.ToString & "/" & dtpPEdido.Value.Year.ToString & Chr(34) & ">"

    'N detalles
    For i = 0 To ListView1.Items.Count - 1
        strXml = strXml & "<ItemPedido Articulo=" & Chr(34) _
        & ListView1.Items(i).SubItems(0).Text & Chr(34) & " Cantidad=" & Chr(34) _
        & ListView1.Items(i).SubItems(1).Text & Chr(34) & "/> "
        items = items + 1
    Next


    ArmarXML = Cabecera & "<ROOT>" _
    & strXml _
    & " </Pedido></ROOT>"

End Function

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
		Handles btnAgregar.Click
    Dim item As New ListViewItem(txtarticulo.Text, 0)
		'Inserto los items en el listview
	

    item.SubItems.Add(txtcantidad.Text)
    ListView1.Items.AddRange(New ListViewItem() {item})
    txtcantidad.Clear()
    txtarticulo.Clear()
    txtcantidad.Focus()
End Sub

Private Sub Form1_Activated(ByVal sender As Object, ByVal e As System.EventArgs) _
		Handles Me.Activated
    txtcliente.Focus()
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
		Handles MyBase.Load
    CreateMyListView()
    InicializarControles()
End Sub

Private Sub CreateMyListView()

    ListView1.View = View.Details
    ListView1.LabelEdit = True
    ListView1.AllowColumnReorder = True
    ListView1.CheckBoxes = False
    ListView1.FullRowSelect = True
    ListView1.GridLines = True
    ListView1.Sorting = SortOrder.Ascending
    ListView1.Scrollable = True
    ListView1.Columns.Add("Descripcion", 100, HorizontalAlignment.Left)
    ListView1.Columns.Add("Cantidad", 100, HorizontalAlignment.Left)
End Sub

Private Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) _
                Handles Button1.Click
    Dim Conexion As New SqlConnection( _
	"user id=sa;Persist Security Info=False;Initial Catalog=nombrebase;" _
    	& "Data Source=localhost; Connect Timeout=2000; Current Language=ENGLISH")
    Dim Comando As New SqlCommand
    Dim oSQLAdapter As New SqlDataAdapter
    Dim oDS As New DataSet
    Dim oRow As DataRow
    Dim iCount As Integer = 0
    Dim iPedido As Integer
    Dim indec As Integer
    Dim procesepedido As Boolean
    Dim primer As Boolean
    Dim armarxml1 As String
    Try
        If IsNumeric(txtPedido.Text) Then
            'Conexion a la bd
            Comando.Connection = Conexion
            If RadioButton1.Checked = True Then
                armarxml1 = ArmarXML(1)
            Else
                If RadioButton2.Checked = True Then

                    armarxml1 = ArmarXML(2)
                Else
                    If RadioButton3.Checked = True Then

                        armarxml1 = ArmarXML(3)
                    Else
                        If RadioButton4.Checked = True Then
                            armarxml1 = ArmarXML(4)
                        End If
                    End If
                End If
            End If

	    'Insercion de Cabecera Detalle

            Comando.CommandText = "AltaCabeceraDetalleXML"
            Comando.CommandType = CommandType.StoredProcedure
            Comando.Parameters.Add("@XML", SqlDbType.Text)
            Comando.Parameters("@XML").Value = armarxml1
            Conexion.Open()
            Comando.ExecuteNonQuery()
            Conexion.Close()

            'Carga de treeview
            TreeView1.Nodes.Clear()
            TreeView1.BeginUpdate()
            TreeView1.Nodes.Add("Pedidos")
            primer = True
            Comando.CommandType = CommandType.Text
            Comando.CommandText = _
	   "SELECT A.IDPEDIDO,A.NROPEDIDO,A.CLIENTE,A.FECHA,B.CANTIDAD" _
            & ",B.ARTICULO  FROM PEDIDO A JOIN ITEMPEDIDO B ON A.IDPEDIDO=B.IDPEDIDO"
            oSQLAdapter.SelectCommand = Comando
            oSQLAdapter.Fill(oDS)
            indec = 0
            For Each oRow In oDS.Tables(0).Rows
                If primer = True Then
                    iPedido = oDS.Tables(0).Rows(iCount).Item("idpedido")
                    procesepedido = False
                    indec = 0
                Else
                    If iPedido = oDS.Tables(0).Rows(iCount).Item("idpedido") Then
                        procesepedido = True
                    Else
                        iPedido = oDS.Tables(0).Rows(iCount).Item("idpedido")
                        indec = indec + 1
                        procesepedido = False
                    End If
                End If
                primer = False
                If Not procesepedido Then
                    TreeView1.Nodes(0).Nodes.Add( _
			oDS.Tables(0).Rows(iCount).Item("nroPedido").ToString.Trim _
			+ "-" + oDS.Tables(0).Rows(0).Item("fecha").ToString.Trim)
                    TreeView1.Nodes(0).Nodes(indec).Nodes.Add( _
			oDS.Tables(0).Rows(iCount).Item("Cliente").ToString)
                End If
                'TreeView1.Nodes(0).Nodes(1).Nodes.Add("Grandchild")
                TreeView1.Nodes(0).Nodes(indec).Nodes(0).Nodes.Add( _
			"Cantidad:" + oDS.Tables(0).Rows(iCount).Item("Cantidad").ToString)
                TreeView1.Nodes(0).Nodes(indec).Nodes(0).Nodes.Add( _
			"Articulo:" + oDS.Tables(0).Rows(iCount).Item("Articulo").ToString)
                iCount += 1
            Next
            TreeView1.EndUpdate()
            ListView1.Items.Clear()
            InicializarControles()
        Else
            MsgBox("El pedido debe ser numerico")
        End If
    Catch ex As Exception
        MsgBox("Error . " & ex.Message, _
		MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error de base de datos")
        Exit Sub
    Finally
        oDS = Nothing
    End Try
End Sub

Conclusiones

Este ejemplo es solo la punta del ovillo para realizar las mas complejas transacciones en una solo procedimiento SQL, evitando manejar transacciones por código.

El ejemplo esta realizado en VB .NET 2005, en el fichero se encuentra el script de creación de las tablas y stored procedure. El ejecutable esta dentro de la carpeta XMLCABDET\bin\Release .

Inserción Cabecera-Detalle con XML, ejemplo práctico

Fecha: 27/Sep/2006 (19-09-06) Autor: Sebastian Contente - sebaconte@fibertel.com.ar Actualizado: 30/Sep/06

Introducción

Problemática

Fecha: 27/Sep/2006 (19-09-06)
Autor: Sebastian Contente - sebaconte@fibertel.com.ar

Actualizado: 30/Sep/06