How Do I Edit Database Records In A DataGridView Using LINQ to SQL?

Question

I'm so close on this one, but I need a little help. I have a simple WinForms app that uses LINQ to SQL to grab data that needs to be reviewed from a database and stuff it into a DataGridView. That part works great, but the connection I can't seem to make is how to get the data back into the database when things change.

Here's what I have so far:

db = new SiteDataDataContext();
src = new BindingSource();
src.DataSource = GetSitesPendingApproval(); // Returns IQueryable<Site>.
dataGridView1.DataSource = src;
dataGridView1.AllowUserToDeleteRows = true;
dataGridView1.EditMode = DataGridViewEditMode.EditOnEnter;

I have two actions I want to be able to take:

1. When the checkbox for the boolean value IsActive (DataGridView.Columns[7]) is checked/unchecked I want changes to be sent to the database.
2. When a record is deleted from the datagridview, I want it to also be deleted from the database.

I'm obviously missing some sort of binding, submit, merge, append, or cell changed event handler, but I haven't been able to figure out the connection to send back the changes. If it's easier to just make all the changes and then hit some sort of submit button to send it all back that's fine too.

Answer 1

Have you tried adding an event handler for the UserDeletingRow event that uses the id on the row to remove the row from the database?

private void DataGridView1_UserDeletingRow(object sender,
                                           DataGridViewRowCancelEventArgs e)
{
    DataGridViewRow row = sender as DataGridViewRow;

    int id = ...get id from row column...

    using (var dc = new SiteDataDataContext())
    {
         var site = dc.Sites.Where( s => s.ID == id ).SingleOrDefault();

         if (site != null)
         {
             try
             {
                dc.Sites.DeleteOnSubmit( site );
                dc.SubmitChanges();
                dataGridView1.Bind();
             }
             catch (SqlException)
             {
                e.Cancel = true;
             }
         }
     }
}

You might also provide an error message if the delete failed, but how you do it would depend on your application.