0

I am trying to update a sql table using linq to sql :

Querying the database using Linq to SQL I am able to load the data into a gridview in the UI in a WPF application. So I have a check box associated with each row. The table has EmpName and Ability as two columns. So when user checks the check box associated with the row/rows I have to update the value in the table back in the sql DB.

Here is the code for my check box checked :

    private void EmployeeName_Checked(object sender, RoutedEventArgs e)
    {
      DataDataContext dc = new DataDataContext();

        foreach (var item in empListView.SelectedItems)
        {


        EmpList updateList  = (from p in dc. EmpList
                                          where p.able == No
                                          select p).FirstOrDefault();


            if(updateList.able==NO)
            updateList.able = YES;

        }

        dc.SubmitChanges();
        empListView.ItemsSource = dc.EmpLists.ToList();
    }

EmpList here is the one created when I added the linqtosql class from the designer. So I am trying to update the same list which inturn submits the changes to the DB.

Question: I am able to select the rows based on the selected checkboxes in the gridview and I am able to loop through the selected items, but I am unable to update all the selected rows..only few are updating...I would be glad if some one can look at the code and guide me if theres any thing wrong with the query.

Ultimately, I have to update all the selcted rows based on their current values in the colum Ability of the gridview.

Macnique
  • 1,028
  • 2
  • 19
  • 44
  • You sample code would not compile. I still guess your problem is that you overwrite the datacontext on each iteration. Your save call therefore only acts on the last one. – usr Feb 21 '12 at 21:01
  • @cadrell0 Yes and No are bit values from the database. – Macnique Feb 21 '12 at 21:04
  • 1
    @usr additionally, the call to `dc.SubmitChanges()` happens at a different scope than where the variable is declared. It should be out of scope at that point and unavailable. – cadrell0 Feb 21 '12 at 21:04
  • cadrell0, thats what I meant with "would not compile". I think the question is posted in a kind of careless way. – usr Feb 21 '12 at 22:04

2 Answers2

0

Here is a good article on Batch Updates and Deletes with LINQ to SQL http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx

Maybe this gives you an idea or a pointer...

silverfighter
  • 6,762
  • 10
  • 46
  • 73
0

DataDataContext dc = new DataDataContext(); needs to be moved outside of the foreach loop. Currently it should not compile because it is out of scope. I don't understand how you are linking the item selected to the item you are pulling from the data set, you are just pulling the first one? Does your item have an Id or something to grab?

Connor Ross
  • 345
  • 2
  • 13
  • Since you plan on grabbing all the EmpList at the end of the function, you might as well grab it before your do your foreach loop. This will mean only one fetch from the database, and still allow you to update them. In your linq query select from your saved list, and find where item.ID == p.ID && p.able == NO. That will make sure you are actually grabbing the selected one, not just the random first one. – Connor Ross Feb 22 '12 at 16:51