Quando se leva em conta a necessidade de compartilhamento de informações entre diferentes sistemas, arquivos ainda são um meio ainda bastante comum de integração. Esse tipo de prática está normalmente associado ao processamento de um grande volume de dados, podendo tanto acontecer entre softwares de uma mesma companhia, quanto entre aplicações de organizações distintas.
Listas de preços geradas por um determinado fornecedor, lançamentos contábeis e/ou financeiros, movimentações bancárias e registros de folha de pagamento são apenas alguns exemplos comuns de agrupamento de dados comumente carregados em bases relacionais por meio de arquivos.
Existem inúmeras soluções que podem ser empregadas em cenários como os citados aqui. A própria Microsoft conta com o Integration Services, sendo este último um dos componentes do SQL Server. A grande vantagem deste serviço (Integration Services) está em permitir que se obtenham aplicações robustas e extremamente flexíveis no que se refere processamento de informações.
Ainda sobre o Integration Services, merece destaque o fato de que o mesmo dispõe de mecanismos capazes de processar grandes massas de dados organizadas sob a forma dos mais variados tipos de arquivos, sem que isto implique em grandes esforços de implementação. São suportados, neste caso, formatos como texto (em que cada posição pode corresponder a um campo/informação), CSV (sigla do inglês "Comma-separated values", com os diferentes valores de cada linha estando separados neste caso por ponto-e-vírgula), XML ou ainda, planilhas do Excel e bancos de dados Access.
No entanto, nem sempre será possível se contar com ferramentas que simplificam a importação de dados presentes em arquivos. Se o gerenciador de banco de dados utilizado for o SQL Server, a classe SqlBulkCopy do .NET Framework (namespace System.Data.SqlClient) pode se revelar como um instrumento de grande valia em tais situações.
Partindo de objetos dos tipos DataReader, DataTable ou DataRow, a classe SqlBulkCopy fornece meios para a carga eficiente de grandes volumes de dados em tabelas do SQL Server. Além disso, a performance conseguida através de instâncias da classe SqlBulkCopy costuma se mostrar bem superior, sobretudo se comparada à execução de centenas de instruções SQL através de referências to tipo Command ou ainda, com mecanismos como LINQ to SQL ou o Entity Framework.
A finalidade deste artigo é demonstrar como o tipo SqlBulkCopy pode ser utilizado na importação do conteúdo de arquivos para bases de dados do SQL Server. Visando cumprir este objetivo, será apresentado um exemplo prático de como se proceder com a carga de um arquivo com a extensão .csv.
Criando uma aplicação que faz uso da classe SqlBulkCopy
A solução apresentada neste artigo foi criada no .NET framework 4.5, através da utilização do Microsoft Visual Studio 2012 Professional.
Basicamente, será construída uma Console Application de nome “TesteSqlBulkCopy (Figura 1), a qual será responsável por importar informações de um arquivo .csv contendo preços de produtos comercializados por um estabelecimento comercial.
Figura 1: Criando o projeto TesteSqlBulkCopy
Num cenário real, este catálogo de produtos poderia estar sendo gerado por um fornecedor da empresa considerada, englobando neste caso um número extenso de itens que seriam reprocessados a cada novo arquivo recebido (ao menos para os testes aqui descritos, será efetuada a carga de um arquivo com poucos itens, somente para efeitos de simulação).
Uma vez que a solução e o projeto correspondente tenham sido gerados, faz-se necessária a inclusão de uma referência para a biblioteca System.Configuration (Figura 2). Este ajuste foi efetuado de maneira que se possam acessar funcionalidades disponibilizadas pelo tipo ConfigurationManager (conforme será demonstrado durante a implementação das classes que compõem a aplicação).
Figura 2: Adicionando ao projeto uma referência à biblioteca System.Configuration
Quanto à estrutura das informações presentes no catálogo de produtos, o arquivo .csv em que constarão tais registros possuirá as seguintes colunas (já mencionadas aqui na ordem esperada pela aplicação de exemplo):
- Código de barras do produto;
- Nome do produto;
- Categoria (corresponde ao tipo de um produto: CDs, DVDs, Blu-Ray, Livros);
- Data em que o produto passou a fazer parte do catálogo;
- Quantidade disponível para comercialização;
- Preço de venda sugerido.
Na Figura 3 está um exemplo de como seria este arquivo.
OBSERVAÇÃO: Para efeito de testes, a aplicação de exemplo irá sempre procurar por um arquivo de nome “AtualizacaoCatalogo.csv” no diretório “C:\Temp\Catalogo\”.
Figura 3: Arquivo .csv contendo o catálogo de produtos
A estrutura da tabela que receberá as informações do arquivo AtualizacaoCatalogo.csv é apresentada na Listagem 1. Conforme será demonstrado mais adiante, está se partindo do pressuposto que essa estrutura (TB_CARGA_CATALOGO) faz parte de um banco de dados também chamado “TesteSqlBulkCopy”.
Listagem 1: Estrutura da tabela TB_CARGA_CATALOGO
CREATE TABLE [dbo].[TB_CARGA_CATALOGO](
[CodigoBarras] [varchar](13) NOT NULL,
[NomeProduto] [varchar](50) NOT NULL,
[Categoria] [varchar](30) NOT NULL,
[DtIniComercializacao] [datetime] NOT NULL,
[QtdDisponivel] [int] NOT NULL,
[VlSugerido] [decimal](10,2) NOT NULL,
CONSTRAINT [PK_TB_CARGA_CATALOGO] PRIMARY KEY ([CodigoBarras])
)
Já na Listagem 2 está o arquivo app.config do projeto TesteSqlBulkCopy. Encontram-se declarados no mesmo os seguintes itens:
- Uma ConnectionString de nome “TesteSqlBulkCopy”, a qual aponta para a base em que serão importadas as informações de produtos;
- O caminho em que consta o arquivo .csv correspondente ao catálogo de produtos (configuração “CaminhoArquivoCatalogo”).
Listagem 2: Arquivo app.config da aplicação TesteSqlBulkCopy
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="TesteSqlBulkCopy"
providerName="System.Data.SqlClient"
connectionString="Data Source=.;Initial Catalog=TesteSqlBulkCopy;Integrated Security=True"/>
</connectionStrings>
<appSettings>
<add key="CaminhoArquivoCatalogo"
value="C:\Temp\Catalogo\AtualizacaoCatalogo.csv"/>
</appSettings>
<startup>
<supportedRuntime version="v4.0"
sku=".NETFramework,Version=v4.5" />
</startup>
</configuration>
Com as diferentes configurações do projeto de testes já definidas, chega o momento de se proceder com a implementação das classes que serão utilizadas por esta aplicação.
A primeira dessas construções será o tipo estático ArquivoCatalogo (Listagem 3), o qual será responsável pela conversão do conteúdo do arquivo .csv contendo o catálogo de produtos num objeto equivalente da classe DataTable (namespace System.Data).
Constam na classe ArquivoCatalogo as seguintes operações:
- CreateDataTableCatalogo: método privado usado na criação novas instâncias do tipo DataTable, seguindo para isto a estrutura de colunas esperada para tais referências (o que é feito através de chamadas à operação Add, a partir da propriedade Columns);
- GetInformacoesCatalogo: a finalidade deste método é retornar um objeto do tipo DataTable com todo o conteúdo já convertido de um arquivo .csv, sendo que este processo envolve ainda uma chamada à operação privada CreateDataTableCatalogo.
A operação GetInformacoesCatalogo faz ainda uso das seguintes classes:
- ConfigurationManager (namespace System.Configuration): permite o acesso a elementos definidos num arquivo de configuração (app.config), com isto acontecendo por meio da propriedade AppSettings;
- StreamReader (namespace System.IO): uma instância deste tipo servirá de base para a leitura do arquivo .csv em que estão as informações do catálogo de produtos. A obtenção de cada linha existente em tal arquivo acontece invocando-se o método ReadLine;
- DataRow (namespace System.Data): representa um registro em um DataTable. As diferentes informações que formam uma linha do arquivo .csv são transformadas em um array (via método Split da classe String), com o conteúdo deste último sendo então associado às diferentes colunas previstas para o objeto DataRow.
Listagem 3: Classe ArquivoCatalogo
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.IO;
using System.Configuration;
namespace TesteSqlBulkCopy
{
public static class ArquivoCatalogo
{
private static DataTable CreateDataTableCatalogo()
{
DataTable dt = new DataTable();
dt.Columns.Add(
"Codigo", typeof(string));
dt.Columns.Add(
"Nome", typeof(string));
dt.Columns.Add(
"Categoria", typeof(string));
dt.Columns.Add(
"DtIniComerc", typeof(DateTime));
dt.Columns.Add(
"QtDisponivel", typeof(int));
dt.Columns.Add(
"PrecoSugerido", typeof(decimal));
return dt;
}
public static DataTable GetInformacoesCatalogo()
{
DataTable dtProdutos = CreateDataTableCatalogo();
using (StreamReader arquivo = new StreamReader(
ConfigurationManager
.AppSettings["CaminhoArquivoCatalogo"]))
{
string linhaArquivo;
string[] campos;
DataRow registro;
while (!arquivo.EndOfStream)
{
linhaArquivo = arquivo.ReadLine();
campos = linhaArquivo.Split(
new string[] { ";" },
StringSplitOptions.None);
registro = dtProdutos.NewRow();
registro["Codigo"] = campos[0].Trim();
registro["Nome"] = campos[1].Trim();
registro["Categoria"] = campos[2].Trim();
registro["DtIniComerc"] =
Convert.ToDateTime(campos[3]);
registro["QtDisponivel"] =
Convert.ToInt32(campos[4]);
registro["PrecoSugerido"] =
Convert.ToDecimal(campos[5]);
dtProdutos.Rows.Add(registro);
}
}
return dtProdutos;
}
}
}
Na Listagem 4 está a definição da classe estática CatalogoBulkCopyHelper. Esta última disponibiliza o método CreateSqlBulkCopy, o qual recebe como parâmetro uma conexão do tipo SqlConnection (namespace System.Data.SqlClient). Como resultado de sua execução, a operação CreateSqlBulkCopy retornará uma instância da classe SqlBulkCopy, objeto este devidamente configurado para a carga das informações do catálogo.
Sobre a criação de uma referência da classe SqlBulkCopy a partir da operação CreateSqlBulkCopy, é possível observar:
- A instância do tipo SqlConnection fornecida a CreateSqlBulkCopy é repassada como parâmetro ao construtor de SqlBulkCopy;
- Na propriedade DestinationTableName é definido o nome da tabela de destino (neste caso, “dbo.TB_CARGA_CATALOGO”);
- Já a propriedade ColumnMappings permite que se faça o mapeamento entre as colunas do DataTable com os dados de produtos e a tabela SQL Server correspondente, com isto acontecendo através de chamadas ao método Add. A configuração dessa forma deste tipo de relacionamento não é obrigatória em situações nas quais o nome das colunas de um objeto coincidir com a identificação dos campos da tabela considerada.
Listagem 4: Classe CatalogoBulkCopyHelper
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace TesteSqlBulkCopy
{
public static class CatalogoBulkCopyHelper
{
public static SqlBulkCopy CreateSqlBulkCopy(
SqlConnection conexao)
{
SqlBulkCopy bc = new SqlBulkCopy(conexao);
bc.DestinationTableName = "dbo.TB_CARGA_CATALOGO";
bc.ColumnMappings.Add("Codigo", "CodigoBarras");
bc.ColumnMappings.Add("Nome", "NomeProduto");
bc.ColumnMappings.Add("Categoria", "Categoria");
bc.ColumnMappings.Add("DtIniComerc",
"DtIniComercializacao");
bc.ColumnMappings.Add("QtDisponivel", "QtdDisponivel");
bc.ColumnMappings.Add("PrecoSugerido", "VlSugerido");
return bc;
}
}
}
Será por meio do método ProcessarImportacao da classe CatalogoFacade (Listagem 5) que ocorrerá a leitura do arquivo AtualizacaoCatalogo.csv e, consequentemente, a carga dos dados deste último para a tabela TB_CARGA_CATALOGO.
O tipo CatalogoFacade pode ser considerado um exemplo de uso de um padrão de projeto conhecido como Façade. Através deste pattern um conjunto de ações envolvendo diversos objetos pode ser encapsulado, de forma que estruturas que dependam de uma ou mais funcionalidades não precisem realizar uma série de ações complexas para produzir o efeito esperado.
Quanto à implementação da operação ProcessarImportacao, deve ser destacado:
- Inicialmente é gerada uma instância do tipo SqlConnection, utilizando para isto a string de conexão definida no arquivo app.config (via propriedade ConnectionStrings da classe ConfigurationManager);
- Um objeto do tipo SqlCommand é então criado, de forma a se executar uma instrução que elimina prováveis informações existentes na tabela TB_CARGA_CATALOGO. O comando “TRUNCATE TABLE” equivale, dentro do SQL Server, ao uso de uma instrução DELETE sem a cláusula WHERE; a única diferença neste caso está no fato de que a execução de TRUNCATE além de dispor de uma melhor performance, não grava em log quais registros foram excluídos (o que evita portanto a alocação desnecessária de espaço em alguns casos);
- Com os registros de TB_CARGA_CATALOGO removidos, o processo de importação será finalizado com a carga dos dados presentes no arquivo .csv para esta tabela. Uma instância da SqlBulkCopy é criada e (empregando a classe CatalogoBulkCopyHelper), na sequência, aciona-se o método WriteToServer (o qual recebe como parâmetro o DataTable retornado pela operação GetInformacoesCatalogo de ArquivoCatalogo); esta ação será responsável por transferir os dados que estão vinculados ao DataTable para a tabela correspondente na base de testes.
Listagem 5: Classe CatalogoFacade
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace TesteSqlBulkCopy
{
public class CatalogoFacade
{
public void ProcessarImportacao()
{
using (SqlConnection conexao =
new SqlConnection(ConfigurationManager
.ConnectionStrings["TesteSqlBulkCopy"]
.ConnectionString))
{
conexao.Open();
SqlCommand cmd = conexao.CreateCommand();
cmd.CommandText =
"TRUNCATE TABLE dbo.TB_CARGA_CATALOGO";
cmd.ExecuteNonQuery();
using (SqlBulkCopy bc =
CatalogoBulkCopyHelper.CreateSqlBulkCopy(conexao))
{
bc.WriteToServer(
ArquivoCatalogo.GetInformacoesCatalogo());
}
}
}
}
}
Por fim, a Listagem 6 apresenta o código referente à classe Program, com o método Main sendo acionado ao se executar este projeto de testes.
Conforme pode ser observado, uma instância de CatalogoFacade é criada dentro do método Main, invocando a partir disto o método ProcessarImportacao. Com esta ação será carregado o conteúdo do arquivo .csv com informações do catálogo de produtos e, na sequência, tais dados serão inseridos na base de dados de testes.
Listagem 6: Classe Program
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace TesteSqlBulkCopy
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine(
"Iniciando carga do catálogo de produtos...");
CatalogoFacade facade = new CatalogoFacade();
facade.ProcessarImportacao();
Console.WriteLine(
"Processamento concluído com sucesso!");
Console.ReadKey();
}
}
}
Executando a TesteSqlBulkCopy por meio do Visual Studio, será exibida uma tela como a que consta na Figura 4.
Figura 4: Carga dos dados já efetuada através da aplicação TesteSqlBulkCopy
Consultando a tabela TB_CARGA_CATALOGO dentro do SQL Server Management Studio, contata-se que os dados foram carregados corretamente nessa estrutura (Figura 5).
Figura 5: Consultando a tabela TB_CARGA_CATALOGO após a execução da aplicação TesteSqlBulkCopy
OBSERVAÇÃO: a exclusão de informações para a consequente carga de novos dados costuma ser um tipo de prática bastante comum na manipulação de arquivos. Isto se justifica pois, em muitas situações, os registros da tabela de destino não representam a versão definitiva: tais informações serão validadas e, posteriormente, transferidas para uma outra estrutura similar (através da execução de comandos de INSERT, UPDATE ou ainda, DELETE). Importante destacar que todo este processo não foi detalhado aqui por questões de simplificação.
Conclusão
Procurei com este artigo demonstrar uma maneira simples, porém bastante eficiente para a carga de centenas (ou até milhares) de registros em aplicações .NET que dependam de bancos de dados no SQL Server. O uso da classe SqlBulkCopy não substitui outros recursos para execução de instruções SQL como objetos Command do ADO.NET, LINQ to SQL e até o Entity Framework. Na verdade, a utilização de tais mecanismos é mais adequada em situações que envolvam o processamento de transações a partir de telas de cadastro de informações (e que, portanto, envolvem a execução de poucos comandos SQL).
Espero que o conteúdo aqui abordado possa ser útil no seu dia-a-dia. Até uma próxima oportunidade!
