Binding Data to Controls in Office Solutions

You can bind Windows Forms controls and host controls on a Microsoft Office Word document or Microsoft Office Excel worksheet to a data source so the controls automatically display the data. You can bind data to controls in both application-level and document-level projects.

Applies to: The information in this topic applies to document-level projects and application-level projects for Microsoft Office 2010 and the 2007 Microsoft Office system. For more information, see Features Available by Office Application and Project Type.

Host controls extend objects that are in the Word and Excel object models, such as content controls in Word and named ranges in Excel. For more information, see Host Items and Host Controls Overview.

Both Windows Forms and host controls use the Windows Forms data binding model, which supports both simple data binding and complex data binding to data sources such as datasets and data tables. For complete information about the data binding model in Windows Forms, see Data Binding and Windows Forms.

For a related video demonstration, see How Do I: Consume Database Data in Excel?.

Simple Data Binding

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

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.

Displaying Data in Documents and Workbooks

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 Binding Windows Forms Controls to Data in Visual Studio and Data Sources Window.

Dragging Controls from the Data Sources Window

A control is created on the document when you drag an object onto it from the Data Sources window. The type of control that is created depends on whether you are binding a single column of data or multiple columns of data.

For Excel, a NamedRange control is created on the worksheet for each individual field, and a ListObject control is created for each data range that includes multiple rows and columns. You can change this default by selecting the table or field in the Data Sources window and then choosing a different control from the drop-down list.

A ContentControl control is added to documents. The type of content control depends on the data type of the field that you selected.

Binding Data in Document-Level Projects at Design Time

The following topics show examples of binding data at design time:

• How to: Populate Worksheets with Data from a Database

• How to: Populate Documents with Data from a Database

• How to: Populate Documents with Data from Objects

• How to: Populate Documents with Data from Services

• How to: Scroll Through Database Records in a Worksheet

Binding Data in Application-Level Projects

In application-level projects, you can add controls only at run time. The following topics show examples of binding data at run time:

• Walkthrough: Simple Data Binding in an Application-Level Project

• Walkthrough: Complex Data Binding in an Application-Level Project

Updating Data That Is Bound to Host Controls

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.

See Also

Tasks

How to: Create a Simple-Bound Control on a Windows Form

How to: Update Data by Using a TableAdapter

Concepts

Data Binding and Windows Forms

Binding Windows Forms Controls to Data in Visual Studio

Saving Data in Datasets

Caching Data

Other Resources

How Do I: Consume Database Data in Excel?

Concurrency Control in ADO.NET

Data in Office Solutions