How to insert using Sqlbulkcopy with Entityframework

Question

I have a method that adds almost 2000 records at once, I was using the Bulk Insert and it was working perfectly, but this extension is paid for.

So I decided to use the Sqlbulkcopy class but I’m having a hard time with how to use 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; }
}

and :

  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; }
}

currently I do the insertion in 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 don’t know how to use the SqlBulkCopy, I am currently studying THIS EXAMPLE but I haven’t gotten anything yet. If any can explain how this insertion would work, I would be grateful.

Thank you very much from now on.

Answer 1

The same connection in the Entity Framework, but that’s not a rule you can make your own, only Sqlbulkcopy requires a Datatable or Idatareader to record the batch of information in a table, one initial code example:

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, can be made that interaction of the question and generate the Rows 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 is the most important for the use of Sqlbulkcopy, including to understand the process.

_References:

• Transferring Data Using Sqlbulkcopy
• Sqlbulkcopy
• Datatable
• Idatareader
• A workaround Sql Bulk Insert for Entity Framework 6.

Answer 2

In accordance with the microsoft documentation, if the copy(source) and destination data are in the same instance as SQL Server, it is faster and more efficient to use Transact SQL even, for example in a previous.

Another point, you are using a loop (foreach) to scan all records to generate the modifications and then re-enter the database.

You can easily convert this code to a INSERT SELECT, which will run much more quickly, mainly taking into account the amount of records.

Examples are: 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