Inserting bulk records. Perhaps with SQL [duplicate]

Question

I have a button with a list of users in the table ButtonUsers. A parent Button is updated with the list of users, and all other Buttons' UserList should be updated. This isn't the case every time, therefore each button has it's own list. The problem is that I have maybe 5000 buttons, that should each be updated.

I tried pulling all buttons at once, then pull the users necessary, and set each buttons state as modified. When I've iterated through all the buttons, call SaveChanges().

public bool UpdatePreviousButtons(Button button, List<string> userList) { var buttons = fieldRepo.GetFieldsCreatedFrom<Button>(button.ID); var users = fieldRepo.GetUsersFromIDs(userList); foreach(var btn in buttons) { btn.UserList = users; fieldRepo.UpdateFieldGeneric<Button>(btn); } return fieldRepo.Commit(); }

The problem seems to be that it doesn't bulk insert the records, instead it inserts one record per call. This inevitably leads to a time out, and takes far to long.

I have been thinking about doing it manually with Context.Database.ExecuteSqlCommand(). Is there a better way, using Entity Framework?

It is common for ORMs (like EF) to work on a per-object basis; tools like SqlBulkCopy exist (and can be used with raw objects if needed), but they have fewer guarantees ("ACID" etc); you usually have to implement such things fairly manually. Alternatively, maybe consider "table valued parameters" — Marc Gravell
– Marc Gravell, Commented Aug 28, 2019 at 9:00

MDBarbier · Accepted Answer · 2019-08-28 08:59:05Z

You could try the SqlBulkCopy class:

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.8

As a simple example of how it is used:

var data = new DataTable(); data.Columns.Add("Name"); data.Columns.Add("Id"); for (var i = 1; i< 10_000; i++) { data.Rows.Add($"Name={i+1}", i+1); } using (var sqlBulk = new SqlBulkCopy(_connectionstring)) { sqlBulk.DestinationTableName = "People"; sqlBulk.WriteToServer(data); }

That looks like something that might work. Thanks for the example
Wow that worked really quick Thank you. As a bonus, is there something like this, to delete the current records, so that old data isn't left behind?
SqlBulkCopy just inserts as far as I know, this thread discusses ways to deal with requirements to update etc: stackoverflow.com/questions/4889123/…

Collectives™ on Stack Overflow

Inserting bulk records. Perhaps with SQL [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related