Cuidados ao utilizar SqlBulkCopy

Publicado em 2011-07-08 20:35 por Bruno Canettieri

Alguns cuidados são necessários ao utilizar SqlBulkCopy, em especial verificar as constraints e o tratamento aos nulos, que devem ser feitos explicitamente em código para evitar problemas de consistência potencialmente muito custosos.

Considere as seguintes tabelas

create table Employer
(
    EmployerId int not null constraint PK_Employer Primary Key
    --Outras informações
)

create table Employee
(
    EmployeeId int not null constraint PK_Employee Primary Key
    --Outras informações
)

create table EmployerEmployee
(
    EmployerId int not null constraint FK_EmployerEmployee_EmployerId
        Foreign Key references Employer (EmployerId),
    EmployeeId int not null constraint FK_EmployerEmployee_EmployeeId
        Foreign Key references Employee (EmployeeId),
    HireDate datetime null constraint df_EmployerEmployee default getdate(),
    constraint PK_EmployerEmployee Primary Key (EmployerId, EmployeeId)
)

A princípio as relações extrangeiras (foreign keys) garantem que não exista nenhum Id inválido (ex.: Existe uma relação mas o EmployerId não existe na tabela Employer). Mas o comportamento padrão do SqlBulkCopy é ignorar essas constraints.

Então se utilizarmos o código abaixo, quaisquer valores poderão ser inseridos nas tabelas.

private static void InserRelation(int employerId, IEnumerable employeeIds)
{
    using (var bulkCopy = new SqlBulkCopy(ConnectionString, ))
    using (var table = new DataTable())
    {
        table.Columns.Add("EmployerId", typeof(int));
        table.Columns.Add("EmployeeId", typeof(int));
        table.Columns.Add("HireDate", typeof(double));
        bulkCopy.DestinationTableName = "EmployerEmployee";

        bulkCopy.BulkCopyTimeout = BulkTimeout;
        bulkCopy.BatchSize = BulkBatchSize;

        for (int i = 0; i < table.Columns.Count; i++)
        {
            bulkCopy.ColumnMappings.Add(table.Columns[i].ColumnName, 
			                            table.Columns[i].ColumnName);
        }

        foreach (var employeeId in employeeIds)
        {
            table.Rows.Add(employerId, employeeId, null);

            if (table.Rows.Count >= bulkCopy.BatchSize)
            {
                bulkCopy.WriteToServer(table);
                table.Rows.Clear();
            }
        }

        bulkCopy.WriteToServer(table);
        table.Rows.Clear();
    }
}

Para garantir que os valores serão checados utilizar a opção SqlBulkCopyOptions.CheckConstraints. Deste modo:

using (var bulkCopy = new SqlBulkCopy(ConnectionString, 
    SqlBulkCopyOptions.CheckConstraints))

E mais...se checarmos os valores inseridos na coluna HireDate, veremos que o valores "null" passados foram desconsiderados e a constraint default foi utilizada em seu lugar. Para garantir os valores "null" combinar a opção utilizada com SqlBulkCopyOptions.KeepNulls. Desta forma:

using (var bulkCopy = new SqlBulkCopy(ConnectionString, 
    SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepNulls))

É claro que tudo isso vai depender da consistência e dos valores esperados no BD. Vale a pena testar as outras opções para não acabar com resultados inesperados.