Algumas implementações multivaloradas com XML e Table Value Parameter – Parte I

Boa Tarde Pessoal,

Um dia desses, um aluno me perguntou se o SQL Server disponibiliza alguma forma de tratar implementações "Master Detail" em uma única iteração sem a necessidade de loops para os registros "Detail" evitando assim várias trocas de contexto entre a aplicação e o banco de dados. Achei a pergunta bastante interessante e felizmente há algumas alternativas para lidar com essas situações. Antes de apresentá-las é importante exemplificar as dificuldades comuns de cadastro. Para isso utilizarei uma pequena aplicação em .NET. Meus conhecimentos nessa plataforma são um pouco limitados (tirei apenas o MCAD no .NET Framework 1.1), mas vejamos a construção de um aplicativo em C# (VS 2008) para simular o cadastro de um pedido e seus Itens. Para evitar a construção de um formulário de entrada de dados, farei a importação de dados a partir de um arquivo XML e a gravação através de stored procedures.

Arquivo XML de Pedidos

<?xml version="1.0"?>
 <Pedidos>
  <Pedido DataPedido="2010-05-17T20:45:25" Cliente="1" EnderecoID="2" Frete="45,39">
   <Item ProdutoID="11" Preco="15,02" Quantidade="21"/>
   <Item ProdutoID="13" Preco="16,05" Quantidade="15"/>
   <Item ProdutoID="12" Preco="29,55" Quantidade="33"/>
   <Item ProdutoID="15" Preco="32,13" Quantidade="17"/>
   <Item ProdutoID="16" Preco="29,99" Quantidade="23"/>
  </Pedido>
  <Pedido DataPedido="2010-05-17T20:57:33" Cliente="3" EnderecoID="1" Frete="95,33">
   <Item ProdutoID="17" Preco="150,31" Quantidade="25"/>
   <Item ProdutoID="11" Preco="290,00" Quantidade="20"/>
   <Item ProdutoID="18" Preco="324,55" Quantidade="16"/>
   <Item ProdutoID="13" Preco="456,12" Quantidade="34"/>
   <Item ProdutoID="14" Preco="399,99" Quantidade="43"/>
   <Item ProdutoID="10" Preco="238,45" Quantidade="19"/>
  </Pedido>
 </Pedidos>

A estrutura do arquivo XML é bem intuitiva. Há um nó root (Pedidos) que lista todos os pedidos a serem processados. Os nós do tipo "Pedido" tem os dados do Pedido e o nós do tipo "Item" possuem os itens de cada pedidos. Em princípio o arquivo foi salvo como C:\Pedidos.xml podendo ser alterado caso necessários. Os scripts abaixo criam uma base de dados, as tabelas e stored procedures necessárias.

Criação do Banco de Dados

— Cria uma base de dados
CREATE DATABASE Pedidos

— Muda o contexto de banco de dados
USE Pedidos

— Cria as tabelas de Pedidos e Itens
CREATE TABLE Pedidos (NumPedido INT NOT NULL IDENTITY(1,1),
    DataPedido DATETIME2(0) NOT NULL, Frete SMALLMONEY NOT NULL,
    ClienteID INT NOT NULL, EnderecoID INT NOT NULL,
    CONSTRAINT PK_Pedidos PRIMARY KEY (NumPedido))

CREATE TABLE Itens (
    ItemID INT NOT NULL IDENTITY(1,1), NumPedido INT NOT NULL,
    ProdutoID INT NOT NULL, Quantidade INT NOT NULL,
    PrecoUnitario SMALLMONEY NOT NULL,
    CONSTRAINT FK_Itens_Pedidos FOREIGN KEY (NumPedido)
    REFERENCES Pedidos (NumPedido))

— Cria a Stored Procedure para Inserção de Pedidos
CREATE PROCEDURE UspInserePedidos
    @DataPedido DATETIME2(0),
    @Frete SMALLMONEY,
    @ClienteID INT,
    @EnderecoID INT,
    @NumPedido INT OUTPUT
AS
    — Faz a gravação na tabela de pedidos
    INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
    VALUES (@DataPedido, @Frete, @ClienteID, @EnderecoID)

    — Atribui o valor gerado pela coluna Identity na variável @NumPedido
    SET @NumPedido = SCOPE_IDENTITY()

— Cria a Stored Procedure para Inserção de Itens
CREATE PROCEDURE UspInsereItens
    @NumPedido INT,
    @ProdutoID INT,
    @Quantidade INT,
    @PrecoUnitario SMALLMONEY
AS
    — Faz a gravação na tabela de itens
    INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
    VALUES (@NumPedido, @ProdutoID, @Quantidade, @PrecoUnitario)

Agora que o banco de dados está pronto, o próximo passo é criar uma aplicação para carregar o arquivo XML no banco de dados.

