Usando SqlBulkCopy

Pessoal,

Hoje tive que fazer um aplicativo para migrar uma base de dados, e em um determinado momento tive que fazer alguns milhares de inserts em uma tabela, e para piorar ainda mais tudo isso dentro de um ForEach 🙁

Bom vou descrever minhas tentativas para melhorar esse processo

Tentativa 1) A conexão estava sendo criada dentro do ForEach, isso quer dizer que a cada interação a conexão estava sendo criada, então deixei ela fora para ser criada apenas uma vez, melhorou bastante

Tentativa 2) Tirei todos os indices da tabela (Pk, Fk, etc..) melhorou um pouco

Tentativa 3) Peguei os inserts e coloquei em um arquivo .sql e depois mandava rodar esse script direto no banco, melhorou um pouco

Solução: Foi ai que lembrei de uma thead que rolou na lista .NET BR falando extamente esse assunto, foi lá que eu descobri a classe SqlBulkCopy que auxilia muito nesses casos de migração

Minha solução foi criar um arquivo XML contendo as informações que eu precisava, conforme abaixo:

[sourcecode language=”xml”]
<?xml version="1.0" encoding="UTF-8"?>
<juridicas>
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
<juridica cnpj="12345678901234" cpf="12345678901" cod_empreendimento="123" />
</juridicas>
[/sourcecode]

Depois de criar esse XML eu chamo um método que vai executar esse XML no banco de dados usando o SqlBulkCopy conforme abaixo:

[sourcecode language=”csharp”]
public void InsertTo_tmp_pessoa_juridica(string xml)
{
// String de conexão com o banco
string connectionString = @"Server=X;Database=X;Trusted_Connection=true";

// cria um novo data set
DataSet ds = new DataSet();
// Le o XML para dentro do DataSet
ds.ReadXml(xml);
// Recupera o DataTable do DataSet
DataTable sourceData = ds.Tables[0];

// Conecta no banco de dados
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
// Abre a conexão
destinationConnection.Open();

// Criamos uma instancia do SqlBulkCopy
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
{
// aqui devemos mapear as colunas do nosso XML para as colunas da nossa Tabela
bulkCopy.ColumnMappings.Add("cnpj", "cnpj");
bulkCopy.ColumnMappings.Add("cpf", "cpf");
bulkCopy.ColumnMappings.Add("cod_empreendimento", "cod_empreendimento");

// Nome da tabela no BD que vamos gravar as informações
bulkCopy.DestinationTableName = "tmp_pessoa_juridica";

// Mandamos executar a operação
bulkCopy.WriteToServer(sourceData);
}
}
}
[/sourcecode]

O código é bastante simples, temos que prestar atenção somente no mapeamento das colunas do XML para as colunas da nossa tabela.

Conclusão

Para aqueles que precisam executar vários registros na base de dados essa é uma boa opção, em meu caso para inserir cerca de 1.000.000 registros demorou cerca de 33s será que está bom??

Deixe seu comentário, opinião, critica

Aquele abraço!

Sobre Leandro Prado

Leandro Silveira Prado é Premier Field Engineer na Microsoft especializado em Application Lifecycle Management.