Best Practice for importing data into custom database table when looping
Hello everyone, hope you'll are having a fine morning/evening.
I was able to import a list of Students along with their Subject in bulk. I am able to do so with the follow approach. The list mostly is in thousands.
using(var scope = _scopeProvider.CreateScope()) {
var db = scope.Database;
for (var i = 0; i < items.Count; i++) {
//insert student record
var student = items[i].Student;
db.Insert(student);
//insert subject record
var subject = items[i].Subject;
db.Insert(subject):
}
scope.Complete();
}
Is this approach correct where I am looping through all the items and then calling Scope.Complete() or should I call Scope.Complete on each iteration.
I would create all the objects and do a batch insert.
so define a list of students and add them in your loop and then do a batch insert
List<Student> students = new List<Student>();
Loop to add students to list ...
using (var transaction = scope.Database.GetTransaction())
{
var database = scope.Database;
database.InsertBatch<Student>(students);
transaction.Complete();
}
I like this approach, but say if I want tocheck if the record exists or not and only then insert or update. Can you please advise on what should be the way to go for this scenario?
A batch approach wouldn't work if you need to perform checks.
An alternative would be to have a secondary table (same schema as students, it could be a temp table). Batch import into it, then at the end of the batch INSERT INTO the main student table where the students didn't already exist.
Best Practice for importing data into custom database table when looping
Hello everyone, hope you'll are having a fine morning/evening.
I was able to import a list of Students along with their Subject in bulk. I am able to do so with the follow approach. The list mostly is in thousands.
Is this approach correct where I am looping through all the items and then calling Scope.Complete() or should I call Scope.Complete on each iteration.
I would create all the objects and do a batch insert.
so define a list of students and add them in your loop and then do a batch insert
I like this approach, but say if I want tocheck if the record exists or not and only then insert or update. Can you please advise on what should be the way to go for this scenario?
A batch approach wouldn't work if you need to perform checks.
An alternative would be to have a secondary table (same schema as students, it could be a temp table). Batch import into it, then at the end of the batch INSERT INTO the main student table where the students didn't already exist.
I would agree with Brendan if you need to check for existence.
is working on a reply...
This forum is in read-only mode while we transition to the new forum.
You can continue this topic on the new forum by tapping the "Continue discussion" link below.