Hi i have list of indicators which i am trying to insert into sql database. I have a facility, servicepoint, Indicator, agegroup, gender region and timestamp. Every indicator should have its own row in the database. I have about 150 indicators.
Databases.HTS hts = new Databases.HTS()
{
Year = Session["year"].ToString(),
Month = Session["month"].ToString(),
Facility = ddlFacility.SelectedValue.Split(":".ToCharArray())[0],
FacilityEntryPoint = ddlFacilityEntryPoint.Text,
Indicator = "Total Male Tested",
Total = Convert.ToInt16(maletestedunder1.Text),
SubGroup = "Under 1",
Region = ddlRegion.Text,
Timestamp = DateTime.Now,
For now you just told what you want and show us code for inserting a single entity. So I assume you don't know yet that you cand "Add" multiple entities (even a whole "graph" of related objects) and that EF will then save them all inside a single transaction
when calling SaveChanges.
Depending on your data, another option could be to use a single INSERT INTO SELECT SQL statement on the db side.
If you need further help you'll have to be more explicit about the exact problem you have when trying to do that...
var accounts = new List<Account>();
var context = new YourDbContext();
context.Configuration.AutoDetectChangesEnabled = false;
foreach (var account in apiData)
{
accounts.Add(account);
if (accounts.Count % 1000 == 0)
// Play with this number to see what works best
{
context.Set<Account>().AddRange(accounts);
accounts = new List<Account>();
context.ChangeTracker.DetectChanges();
context.SaveChanges();
context?.Dispose();
context = new YourDbContext();
}
}
context.Set<Account>().AddRange(accounts);
context.ChangeTracker.DetectChanges();
context.SaveChanges();
context?.Dispose();
insert into profiles (name, description) select first, 'Auto-generated' from users You seem to be confusing insert and update statement, which are different beasts.
None
0 Points
1 Post
C# Inserting multiple rows into sql
Feb 10, 2020 07:50 AM|Swazi_Programmer|LINK
Hi i have list of indicators which i am trying to insert into sql database. I have a facility, servicepoint, Indicator, agegroup, gender region and timestamp. Every indicator should have its own row in the database. I have about 150 indicators.
Databases.HTS hts = new Databases.HTS()
{
Year = Session["year"].ToString(),
Month = Session["month"].ToString(),
Facility = ddlFacility.SelectedValue.Split(":".ToCharArray())[0],
FacilityEntryPoint = ddlFacilityEntryPoint.Text,
Indicator = "Total Male Tested",
Total = Convert.ToInt16(maletestedunder1.Text),
SubGroup = "Under 1",
Region = ddlRegion.Text,
Timestamp = DateTime.Now,
};
healthDC.HTS.Add(hts);
healthDC.SaveChanges();
lblMessage.Text = "Record saved successfully!";
All-Star
48510 Points
18071 Posts
Re: C# Inserting multiple rows into sql
Feb 10, 2020 10:41 AM|PatriceSc|LINK
Hi,
For now you just told what you want and show us code for inserting a single entity. So I assume you don't know yet that you cand "Add" multiple entities (even a whole "graph" of related objects) and that EF will then save them all inside a single transaction when calling SaveChanges.
Depending on your data, another option could be to use a single INSERT INTO SELECT SQL statement on the db side.
If you need further help you'll have to be more explicit about the exact problem you have when trying to do that...
Contributor
3140 Points
983 Posts
Re: C# Inserting multiple rows into sql
Feb 11, 2020 01:58 AM|Yang Shen|LINK
Hi Swazi,
As @PatriceSc mentioned, EF can do the inserting multiple rows by default using the AddRange() menthod.
You can refer to Entity Framework update/insert multiple entities for a detaied sample.
Best Regard,
Yang Shen
None
0 Points
2 Posts
Re: C# Inserting multiple rows into sql
Mar 25, 2020 09:25 AM|fakeil|LINK
insert into profiles (name, description) select first, 'Auto-generated' from users You seem to be confusing insert and update statement, which are different beasts.
Lucky Patcher Kodi nox
Participant
1620 Points
927 Posts
Re: C# Inserting multiple rows into sql
Mar 25, 2020 11:23 PM|PaulTheSmith|LINK
There doesn't seem to be a question … what do you want to know?