How to Get a Wpf Datagrid to Save Changes Back to the Database

How do I Get a WPF DataGrid to Save Changes Back to the DataBase?

Performing Updates

When the user edits the Customers data within the DataGrid, the bound in-memory DataTable is updated accordingly. However, these updates are not automatically written back to the database. It is up to the developer to decide when changes to the DataTable are written back to the database depending on the requirements of the application. For example, in some cases, you might wish to submit a batch of changes via a "Submit" button, or you may wish to have the database updated as the user commits each row edit. In order to support these, the rows that the DataTable contains have a RowState property which indicates whether they contain changes which should be synchronized with the database. The synchronization process is easily achieved via the TableAdapter's Update method.
url:
WPF DataGrid examples

The following example shows how the RowChanged and RowDeleted events can be handled so that changes in the DataTable state are written to the database each time the user changes a row:

public CustomerDataProvider()
{
    NorthwindDataSet dataset = new NorthwindDataSet();

    adapter = new CustomersTableAdapter();
    adapter.Fill(dataset.Customers);

    dataset.Customers.CustomersRowChanged +=
        new NorthwindDataSet.CustomersRowChangeEventHandler(CustomersRowModified);
    dataset.Customers.CustomersRowDeleted +=
        new NorthwindDataSet.CustomersRowChangeEventHandler(CustomersRowModified);
}

void CustomersRowModified(object sender, NorthwindDataSet.CustomersRowChangeEvent e)
{
    adapter.Update(dataset.Customers);
}

How to make WPF DataGrid save changes back to database?

Well, I should search harder be for post, anyway here is the solution:

    SqlCommandBuilder builder = new SqlCommandBuilder(a);
    a.UpdateCommand = builder.GetUpdateCommand();
    a.Update(d);

For more detail: http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx

Editing and saving data in datagrid

Implement INotifyPropertyChanged on class Track. like that

public class Track : INotifyPropertyChanged 
{
     public string title
     {
         get{return _t;}
         set
            {
                 _t = value;
                 OnPropertyChanged("title");
            }
     }
     public event PropertyChangedEventHandler PropertyChanged;
     public void OnPropertyChanged(string strCaller = null)
     {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(strCaller));
     }
}

In XAML code, you can modify a little bit:

Binding="{Binding title, Mode="TwoWay", UpdateSourceTrigger="PropertyChanged"}

WPF Save Changes in DataGrid to DataTable

Finally found the answer!. I have to Get the DataRow that was being changed:

private void dataGrid_CurrentCellChanged(object sender, EventArgs e)
{
    DataTable dt = ((DataView)dataGridQueue.ItemsSource).ToTable();
    // Set the value of my datatable to the the changed version before 
    // writing it so a file.
    dt.WriteXMLScema(...);
}

Unable to update Datagrid and save changes to database

The output will be like this:-

Change your Xaml like give below

<DataGrid x:Name="dataGrid" Margin="5,5,10,5" AutoGenerateColumns="False"  HorizontalAlignment="Stretch" ItemsSource="{Binding ProductList}" VerticalAlignment="Stretch" Height="566"  >
<i:Interaction.Triggers>
     <i:EventTrigger EventName="RowEditEnding" ">
        <cmd:EventToCommand Command="{Binding EndEdit}" PassEventArgsToCommand="True"/>
     </i:EventTrigger>
</i:Interaction.Triggers>
<DataGrid.Columns>
  <DataGridTextColumn x:Name="Id" Binding="{Binding Path=Id, Mode=TwoWay, UpdateSourceTrigger=LostFocus}" Header="Id"/>
    <DataGridTextColumn x:Name="name" Binding="{Binding Path=Name, Mode=TwoWay, UpdateSourceTrigger=LostFocus}" Header="Name"/>
    <DataGridTextColumn x:Name="cost" Binding="{Binding Path=Cost, Mode=TwoWay, UpdateSourceTrigger=LostFocus}" Header="Cost"/>
    <DataGridTextColumn x:Name="Quantity" Binding="{Binding Path=Quantity, Mode=TwoWay, UpdateSourceTrigger=LostFocus}" Header="Quantity"/>
    <DataGridTextColumn x:Name="total" Binding="{Binding Path=Total, Mode=TwoWay, UpdateSourceTrigger=LostFocus}" Header="Total"/>
