How to update Datagrid with OleDB?

Question

I use the following code to fill my datagrid dgFolien:

connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = "SELECT Hersteller,Serie,Farbe,[EK-Preis] FROM Folien";

command.ExecuteNonQuery();

OleDbDataAdapter dataAdp = new OleDbDataAdapter(command);
DataTable dt = new DataTable("Folien");
dataAdp.Fill(dt);
dgFolien.ItemsSource = dt.DefaultView;
dataAdp.Update(dt);

connection.Close();

Now my problem: The user should just doubleclick into a cell, edit the value, leave the cell and the table should be updated in my database.

How can I handle this?

I found that it can work with RowEditEnding, but I have no idea about the code for updating my database.

Anyone can help me?

Answer 1

Try to handle the event something like this:

private void dgFolien_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
{
    DataRowView drv = e.Row.DataContext as DataRowView;
    if (drv != null)
    {
        using (OleDbConnection conn = new OleDbConnection("yourConnectionString..."))
        {
            conn.Open();
            OleDbCommand cmd = conn.CreateCommand();
            cmd.Parameters.Add(new OleDbParameter("@var1", drv["Hersteller"].ToString()));
            cmd.Parameters.Add(new OleDbParameter("@var2", drv["Serie"].ToString()));
            cmd.Parameters.Add(new OleDbParameter("@var3", drv["Farbe"].ToString()));
            cmd.Parameters.Add(new OleDbParameter("@var4", drv["EK-Preis"].ToString()));


            cmd.CommandText = "UPDATE Folien SET Hersteller=@var1, Serie=@var2, Farbe=@var3 WHERE [EK-Preis] = @var4";
            cmd.ExecuteNonQuery();
        }
    }
}

Also make sure that you set the UpdateSourceTrigger property of the bindings to PropertyChanged in the XAML markup for the column values to be set immediately: Datagrid.RowEditEnding doesn't return the update value

<DataGrid x:Name="dgFolien" AutoGenerateColumns="False" CanUserAddRows="False" HorizontalAlignment="Left" 
                  Height="268" Margin="10,138,0,0" VerticalAlignment="Top" Width="489" ColumnWidth="*" RowEditEnding="dgFolien_RowEditEnding">
    <DataGrid.Columns>
        <DataGridTextColumn Header="Hersteller" Binding="{Binding Hersteller, UpdateSourceTrigger=PropertyChanged}" />
        <DataGridTextColumn Header="Serie" Binding="{Binding Serie, UpdateSourceTrigger=PropertyChanged}" />
        <DataGridTextColumn Header="Farbe" Binding="{Binding Farbe, UpdateSourceTrigger=PropertyChanged}" />
        <DataGridTextColumn Header="EK-Preis" Binding="{Binding EK-Preis, UpdateSourceTrigger=PropertyChanged}" />
    </DataGrid.Columns>
</DataGrid>