CODEWITHSUNDEEP
c#sqllinq
user6057485
user6057485

Reputation:

LINQ to SQL update all records

I have a table like this:

enter image description here

I want to update Seasonal_Voucher column for all users with something

like : UPDATE Users SET Seasonal_Voucher = 'Something'

I tried this but it wont work:

using (DataClassesDataContext context = new DataClassesDataContext())
{
    Users use = context.Users;
    {
        use.Seasonal_Voucher =DropDownList1.SelectedItem.ToString();
    };
    context.Refresh(0);
    context.SubmitChanges();
}

Upvotes: 1

Views: 3219

Answers (2)

Keyur PATEL
Keyur PATEL

Reputation: 2329

As Sami suggested in the comments, "it won't work" is too broad. However, here is a basic modification of your code: (since you tagged linq also, I assume you are familiar with lambda syntax)

using (DataClassesDataContext context = new DataClassesDataContext())
{
    foreach (var use in context.Users)
    {
        use.Seasonal_Voucher = DropDownList1.SelectedItem.ToString();
    };
    //context.Refresh(0);
    //context.SubmitChanges();
    context.SaveChanges();
}

I realize (as Rob mentions) that loading the entire table into memory for updating is not wise. As such, you could simply try the ExecuteSqlCommand method (since it looks like you're using Entity Framework anyways). MSDN: Database.ExecuteSqlCommand Method

It is useful for bulk updates.

string sqlQuery = "UPDATE Users SET Seasonal_Voucher = '" + DropDownList1.SelectedItem.ToString() + "'";
using (DataClassesDataContext context = new DataClassesDataContext())
{
    context.Database.ExecuteSqlCommand(sqlQuery);
}

Upvotes: 3

ajanthanATstackoverflow
ajanthanATstackoverflow

Reputation: 35

Alternatively with reference to how to update the multiple rows at a time using linq to sql? Below code does the same. However as its mentioned by Keyur, entire table will be loaded for the update operation which would be a performance bottleneck. 

using (DataClassesDataContext context = new DataClassesDataContext())
{
   var users= context.Users.ToList();
    users.ForEach(user=>user.Seasonal_Voucher=DropDownList1.SelectedItem.ToString());
   context.SaveChanges();

}

or 

using (DataClassesDataContext context = new DataClassesDataContext())
{
   context.Users.ToList().ForEach(user=>user.Seasonal_Voucher=DropDownList1.SelectedItem.ToString());
   context.SaveChanges();   
}

Upvotes: 2

Related Questions