</DataGrid.Columns>

And in View Model you Bindings be like

public BindingList<Product> ProductList
{
 get
  {
   return _proxy.ProductList;
  }
}

And EndEdit Command Should execute the following function

private void ExecuteEndEdit(DataGridRowEditEndingEventArgs param)
 {
  var product  = param.Row.Item as Product; 
  var result = ProductList.FirstOrDefault(p => p.Id == product.Id);
  var index= ProductList.IndexOf(result);
  result.Total = result.Cost * result.Quantity;
  ProductList.ResetItem(index);
 }

Your IDataService can Expose Binding List like

  public class DataService : IDataService
    {
        ProductEntities context;
        public DataService()
        {
            context = new ProductEntities();
        }
        public BindingList<Product> ProductList
        {
            get
            {
               //EDIT: YOU HAVE TO CALL context.Products.Load(); OR IT WILL RETURN EMPTY RESULTS
               context.Products.Load();
                return context.Products.Local.ToBindingList<Product>();
            }
        }
        public void SaveAll()
        {
            context.SaveChanges();
        }
    }

The Context.Save will save your code.

Save Changes to SQL Server Database from WPF Datagrid

Updates will work, because the fields in the view map 1:1 to fields in the underlying tables, so Sql Server will be able to resolve updates in the view back to the tables (even AccountType).

Inserts will not work because Account.AccountTypeId can not be set and I assume that it is a mandatory field.

Deletes will not work, because Sql Server cannot tell which record in which table you are trying to delete.

I think in this case, you can just as well work with Accounts records and show AccountTypeId in a DataGridComboBoxColumn that has AccountData as datasource, displays AccountData.AccountType and binds to Account.AccountTypeId.

WPF MVVM - DataGrid does not update changes back to database

Yeah, the reason is that your Viewmodels haven't a direct access to the Database.

        //opening the database
        using (var context = new NinjaApp_DatabaseEntities()) 
        {
           //query the database and store the result in memory
            var ninjas = context.ninjas.ToList();

           //Viewmodel mapping
            Ninjas = new ObservableCollection<NinjaVM>(ninjas.Select(r => new NinjaVM(r)));
        } // in the end of the using statement the database will be "closed"

Manipulating an ninja doesn't affect the database.

I suspect you are using the entity framework.

so if you want to store the changes, you must reopen the Database, search for the specific ninja and overwrite the property and use the SaveChanges Methode.

For practicing you can do this in the setter

    public class NinjaVM
{

    private int _id;

    private string _name;
    public string Name
    {
        get { return _name; }
        set
        { 
            using (var context = new NinjaApp_DatabaseEntities())
            {
                var ninja = context.ninjas.FirstOrDefault(n => n.Id == _id  ));
                if(ninja == null)
                return;
                ninja.Name = value;
                context.SaveChanges();
            }
        }
    }

Save a new Datagrid Row to database

You can save a newly entered row into database from datagrid as follows:

First assign datasource to datagrid:

datagrid.ItemsSource = myview.GetList();

Next select the SelectionChanged Event of Datagrid:

private void datagrid_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
  objToAdd = datagrid.SelectedItem as clsName; //Entity Object
}

Next select CellEditEnding Event of Datagrid:

private void datagrid_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
    {
        try
        {
            FrameworkElement element1 = datagrid.Columns[0].GetCellContent(e.Row);
            if (element1.GetType() == typeof(TextBox))
            {
                var colomn1 = ((TextBox)element1).Text;
                objToAdd.Column1 = Convert.ToInt32(Column1);
            }
            FrameworkElement element2 = datagrid.Columns[1].GetCellContent(e.Row);
            if (element2.GetType() == typeof(TextBox))
            {
                var colomn2 = ((TextBox)element2).Text;
                objToAdd.Column2 = Convert.ToInt32(Column2);
            }
            FrameworkElement element3 = datagrid.Columns[2].GetCellContent(e.Row);
            if (element3.GetType() == typeof(TextBox))
            {
                var colomn3 = ((TextBox)element3).Text;
                objToAdd.Column3 = Convert.ToInt32(Column3);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message); 
        }
    }