Console Application em C# para gravação de pedidos

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens");
            Console.ReadLine();

            // Instancia um documento XML
            XmlDocument xd = new XmlDocument();

            // Carrega o documento XML para o objeto xd
            xd.Load(@"C:\Pedidos.xml");

            // Converte o documento XML para um DataSet
            DataSet ds = new DataSet();
            ds.ReadXml(new XmlNodeReader(xd));

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona os parâmetros necessários
            cmdPed.Parameters.Add("@DataPedido", SqlDbType.DateTime2);
            cmdPed.Parameters.Add("@Frete", SqlDbType.SmallMoney);
            cmdPed.Parameters.Add("@ClienteID", SqlDbType.Int);
            cmdPed.Parameters.Add("@EnderecoID", SqlDbType.Int);

            // Cria uma variável para capturar o ID do Pedido
            Int32 NumPedido = 0;
            cmdPed.Parameters.Add("@NumPedido", SqlDbType.Int);
            cmdPed.Parameters["@NumPedido"].Direction = ParameterDirection.Output;

            // Instancia um comando para utilizar a stored procedure UspInsereItens
            SqlCommand cmdItem = sc.CreateCommand();
            cmdItem.CommandType = CommandType.StoredProcedure;
            cmdItem.CommandText = "UspInsereItens";

            // Adiciona os parâmetros
            cmdItem.Parameters.Add("@NumPedido", SqlDbType.Int);
            cmdItem.Parameters.Add("@ProdutoID", SqlDbType.Int);
            cmdItem.Parameters.Add("@Quantidade", SqlDbType.Int);
            cmdItem.Parameters.Add("@PrecoUnitario", SqlDbType.SmallMoney);

            // Abre a conexão
            sc.Open();

            // Para cada Pedido, registre-o
            foreach (DataRow drPed in ds.Tables["Pedido"].Rows)
            {
                cmdPed.Parameters["@DataPedido"].Value = DateTime.Parse(drPed["DataPedido"].ToString());
                cmdPed.Parameters["@Frete"].Value = Double.Parse(drPed["Frete"].ToString());
                cmdPed.Parameters["@ClienteID"].Value = Int32.Parse(drPed["Cliente"].ToString());
                cmdPed.Parameters["@EnderecoID"].Value = Int32.Parse(drPed["EnderecoID"].ToString());

                // Executa SP
                cmdPed.ExecuteNonQuery();

                // Captura o ID
                NumPedido = Int32.Parse(cmdPed.Parameters["@NumPedido"].Value.ToString());

                // Para cada Item, registre-o
                foreach (DataRow drItem in drPed.GetChildRows(ds.Relations[0]))
                {
                    cmdItem.Parameters["@NumPedido"].Value = NumPedido;
                    cmdItem.Parameters["@ProdutoID"].Value = Int32.Parse(drItem["ProdutoID"].ToString());
                    cmdItem.Parameters["@Quantidade"].Value = Int32.Parse(drItem["Quantidade"].ToString());
                    cmdItem.Parameters["@PrecoUnitario"].Value = Double.Parse(drItem["Preco"].ToString());

                    // Executa a SP
                    cmdItem.ExecuteNonQuery();
                }
            }

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

Antes de efetuar a compilação do código é necessário fazer as alterações para o caminho do arquivo XML (C:\Pedidos.xml) e o servidor utilizado em questão (utilizei a instância local default). Se os parâmetros utilizados não forem os mesmos é preciso fazer alterações no código. Os passos a seguir produzem o executável.

  • Abra o Notepad
  • Cole o código
  • Salve o arquivo em algum local com a extensão .cs (Ex: C:\CadastraPedido.cs)
  • Abra um prompt de comando
  • Navegue até o diretório C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 (ou superior se houver .NET Framework mais recente instalado)
  • Digite csc /out:C:\CadastraPedido.exe C:\CadastraPedido.cs e pressione ENTER (pressupondo que o arquivo seja C:\CadastraPedido.cs)

Após a compilação do código e geração do executável, basta abrir um prompt de comando e executá-lo:

Command Prompt
C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedido.exe C:\CadastraPedido.cs
Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053
for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727
Copyright (C) Microsoft Corporation 2001-2005. All rights reserved.

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\
C:\>CadastraPedido.exe
Pressione qualquer tecla para fazer o cadastro dos pedidos e itens

Os pedidos e itens foram cadastrados com êxito
Pressione qualquer tecla para fechar esse aplicativo

C:\>

Uma breve consulta a partir do Management Studio mostra que o cadastro foi feito com sucesso:

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido DataPedido Frete ClienteID EnderecoID
1 2010-05-17 20:45:25 45,39 1 2
2 2010-05-17 20:57:33 95,33 3 1

