How to insert using SqlBulkCopy with EntityFramework

3

I have a method that adds almost 2000 records at a time, I was using the Bulk Insert and it was working perfectly, however this extension and payment.

So I decided to use the SQLBulkCopy class but I'm having a hard time using it.

I have this class:

public class PessoaNotificacao
{
    public int PessoaNotificacaoId { get; set; }
    public int PessoaUnidadeId { get; set; }
    public int NotificacaoId { get; set; }
    public bool Visualizado { get; set; }
    public virtual Notificacao Notificacao { get; set; }
    public virtual PessoaUnidade PessoaUnidade { get; set; }
}

I have a method with a foreach creates an insert list with approximately 2000 records, the tables that make links are these:

  public class Notificacao
{
    public int Posicao { get; set; }
    public int NotificacaoId { get; set; }
    public int CategoriaId { get; set; }
}

e:

  public class Pessoa
{ 
    public int PessoaId { get; set; }
    public string Nome { get; set; }
    public string Email { get; set; }
    public string Cpf { get; set; }
    public string Senha { get; set; }
    public string Rg { get; set; }
}

I currently do the insertion this way:

   foreach (PessoaUnidade pessoa in pessoas){

                var pessoaNotificacao = new PessoaNotificacao
                {
                    Visualizado = false,
                    PessoaUnidade = pessoa,
                    Notificacao = notificacao
                };
                _contexto.PessoaNotificacao.Add(pessoaNotificacao);
            }

            _contexto.SaveChanges();

But I do not know how to use SqlBulkCopy , I'm currently studying THIS EXAMPLE but I have not yet achieved anything. If you can not explain how this insertion would work, I would be grateful.

Many thanks to all of you.

    
asked by anonymous 19.09.2017 / 15:19

2 answers

1

The same existing connection can be used in the Entity Framework , but, this is not a rule that can be done one, only SQLBulkCopy requires a DataTable or IDataReader to write the batch of information in

using (MyDbContext db = new MyDbContext())
{
    SqlConnection connection = (SqlConnection)db.Database.Connection;
    connection.Open();

    SqlBulkCopy copy = new SqlBulkCopy(connection);
    copy.DestinationTableName = "PessoaNotificacaoId";
    copy.ColumnMappings.Add("PessoaNotificacaoId", "PessoaNotificacaoId");
    copy.ColumnMappings.Add("PessoaUnidadeId", "PessoaUnidadeId");
    copy.ColumnMappings.Add("NotificacaoId", "NotificacaoId");
    copy.ColumnMappings.Add("Visualizado", "Visualizado");


    DataTable dt = new DataTable();
    dt.Columns.Add("PessoaNotificacaoId", typeof(int));
    dt.Columns.Add("PessoaUnidadeId", typeof(int));
    dt.Columns.Add("NotificacaoId", typeof(int));
    dt.Columns.Add("Visualizado", typeof(bool));

    DataRow row1 = dt.NewRow();
    row1["PessoaNotificacaoId"] = 1;
    row1["PessoaUnidadeId"] = 1;
    row1["NotificacaoId"] = 1;
    row1["Visualizado"] = true;

    DataRow row2 = dt.NewRow();
    row2["PessoaNotificacaoId"] = 2;
    row2["PessoaUnidadeId"] = 2;
    row2["NotificacaoId"] = 2;
    row2["Visualizado"] = true;

    DataRow row3 = dt.NewRow();
    row3["PessoaNotificacaoId"] = 3;
    row3["PessoaUnidadeId"] = 3;
    row3["NotificacaoId"] = 3;
    row3["Visualizado"] = true;
    dt.Rows.Add(row1);
    dt.Rows.Add(row2);
    dt.Rows.Add(row3);

    copy.WriteToServer(dt);
}

In this example, you can do that question interaction and generate the Rows of that DataTable , example :

DataTable dt = new DataTable();
DataRow row = null;
foreach (PessoaUnidade pessoa in pessoas)
{
    row = dt.NewRow();
    row["PessoaNotificacaoId"] = pessoa.Id;
    row["PessoaUnidadeId"] = pessoa.Id;
    row["NotificacaoId"] = notificacao.Id;
    row["Visualizado"] = false;
    dt.Rows.Add(row);
}

If you want to convert with a code it would be very practical to use a routine like this:

public static DataTable ConvertToDataTable<T>(IList<T> data)
{
    PropertyDescriptorCollection properties =
       TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    foreach (PropertyDescriptor prop in properties)
        table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) 
           ?? prop.PropertyType);
    foreach (T item in data)
    {
        DataRow row = table.NewRow();
        foreach (PropertyDescriptor prop in properties)
            row[prop.Name] = prop.GetValue(item)
             ?? DBNull.Value;
        table.Rows.Add(row);
    }
    return table;

}

But, do not forget that the first one is most important for using SQLBulkCopy , including to understand the process.

19.09.2017 / 16:25
1

According to Microsoft documentation If the copy (source) and destination data are in the same instance of SQL Server, it is faster and more efficient to use Transact SQL yourself, for example in a procedure.

Another point, you are using a (foreach) loop to scan all records to generate the changes and then re-insert them into the database.

You can easily convert this code to a INSERT SELECT , which will run much faster, especially with the number of records.

Examples: Simple example to copy all columns

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Example to copy only the required columns or modify some value

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

Reference: INSERT SELECT COMMAND

    
19.09.2017 / 15:36