How to Save a WPF DataGrid Back to Database after Editing

Question

Hi,

I am wondering if theirs a way to save a updated Line from WPF DataGrid back to Database .

I don't Know the best way to do this without going back to WinForms Apps.

I would like to use WPF, any help would be appreciated.

Answer 1

Hi, @Luke A'Court. Welcome to Microsoft Q&A. 

First, you could get the row that the user is editing through the RowEditEnding event and record it.

Then write the recorded row to the database through Entity FrameWork Core or Ado.Net

Example Project

Assume that the table in the database you want to update is:

CREATE TABLE [dbo].[Person] (
    [Id]   INT          NOT NULL,
    [Name] VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

The corresponding class is:

Person.cs

    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

Here we take Ado.Net as an example and install System.Data.SqlClient through NuGet. Write Ado.Net code to write data to the database.

PersonService.cs (Note: Replace the connection string with your database connection string.)

    public class PersonService
    {
        public string connectionString = "Your database connection string ";

        public int Update(Person person) {
            string updateSql = "update Person set Name=@value2 where Id=@value1";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(updateSql, connection);
                command.Parameters.AddWithValue("@Value1", person.Id);
                command.Parameters.AddWithValue("@Value2", person.Name);

                try
                {
                    connection.Open();
                    int rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected;

                }
                catch (Exception ex)
                {
                    Console.WriteLine("An error occurred: " + ex.Message);
                }
                return -1;
            }
        }
    }

MainWindow.xaml

    <Grid>
        <DataGrid x:Name="MyDataGrid" CanUserAddRows="False" AutoGenerateColumns="False" RowEditEnding="MyDataGrid_RowEditEnding" >
            <DataGrid.Columns>
                <DataGridTextColumn Header="Id" Binding="{Binding Id}"></DataGridTextColumn>
                <DataGridTextColumn Header="Name" Binding="{Binding Name}"></DataGridTextColumn>
            </DataGrid.Columns>
        </DataGrid>
        <Button Width="200" Height="50" Margin="0,350,0,0" Content="Update" Click="Button_Click"></Button>
    </Grid>

MainWindow.xaml.cs

    public partial class MainWindow : Window
    {
        //Assume that the data read from the database is as follows
        public ObservableCollection<Person> MyList = new ObservableCollection<Person>() {
            new Person(){ Id = 1,Name="AA"},
            new Person(){ Id = 2,Name="BB"},
            new Person(){ Id = 3,Name="CC"},
        };

        //Use HashSet to avoid duplicate IDs
        HashSet<int> changeList = new HashSet<int>();

        PersonService personService = new PersonService();

        public MainWindow()
        {
            InitializeComponent();
            this.DataContext = this;
            MyDataGrid.ItemsSource = MyList;
        }
    
        private void MyDataGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
        {
            var person = e.Row.Item as Person;
            changeList.Add(person.Id);
        }
        
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            foreach(var id in changeList)
            {
                //Get the modified Person by the previously saved Id
                var person = MyList.FirstOrDefault(p=>p.Id == id);
                //Update using Ado.Net
                var result = personService.Update(person);

                if(result == -1)
                {
                    MessageBox.Show("Update Failure");
                }
                else
                {
                    MessageBox.Show("Update Success");
                }
            }
        }
    }

Additional: You could set UpdateSourceTrigger to PropertyChanged, so that var person = e.Row.Item as Person; in the RowEditEnding event method will directly obtain the modified value. However, it is not recommended to write data to the database directly in the RowEditEnding event method because the user may need to undo the update.

        <DataGrid x:Name="MyDataGrid" CanUserAddRows="False" AutoGenerateColumns="False" RowEditEnding="MyDataGrid_RowEditEnding" >
            <DataGrid.Columns>
                <DataGridTextColumn Header="Id" Binding="{Binding Id,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
                <DataGridTextColumn Header="Name" Binding="{Binding Name,UpdateSourceTrigger=PropertyChanged}"></DataGridTextColumn>
            </DataGrid.Columns>
        </DataGrid>

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.