Posting Data to a Connected Database in ADO.NET

  • 2 minutes to read

When binding using ADO.NET, you typically bind a control to a DataTable containing data from a database. When you change data via the Tree List control (adding, deleting or modifying records), the changes are accumulated in the underlying DataTable. They are not automatically posted to the database. Therefore, you need to manually call dedicated methods to post the changes.

The ADO.NET data model implies using a Data Adapter object, helping you to populate a DataTable with data from the database. The Data Adapter also provides the Update method that allows changes from the DataTable to be posted back to the database.

Before calling the Update method, make sure that the Tree List control has saved all the changes made to the currently focused node (an end-user could enter new data but forget to update the node). For this purpose, you need to call the TreeList.PostEditor and TreeList.EndCurrentEdit methods.

The following code contains the UpdateDatasource method that posts changes stored in a custom Suppliers DataTable to a database. It is assumed that a corresponding data adapter (oleDbDataAdapter1) contains appropriate Update SQL statements to modify the target table in the database

In the example, the dataSet1 object is a DataSet instance that contains the Suppliers DataTable.

using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Base;
using System.Data.Common;
//...
public void UpdateDatasource(TreeList treeList1) {
    //Save the latest changes to the bound DataTable
    treeList1.PostEditor();
    treeList1.EndCurrentEdit();

    //Update the database's Suppliers table to which oleDBDataAdapter1 is connected
    DoUpdate(oleDbDataAdapter1, dataSet1.Tables["Suppliers"]);
}

public void DoUpdate(DbDataAdapter dataAdapter, System.Data.DataTable dataTable) {
    try {
        dataAdapter.Update(dataTable);
    } catch(Exception ex) {
        MessageBox.Show(ex.Message);
    }
}