Next select RowEditEnding Event of Datagrid:

private void datagrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
    {
        try
        {
            var Res = MessageBox.Show("Do you want to Create this new entry", "Confirm", MessageBoxButton.YesNo);
            if (Res == MessageBoxResult.Yes)
            {
                EntityObject.InsertEmployee(objToAdd);
                EntityObject.SaveChanges();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message); 
        }
    }

Hope this will help you...

Updating DATAGRID row to save to SQL

Just copy below codes. I've created all the thing of you and tested successfully. Rather than the first way, I tried to let you go more popular way. Therefore, it took me time to adopt..

Hope this helps you !

SqlDataAdapter da;
DataTable dt;

    private void Window_Loaded(object sender, RoutedEventArgs e)
    {
        SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = yourConnectionString;
        Conn.Open();

        SqlCommand gridcomm = new SqlCommand();
        gridcomm.Connection = Conn;

        gridcomm.CommandText = "SELECT Id, Name, Quantity, Rate, Time FROM Budget";

        da = new SqlDataAdapter(gridcomm);

        SqlDataReader gridreader = gridcomm.ExecuteReader();
        while (gridreader.Read())
        {
        }
        gridreader.Close();

        dt= new DataTable("Budget");
        da.Fill(dt);

        dataGrid_Budget.ItemsSource = dt.DefaultView;

        Conn.Close();

    }

    private void dataGrid_Budget_RowEditEnding(object sender, System.Windows.Controls.DataGridRowEditEndingEventArgs e)
    {
        DataGridRow editedrow = e.Row;

        int row_index = (DataGrid)sender).ItemContainerGenerator.IndexFromContainer(editedrow);

        for (int k=0;k< 5;k++)
        {
            DataGridCell cell = GetCell(row_index, k);
            TextBlock tb = cell.Content as TextBlock;

            if (k==1)
            {
                dt.Rows[row_index][k] = tb.Text;
            }
            else if (k == 4)
            {
                if (tb.Text != "")
                {
                    dt.Rows[row_index][k] = Convert.ToDateTime(tb.Text);
                }
            }
            else
            {
                dt.Rows[row_index][k] = Convert.ToInt32(tb.Text);
            }
        }

        da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();

        da.Update(dt);
    }

    public DataGridCell GetCell(int row, int column)
    {
        DataGridRow rowContainer = GetRow(row);

        if (rowContainer != null)
        {
            DataGridCellsPresenter presenter = GetVisualChild<DataGridCellsPresenter>(rowContainer);

            DataGridCell cell = (DataGridCell)presenter.ItemContainerGenerator.ContainerFromIndex(column);
            if (cell == null)
            {
                dataGrid_Budget.ScrollIntoView(rowContainer, dataGrid_Budget.Columns[column]);
                cell = (DataGridCell)presenter.ItemContainerGenerator.ContainerFromIndex(column);
            }
            return cell;
        }
        return null;
    }

    public DataGridRow GetRow(int index)
    {
        DataGridRow row = (DataGridRow)dataGrid_Budget.ItemContainerGenerator.ContainerFromIndex(index);
        if (row == null)
        {
            dataGrid_Budget.UpdateLayout();
            dataGrid_Budget.ScrollIntoView(dataGrid_Budget.Items[index]);
            row = (DataGridRow)dataGrid_Budget.ItemContainerGenerator.ContainerFromIndex(index);
        }
        return row;
    }

    public static T GetVisualChild<T>(Visual parent) where T : Visual
    {
        T child = default(T);
        int numVisuals = VisualTreeHelper.GetChildrenCount(parent);
        for (int i = 0; i < numVisuals; i++)
        {
            Visual v = (Visual)VisualTreeHelper.GetChild(parent, i);
            child = v as T;
            if (child == null)
            {
                child = GetVisualChild<T>(v);
            }
            if (child != null)
            {
                break;
            }
        }
        return child;
    }

---

Related Topics