Bind data to controls in Office solutions

Applies to: Visual Studio Visual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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 VSTO Add-in projects. 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 bind and Windows Forms.

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.

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 VSTO Add-in project for a VSTO Add-in 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 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"], "Customers");

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

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

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 VSTO Add-in project for a VSTO Add-in project.

Display 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 Bind Windows Forms controls to data in Visual Studio and Add new data sources.

Drag 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.

Bind 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

Bind data in VSTO Add-in projects

In VSTO Add-in 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 VSTO Add-in project

• Walkthrough: Complex data binding in VSTO Add-in project

Update 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

• Data binding and Windows Forms
• How to: Create a simple-bound control on a Windows Form
• Bind Windows Forms controls to data in Visual Studio
• Save data back to the database
• Update data by using a TableAdapter
• Cache data
• Data in Office solutions