Boa Noite Pessoal,
No artigo anterior, apresentei duas alternativas para lidar com implementações "Master Detail". A primeira foi mais trivial e envolveu várias iterações com o banco de dados. A segunda baseada em XML já optou por consolidar os dados e com uma única iteração com o banco de dados enviá-los em um formato XML. O próprio banco de dados efetuou o processo de Shredding convertendo os dados em XML para um formato tabular com linhas e colunas. Na parte II desse artigo demonstrarei uma terceira alternativa baseada no SQL Server 2008.
O SQL Server 2008 disponibilizou novas funcionalidades em relação ao Transact SQL. Uma dessas melhorias foi a evolução da variável do tipo TABLE para estruturas do conhecidas como User Defined Table Type & Table-Valued Parameters. Esse recurso permite que várias linhas sejam repassadas como uma única variável para stored procedures ou functions o que em outras palavras significa repassar uma tabela ou um resultset como parâmetro. O script a seguir exemplifica a criação de tipos TABLE:
— Muda o contexto de banco de dados
USE Pedidos;
— Cria um tipo tPedidos baseado na estrutura da tabela Pedidos
CREATE TYPE dbo.tPedidos As TABLE (
DataPedido DATETIME2(0) NOT NULL, Frete SMALLMONEY NOT NULL,
ClienteID INT NOT NULL, EnderecoID INT NOT NULL)
— Cria um tipo tPedidos baseado na estrutura da tabela Pedidos
CREATE TYPE dbo.tItens As TABLE (
NumPedido INT NOT NULL, ProdutoID INT NOT NULL,
Quantidade INT NOT NULL, PrecoUnitario SMALLMONEY NOT NULL)
A criação de dois tipos (tPedidos e tItens) permite sua plena reutilização em outras sessões e batch de comandos. A seguir são criadas e populadas duas variáveis baseadas nesses dois tipos.
— Declara uma variável do tipo tPedidos
DECLARE @Pedidos dbo.tPedidos
— Insere pedidos na variável @Pedidos
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:45:25’,45.39,1,2)
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:57:33’,95.33,3,1)
— Declara uma variável do tipo tItens
DECLARE @Itens dbo.tItens
— Insere itens na variável @Itens
INSERT INTO @Itens VALUES (1,11,21,15.02)
INSERT INTO @Itens VALUES (1,13,15,16.05)
INSERT INTO @Itens VALUES (1,12,33,29.55)
INSERT INTO @Itens VALUES (1,15,17,32.13)
INSERT INTO @Itens VALUES (1,16,23,29.99)
INSERT INTO @Itens VALUES (2,17,25,150.31)
INSERT INTO @Itens VALUES (2,11,20,290.00)
INSERT INTO @Itens VALUES (2,18,16,324.55)
INSERT INTO @Itens VALUES (2,13,34,456.12)
INSERT INTO @Itens VALUES (2,14,43,399.99)
INSERT INTO @Itens VALUES (2,10,19,238.45)
— Exibe os pedidos
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos
— Exibe os itens
SELECT NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM @Itens
DataPedido |
Frete |
ClienteID |
EnderecoID |
2010-05-17 20:45:25 |
45,39 |
1 |
2 |
2010-05-17 20:57:33 |
95,33 |
3 |
1 |
NumPedido |
ProdutoID |
Quantidade |
PrecoUnitario |
1 |
11 |
21 |
15,02 |
1 |
13 |
15 |
16,05 |
1 |
12 |
33 |
29,55 |
1 |
15 |
17 |
32,13 |
1 |
16 |
23 |
29,99 |
2 |
17 |
25 |
150,31 |
2 |
11 |
20 |
290,00 |
2 |
18 |
16 |
324,55 |
2 |
13 |
34 |
456,12 |
2 |
14 |
43 |
399,99 |
2 |
10 |
19 |
238,45 |
Com exceção da palavra "reusabilidade" não há absolutamente nenhuma diferença entre User Defined Table Type e variáveis do tipo TABLE. O script poderia ser substituído sem perda de desempenho ou diferenças no resultado final por variáveis do tipo TABLE. De fato variáveis do tipo TABLE e User Defined Table Type compartilham características em comum (não é à toa que alguns costumam dizer que User Defined Table Type são nada mais que variáveis do tipo TABLE "turbinadas"), mas vejamos algumas características mais particulares a estruturas User Defined Table Type.
— Cria uma função que recebe uma "tabela" do tipo tPedidos como parâmetro
CREATE FUNCTION dbo.ExibePedidos (@Peds dbo.tPedidos READONLY)
RETURNS TABLE
RETURN (SELECT DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos)
— Declara uma variável do tipo tPedidos
DECLARE @Pedidos dbo.tPedidos
— Insere pedidos na variável @Pedidos
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:45:25’,45.39,1,2)
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:57:33’,95.33,3,1)
— Faz a chamada à função ExibePedidos
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM dbo.ExibePedidos(@Pedidos)
A possibilidade de passar uma tabela como parâmetro é uma característica exclusiva a User Defined Table Type. Quando essas estruturas são repassadas como parâmetros para stored procedures e functions elas são conhecidas como Table Value Parameters. O exemplo acima declarou uma variável @Pedidos do tipo tPedidos, populou-a com vários registros e repassou-a para uma função que retornou seu conteúdo. No exemplo utilizado até então, pode-se utilizar uma única stored procedure para efetuar o cadastro recebendo apenas dois parâmetros (um do tipo tPedidos e outro do tipo tItens). Algo parecido com o exemplo a seguir:
— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE XXXXXX
@Pedidos dbo.tPedidos READONLY,
@Itens dbo.tItens READONLY
As
— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos
— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM @Itens
Da forma como está proposta, bastaria informar as variáveis @Pedidos e @Itens populadas para a stored procedure e ela faria a carga nas respectivas tabelas. Não faria nenhum sentido popular as variáveis com TSQL e informá-las para a stored procedure. Os parâmetros seriam populados pelo próprio ADO.NET com o uso de objetos do tipo SqlDbType.Structured. Uma pequena alteração deve ser realizada para capturar o numero do pedido gerado pela propriedade Identity.
— 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 a stored procedure UspInserePedidos
DROP PROCEDURE UspInserePedidos
— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE UspInserePedidos
@Pedidos dbo.tPedidos READONLY,
@Itens dbo.tItens READONLY
As
— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos
— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT SCOPE_IDENTITY(), ProdutoID, Quantidade, PrecoUnitario FROM @Itens
Dessa vez, ao invés de popular a aplicação a partir de um arquivo XML com vários pedidos e itens, farei a utilização de apenas um pedido e seus respectivos itens diretamente na aplicação (de forma a evitar a criação de um formulário para entrada de dados).
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 (TVP)");
Console.ReadLine();
// Cria um DataSet
DataSet ds = new DataSet();
// Adiciona duas DataTables (Pedido e Itens) ao DataSet
ds.Tables.Add("Pedidos");
ds.Tables.Add("Itens");
// Adiciona as colunas de Pedidos
ds.Tables["Pedidos"].Columns.Add("DataPedido", System.Type.GetType("System.DateTime"));
ds.Tables["Pedidos"].Columns.Add("Frete", System.Type.GetType("System.Double"));
ds.Tables["Pedidos"].Columns.Add("ClienteID", System.Type.GetType("System.Int32"));
ds.Tables["Pedidos"].Columns.Add("EnderecoID", System.Type.GetType("System.Int32"));
// Adiciona as colunas de Itens
ds.Tables["Itens"].Columns.Add("NumPedido", System.Type.GetType("System.Int32"));
ds.Tables["Itens"].Columns.Add("ProdutoID", System.Type.GetType("System.Int32"));
ds.Tables["Itens"].Columns.Add("Quantidade", System.Type.GetType("System.Int32"));
ds.Tables["Itens"].Columns.Add("PrecoUnitario", System.Type.GetType("System.Double"));
// Adiciona um pedido
DateTime DataPedido = DateTime.Parse("2010-05-17 20:45:25");
Double Frete = 45.39;
Int32 ClienteID = 1;
Int32 EnderecoID = 2;
ds.Tables["Pedidos"].Rows.Add(DataPedido, Frete, ClienteID, EnderecoID);
// Adiciona os itens do pedido
// Declara e inicializa os valores
Int32 NumPedido = 0; // Será sobrescrito no banco de dados
Int32 ProdutoID = 0;
Int32 Quantidade = 0;
Double PrecoUnitario = 0;
// Insere o primeiro Item
ProdutoID = 11;
Quantidade = 21;
PrecoUnitario = 15.02;
ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);
// Insere o segundo Item
ProdutoID = 13;
Quantidade = 15;
PrecoUnitario = 16.05;
ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);
// Insere o terceiro Item
ProdutoID = 12;
Quantidade = 33;
PrecoUnitario = 29.55;
ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);
// Insere o quarto Item
ProdutoID = 15;
Quantidade = 17;
PrecoUnitario = 32.13;
ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);
// Insere o quinto Item
ProdutoID = 16;
Quantidade = 23;
PrecoUnitario = 29.99;
ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);
// Comita as novas linhas
ds.AcceptChanges();
// 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âmetro Pedidos e Itens
cmdPed.Parameters.Add("@Pedidos", SqlDbType.Structured);
cmdPed.Parameters.Add("@Itens", SqlDbType.Structured);
cmdPed.Parameters["@Pedidos"].Value = ds.Tables["Pedidos"];
cmdPed.Parameters["@Itens"].Value = ds.Tables["Itens"];
// Abre a conexão
sc.Open();
// Executa a Stored Procedure
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();
}
}
}
Os passos para a compilação do código são exatamente os mesmos utilizados na primeira parte do artigo. Da mesma forma que a versão em XML foi alterada, fiz apenas uma modificação no nome do arquivo .cs e no .exe para não conflitar com os arquivos anteriores.
Command Prompt |
C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedidoTVP.exe C:\CadastraPedidoTVP.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:\>CadastraPedidoTVP.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP)
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 |
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 |
Essa abordagem funciona perfeitamente para estruturas onde apenas um pedido é informado ou chaves naturais são utilizadas, pois nesse caso não há grandes dificuldades no mapeamento dos relacionamentos no banco de dados. Essa é a situação mais comum, pois, normalmente em um formulário de entrada de dados informa-se um pedido de cada vez. Se os dados do XML (dois pedidos e onze itens) fossem utilizados não seria tão fácil já que colunas do tipo Identity são utilizadas. Como obter os Identities gerados na tabela de pedidos e repassá-los para os itens se as inserções são realizadas em sequência ? Da forma como foi proposto, fatalmente os relacionamentos seriam perdidos, pois, não seria possível recuperar o número do pedido e repassá-lo antes da inserção dos itens.
Resolver esse pequeno detalhe não é algo trivial. Se na implementação com XML várias soluções estavam disponíveis, o mesmo não é verdade com o uso de Table Value Parameters, pois, a inserção é direta com pouca margem a manipulação. Ao meu ver o uso de chaves alternativas seria um caminho (no caso de chaves naturais não estarem disponíveis). Seria possível por exemplo replicar na tabela de Itens do DataSet, as colunas DataPedido e ClienteID para que elas fossem submetidas junto com os demais dados. A estrutura tItens poderia contemplar essas colunas e no momento da inserção em Itens, um JOIN seria realizado para recuperar o Numero do Pedido. Algo semelhante à stored procedure abaixo:
— Recria a Stored Procedure UspInserePedidos
CREATE PROCEDURE XXXXXX
@Pedidos dbo.tPedidos READONLY,
@Itens dbo.tItens READONLY
As
— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos
— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT NumPedido, ProdutoID, Quantidade, PrecoUnitario
FROM @Itens As I
INNER JOIN Pedidos As P ON I.DataPedido = P.DataPedido AND I.ClienteID = I.ClienteID
Nos exemplos anteriores, os dados foram extraídos de um documento XML. Nesse caso, podemos utilizar as colunas de relacionamento que são criadas automaticamente quando o XML é importado para um DataSet e possui aninhamento. Essa combinação com chaves alternativas permite o cadastro sem a perda das referências.
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 (TVP V2)");
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âmetro Pedidos e Itens
cmdPed.Parameters.Add("@Pedidos", SqlDbType.Structured);
cmdPed.Parameters.Add("@Itens", SqlDbType.Structured);
cmdPed.Parameters["@Pedidos"].Value = ds.Tables["Pedido"];
cmdPed.Parameters["@Itens"].Value = ds.Tables["Item"];
// Abre a conexão
sc.Open();
// Executa a Stored Procedure
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();
}
}
}
No SQL Server, os tipos tPedidos e tItens foram alterados para ter exatamente o mesmo esquema que as tabelas do DataSet (inclusive a mesma ordem das colunas).
— 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 a stored procedure UspInserePedidos
DROP PROCEDURE UspInserePedidos
— Elimina a function ExibePedidos
DROP FUNCTION dbo.ExibePedidos
— Elimina os tipos Tipos tPedidos e tItens
DROP TYPE dbo.tPedidos
DROP TYPE dbo.tItens
— Recria os tipos tPedidos e tItens para refletir a estrutura do XML no DataSet
CREATE TYPE dbo.tPedidos As TABLE (Pedido_ID INT NOT NULL,
DataPedido DATETIME2(0) NOT NULL, Cliente INT NOT NULL,
EnderecoID INT NOT NULL, Frete SMALLMONEY NOT NULL)
CREATE TYPE dbo.tItens As TABLE (
ProdutoID INT NOT NULL, Preco SMALLMONEY NOT NULL,
Quantidade INT NOT NULL, Pedido_ID INT NOT NULL)
— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE UspInserePedidos
@Pedidos dbo.tPedidos READONLY,
@Itens dbo.tItens READONLY
As
— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, Cliente, EnderecoID FROM @Pedidos
— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT NumPedido, ProdutoID, Quantidade, Preco
FROM @Itens As I
INNER JOIN @Pedidos As P ON I.Pedido_ID = P.Pedido_ID
INNER JOIN Pedidos As Ped ON P.DataPedido = Ped.DataPedido AND P.Cliente = Ped.ClienteID
A compilação do código é idêntica às realizadas anteriormente. A única alteração que foi feita foi nos nomes dos arquivo cs e exe bem como a chamada do executável conforme o prompt abaixo:
Command Prompt |
C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedidoTVP_v2.exe C:\CadastraPedidoTVP_v2.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:\>CadastraPedidoTVP_v2.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP)
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 |
Como pode ser observado, a presença de colunas Identity pode complicar um pouco algumas implementações e nesse ponto chave naturais pode ser mais interessante. Felizmente a presença de chaves alternativas viabiliza o uso de Identities mesmo com um pouco mais de código. O uso do tipo XML e (ou) de Table Value Parameters são soluções robustas para implementações "Master Detail", pois, estão preparadas para qualquer cardinalidade, ou seja, funcionam bem independente da quantidade de registros "Details" e conseguem repassar os dados com uma única iteração.
[ ]s,
Gustavo