ItemID NumPedido ProdutoID Quantidade PrecoUnitario
01 1 11 21 15,02
02 1 13 15 16,05
03 1 12 33 29,55
04 1 15 17 32,13
05 1 16 23 29,99
06 2 17 25 150,31
07 2 11 20 290,00
08 2 18 16 324,55
09 2 13 34 456,12
10 2 14 43 399,99
11 2 10 19 238,45

A aplicação fez o cadastro dos pedidos e dos respectivos itens. Normalmente essa situação pode-se utilizar do método Update de uma classe SqlAdapter passando-se um objeto do tipo DataSet como parâmetro. Entretanto a presença de valores autoincrementáveis pode trazer certa dificuldade para utilizar apenas esse método como atualização de tabelas em uma relação Master Detail. A Microsoft possui um artigo detalhando essas dificuldades e como contorná-las em: Retrieving Identity or Autonumber Values (ADO.NET), mas o mesmo mostra como recuperar colunas autoincrementáveis, mas não necessariamente como utilizar essas colunas e atualizá-las em tabelas Master Details. Existem outras alternativas com o DataSet, mas o fato é que dificilmente se escapa da necessidade de efetuar um loop para as tabelas filhas como foi feito no exemplo.

O uso do tipo XML

O uso de um tipo semiestruturado como o XML permite a representação dos dados em um formato hierárquico em oposição ao uso de tabelas "Pai" e "Filho" ligadas por um relacionamento (exatamente como demonstrado no documento XML no início do artigo). A passagem dos dados via XML permite que a aplicação envie os dados diretamente ao banco de dados com uma única passagem de parâmetro evitando assim os loops e trocas de contexto entre o banco e a aplicação. Caberá ao banco transformar o XML em um formato relacional (Shredding XML). Para isso é necessário efetuar algumas alterações no documento XML (trocar as vírgulas por pontos), nas stored procedures utilizadas e na aplicação.

<?xml version="1.0"?>
 <Pedidos>
  <Pedido DataPedido="2010-05-17T20:45:25" Cliente="1" EnderecoID="2" Frete="45.39">
   <Item ProdutoID="11" Preco="15.02" Quantidade="21"/>
   <Item ProdutoID="13" Preco="16.05" Quantidade="15"/>
   <Item ProdutoID="12" Preco="29.55" Quantidade="33"/>
   <Item ProdutoID="15" Preco="32.13" Quantidade="17"/>
   <Item ProdutoID="16" Preco="29.99" Quantidade="23"/>
  </Pedido>
  <Pedido DataPedido="2010-05-17T20:57:33" Cliente="3" EnderecoID="1" Frete="95.33">
   <Item ProdutoID="17" Preco="150.31" Quantidade="25"/>
   <Item ProdutoID="11" Preco="290.00" Quantidade="20"/>
   <Item ProdutoID="18" Preco="324.55" Quantidade="16"/>
   <Item ProdutoID="13" Preco="456.12" Quantidade="34"/>
   <Item ProdutoID="14" Preco="399.99" Quantidade="43"/>
   <Item ProdutoID="10" Preco="238.45" Quantidade="19"/>
  </Pedido>
 </Pedidos>

Os scripts a seguir excluem todos os itens e pedidos das tabelas e zera suas respectivas sequências. Posteriormente as stored procedures de inserção de pedidos e itens são excluídas e substituídas por uma única stored procedure que faz a inserção nas duas tabelas.

— Muda o contexto de banco de dados
USE Pedidos;

— Elimina todos os registros de ambas as tabelas e reinicia os Identities
DELETE FROM Itens
DELETE FROM Pedidos
DBCC CHECKIDENT(Itens,‘RESEED’,0)
DBCC CHECKIDENT(Pedidos,‘RESEED’,0)

— Elimina as duas stored procedures
DROP PROCEDURE UspInsereItens
DROP PROCEDURE UspInserePedidos

— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE UspInserePedidos
    @Pedidos XML
AS

— Extrai e grava os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT
    P.p.value(‘@DataPedido’,‘DATETIME2(0)’) As DataPedido,
    P.p.value(‘@Frete’,‘SMALLMONEY’) As Frete,
    P.p.value(‘@Cliente’,‘INT’) As ClienteID,
    P.p.value(‘@EnderecoID’,‘INT’) As EnderecoID
FROM @Pedidos.nodes(‘/Pedidos/Pedido’) P(p)

— Insere e grava os itens
— Assume-se que um cliente jamais fará dois pedidos exatamente no mesmo momento
— A combinação DataPedido e ClienteID serve como chave alternativa para obter o ID do pedido

