Posting Data to a Connected Database in ADO.NET

• Oct 29, 2020
• 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.

• C#
• VB.NET

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);
    }
}

Imports DevExpress.XtraGrid
Imports DevExpress.XtraGrid.Views.Base
Imports System.Data.Common
'...
Public Sub UpdateDatasource(ByVal treeList1 As TreeList)
    'Save the latest changes to the bound DataTable
    treeList1.PostEditor();
    treeList1.EndCurrentEdit();

    'Update the database's Suppliers table to which oleDBDataAdapter1 is connected
    DoUpdateTable(oleDbDataAdapter1, dataSet1.Tables("Suppliers"))
End Sub

Public Sub DoUpdateTable(ByVal dataAdapter As DbDataAdapter, _ 
ByVal dataTable As System.Data.DataTable)
    Try
        dataAdapter.Update(dataTable)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub