Simple data binding exists when a control property is bound to a single data element, such as a value in a data table. For example, the NamedRange control has a Value2 property that can be bound to a field in a dataset. When the field in the dataset changes, the value in the named range also changes. All host controls, except for the XMLNodes control, support simple data binding. The XMLNodes control is a collection, and therefore it does not support data binding.

To perform simple data binding to a host control, add a Binding to the DataBindings property of the control. A Binding object represents the simple binding between a property value of the control and the value of a data element.

The following example demonstrates how to bind the Value2 property to a data element in a document-level project. This code example is part of a larger example that is provided for the DataBindings property.

Dim binding1 As New Binding("Value2", ds, "Customers.Names", True)
namedRange1.DataBindings.Add(binding1)

Binding binding1 = new Binding("Value2", ds, "Customers.Names", true);
namedRange1.DataBindings.Add(binding1);

For walkthroughs that demonstrates simple data binding, see Walkthrough: Simple Data Binding in a Document-Level Project for a document-level project and Walkthrough: Simple Data Binding in an Application-Level Project for an application-level project.

Complex data binding exists when a control property is bound to more than one data element, such as multiple columns in a data table. The ListObject control for Excel is the only host control that supports complex data binding. There are also many Windows Forms controls that support complex data binding, such as the DataGridView control.

To perform complex data binding, set the DataSource property of the control to a data source object that is supported by complex data binding. For example, the DataSource property of the ListObject control can be bound to multiple columns in a data table. All of the data in the data table appears in the ListObject control, and as the data in the data table changes, the ListObject also changes. For a list of the data sources that you can use for complex data binding, see Data Sources Supported by Windows Forms.

The following code example creates a DataSet with two DataTable objects and populates one of the tables with data. The code then binds the ListObject to the table that contains data. This example is for an Excel document-level project.

Private Sub ListObject_DataSourceAndMember()
    ' Create a DataSet and two DataTables.
    Dim ordersDataSet As New DataSet("ordersDataSet")
    Dim tableCustomers As New DataTable("Customers")
    Dim tableProducts As New DataTable("Products")
    ordersDataSet.Tables.Add(tableCustomers)
    ordersDataSet.Tables.Add(tableProducts)

    ' Add a data to the Customers DataTable.
    tableCustomers.Columns.Add(New DataColumn("LastName"))
    tableCustomers.Columns.Add(New DataColumn("FirstName"))
    Dim dr As DataRow = tableCustomers.NewRow()
    dr("LastName") = "Chan"
    dr("FirstName") = "Gareth"
    tableCustomers.Rows.Add(dr)

    ' Create a list object.
    Dim List1 As Microsoft.Office.Tools.Excel.ListObject = _
        Me.Controls.AddListObject(Me.Range( _
        "A1"), "Customers")

    ' Bind the list object to the Customers table.
    List1.AutoSetDataBoundColumnHeaders = True
    List1.DataSource = ordersDataSet
    List1.DataMember = "Customers"

End Sub

private void ListObject_DataSourceAndMember()
{
    // Create a DataSet and two DataTables.
    DataSet ordersDataSet = new DataSet("ordersDataSet");
    DataTable tableCustomers = new DataTable("Customers");
    DataTable tableProducts = new DataTable("Products");
    ordersDataSet.Tables.Add(tableCustomers);
    ordersDataSet.Tables.Add(tableProducts);

    // Add a data to the Customers DataTable.
    tableCustomers.Columns.Add(new DataColumn("LastName"));
    tableCustomers.Columns.Add(new DataColumn("FirstName"));
    DataRow dr = tableCustomers.NewRow();
    dr["LastName"] = "Chan";
    dr["FirstName"] = "Gareth";
    tableCustomers.Rows.Add(dr);

    // Create a list object.
    Microsoft.Office.Tools.Excel.ListObject list1 = 
        this.Controls.AddListObject(
        this.Range["A1", missing], "Customers");

    // Bind the list object to the Customers table.
    list1.AutoSetDataBoundColumnHeaders = true;
    list1.DataSource = ordersDataSet;
    list1.DataMember = "Customers";
}

For walkthroughs that demonstrate complex data binding, see Walkthrough: Complex Data Binding in a Document-Level Project for a document-level project and Walkthrough: Complex Data Binding in an Application-Level Project for an application-level project.

In document-level projects, you can use the Data Sources window to add data-bound controls to your documents or workbooks easily, the same way you use it for Windows Forms. For more information about using the Data Sources window, see Displaying Data Overview and Data Sources Window.

Dragging Controls from the Data Sources Window

Binding Data in Document-Level Projects at Design Time

Binding Data in Application-Level Projects

Data binding between a data source and a host control involves a two-way data update. In simple data binding, changes in the data source are reflected automatically in the host control, but changes in the host control require an explicit call to update the data source. The reason is that in some cases, changes in one data-bound field are not accepted unless they are accompanied by changes in another data-bound field. For example, you might have two fields, one for age and one for years of experience. Experience cannot exceed age. A user cannot update the age from 50 to 25 and then the experience from 30 to 10 unless he or she makes the changes at the same time. To solve this problem, fields with simple data binding are not updated until the updates are explicitly sent by code.

To update a data source from host controls that enable simple data binding, you must send updates to the in-memory data source (such as a DataSet or DataTable) and to the back-end database, if your solution uses one.

You do not need to explicitly update the in-memory data source when you perform complex data binding using the ListObject control. In that case, changes are automatically sent to the in-memory data source without additional code.

For more information, see How to: Update a Data Source with Data from a Host Control.

Tasks

Concepts

Other Resources