;WITH ItensColetados As (
SELECT
    P.i.value(‘../@DataPedido’,‘DATETIME2(0)’) As DataPedido,
    P.i.value(‘../@Cliente’,‘INT’) As ClienteID,
    P.i.value(‘@ProdutoID’,‘INT’) As ProdutoID,
    P.i.value(‘@Preco’,‘SMALLMONEY’) As PrecoUnitario,
    P.i.value(‘@Quantidade’,‘INT’) As Quantidade
FROM @Pedidos.nodes(‘/Pedidos/Pedido/Item’) P(i))

INSERT INTO Itens (NumPedido, ProdutoID, PrecoUnitario, Quantidade)
SELECT
    NumPedido, ProdutoID, PrecoUnitario, Quantidade
FROM ItensColetados As IC
INNER JOIN Pedidos As Ped ON
    IC.DataPedido = Ped.DataPedido AND IC.ClienteID = Ped.ClienteID

Agora que os objetos de persistência no banco de dados foram devidamente alterados, resta adaptar a aplicação para utilizá-los.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (XML)");
            Console.ReadLine();

            // Instancia um documento XML
            XmlDocument xd = new XmlDocument();

            // Carrega o documento XML para o objeto xd
            xd.Load(@"C:\Pedidos.xml");

            // Converte o documento XML para um DataSet
            DataSet ds = new DataSet();
            ds.ReadXml(new XmlNodeReader(xd));

            // Converte o DataSet para XML
            String xmlPed = ds.GetXml().ToString();

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona o parâmetro XML
            cmdPed.Parameters.Add("@Pedidos", SqlDbType.Xml);
            cmdPed.Parameters["@Pedidos"].Value = xmlPed;

            // Abre a conexão
            sc.Open();

            // Executa a Stored Procedure de cadastro de pedidos
            cmdPed.ExecuteNonQuery();

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

Há um passo desse código que gostaria de comentar. O início do código converte o arquivo XML para um DataSet e o passo seguinte converte o DataSet para XML. Não seria mais fácil então utilizar diretamente o XML a partir do arquivo ? Sim, não parece fazer sentido extrair dados em XML para o DataSet e posteriormente convertê-los para XML novamente, mas esses passos foram colocados propositalmente. O uso de um arquivo XML deve-se apenas para evitar a criação de um formulário de entrada de dados. Em uma aplicação real, provavelmente o uso Windows Form (ou Web Form) incluiria os dados em um DataSet e posteriormente faria a conversão para XML. Os passos para a compilação do código são exatamente os mesmos utilizados no início do artigo. Fiz apenas uma alteração no nome do arquivo .cs e no .exe para não conflitar com os arquivos anteriores. Os resultados são os mesmos da implementação anterior:

Command Prompt
C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedidoXML.exe C:\CadastraPedidoXML.cs
Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053
for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727
Copyright (C) Microsoft Corporation 2001-2005. All rights reserved.

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\
C:\>CadastraPedidoXML.exe
Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (XML)

Os pedidos e itens foram cadastrados com êxito
Pressione qualquer tecla para fechar esse aplicativo

C:\>

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido DataPedido Frete ClienteID EnderecoID
1 2010-05-17 20:45:25 45,39 1 2
2 2010-05-17 20:57:33 95,33 3 1

ItemID NumPedido ProdutoID Quantidade PrecoUnitario
01 1 11 21 15,02
02 1 13 15 16,05
03 1 12 33 29,55
04 1 15 17 32,13
05 1 16 23 29,99
06 2 17 25 150,31
07 2 11 20 290,00
08 2 18 16 324,55
09 2 13 34 456,12
10 2 14 43 399,99
11 2 10 19 238,45

O uso XML mostrou-se eficaz no cadastro de pedidos e seus respectivos itens. Além de produzir o mesmo resultado, foi capaz de fazer todo o procedimento com uma dose menor de código na aplicação (só foi necessário identificar o nome da SP e do parâmetro) e o deixou bem versátil (ainda que um overhead adicional seja concentrado no banco de dados).

A construção da stored procedure utilizou uma chave alternativa para identificar o pedido como único através da combinação DataPedido e ClienteID. Esse artifício foi utilizado uma vez que a chave dos pedidos é artificial (Identity) e não seria possível capturá-la e vinculá-la aos itens. Existem outras alternativas baseadas em TSQL como confiar em uma recuperação com base em posição (os primeiros itens serão dos primeiros pedidos) ou ainda utilizar cursores para gravar de forma alternada os pedidos e os itens mas não julgo ser uma solução muito performática. Outras alternativas incluem um ID do pedido no próprio XML, sua transformação com XSLT ou ainda o uso do atributo Nested em uma Data Relation para que o XML veja aninhado e os relacionamentos identificados. Enfim, são algumas das possibilidades quando se usa esse formato.

De forma a não tornar o artigo mais extenso do que eu gostaria e não me deparar com limitações ao tamanho do post, a parte I desse artigo finaliza aqui. A parte II pode ser conferida logo a seguir.

[ ]s,

Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s