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:

When the checkbox for the boolean value IsActive (DataGridView.Columns[7]) is checked/unchecked I want changes to be sent to the database.
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.

tvanfosson · Accepted Answer · 2016-06-17 18:58:26Z

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 DataGrid_UserDeletingRow(object sender,
                                           DataGridCommandEventArgs e)
{
    TableCell itemCell = e.Item.Cells[0]; // assumes id is in column 0

    int id = int.Parse(item.Text); // assumes it's non-null

    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.

edited Jun 17, 2016 at 18:58

answered Apr 12, 2009 at 16:57

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Jason Champion Over a year ago

Not quite how I would have expected to have to do it (figured I'd get more for free with data binding), but that'll do the trick. Thank you.

tvanfosson Over a year ago

For WebForms there is a LinqDataSource that you could bind to that gives you the ability to do a delete more easily, though I suspect it just does something like my example on the back end. I didn't see an analog for WinForms.

Nathan McKaskle Over a year ago

You have "get id from row column" ... how is this done?

tvanfosson Over a year ago

@NathanMcKaskle examine the row and pull the id from the column that contains it. You'd have to make sure to include it in a (perhaps hidden) column in the grid view.

Nathan McKaskle Over a year ago

Can I do that somehow from the row object cast from the sender? The row object has a number of options (methods and properties) on the intellisense but I'm not seeing anything that would reveal or help me get the ID of the row to update.

|

Collectives™ on Stack Overflow

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

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related