Walkthrough: Complex Data Binding in an Application-Level Project

You can bind data to host controls and Windows Forms controls in application-level projects. This walkthrough demonstrates how to add controls to a Microsoft Office Excel worksheet and bind the controls to data at run time.

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

This walkthrough illustrates the following tasks:

  • Adding a ListObject control to a worksheet at run time.

  • Creating a BindingSource that connects the control to an instance of a dataset.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Prerequisites

You need the following components to complete this walkthrough:

-

An edition of Visual Studio 2010 that includes the Microsoft Office developer tools. For more information, seeĀ [Configuring a Computer to Develop Office Solutions](bb398242\(v=vs.100\).md).

Creating a New Project

The first step is to create an Excel add-in project.

To create a new project

  • Create an Excel add-in project with the name Populating Worksheets from a Database, using either Visual Basic or C#.

    For more information, see How to: Create Office Projects in Visual Studio.

    Visual Studio opens the ThisAddIn.vb or ThisAddIn.cs file and adds the Populating Worksheets from a Database project to Solution Explorer.

Creating a Data Source

Use the Data Sources window to add a typed dataset to your project.

To add a typed dataset to the project

  1. On the Data menu, click Add New Data Source.

    The Data Source Configuration Wizard opens.

  2. Click Database, and then click Next.

  3. If you have an existing connection to the AdventureWorksLT database, choose this connection and click Next.

    Otherwise, click New Connection, and use the Add Connection dialog box to create the new connection. For more information, see How to: Connect to Data in a Database.

  4. In the Save the Connection String to the Application Configuration File page, click Next.

  5. In the Choose Your Database Objects page, expand Tables and select Address (SalesLT).

  6. Click Finish.

    The AdventureWorksLTDataSet.xsd file is added to Solution Explorer. This file defines the following items:

    • A typed dataset named AdventureWorksLTDataSet. This dataset represents the contents of the Address (SalesLT) table in the AdventureWorksLT database.

    • A TableAdapter named AddressTableAdapter. This TableAdapter can be used to read and write data in the AdventureWorksLTDataSet. For more information, see TableAdapter Overview.

    You will use both of these objects later in this walkthrough.

Creating Controls and Binding Controls to Data

For this walkthrough, the ListObject control displays all the data in the table you selected as soon as the user opens the workbook. The list object uses a BindingSource to connect the control to the database.

For more information about binding controls to data, see Binding Data to Controls in Office Solutions.

To add the list object, dataset, and table adapter

  1. In the ThisAddIn class, declare the following controls to display the Address table of the AdventureWorksLTDataSet dataset.

    Private addressListObject As Microsoft.Office.Tools.Excel.ListObject
    Private adventureWorksDataSet As AdventureWorksLTDataSet
    Private addressTableAdapter As AdventureWorksLTDataSetTableAdapters.AddressTableAdapter
    Private addressBindingSource As System.Windows.Forms.BindingSource
    
    private Microsoft.Office.Tools.Excel.ListObject addressListObject;
    private AdventureWorksLTDataSet adventureWorksDataSet;
    private AdventureWorksLTDataSetTableAdapters.AddressTableAdapter addressTableAdapter;
    private System.Windows.Forms.BindingSource addressBindingSource;
    
  2. In the ThisAddIn_Startup method, add the following code to initialize the dataset and fill the dataset with information from the AdventureWorksLTDataSet dataset.

    Me.addressTableAdapter = New AdventureWorksLTDataSetTableAdapters.AddressTableAdapter()
    Me.adventureWorksDataSet = New AdventureWorksLTDataSet()
    Me.addressTableAdapter.Fill(Me.adventureWorksDataSet.Address)
    Me.addressBindingSource = New System.Windows.Forms.BindingSource()
    
    this.addressTableAdapter = new AdventureWorksLTDataSetTableAdapters.AddressTableAdapter();
    this.adventureWorksDataSet = new AdventureWorksLTDataSet();
    this.addressTableAdapter.Fill(this.adventureWorksDataSet.Address);
    this.addressBindingSource = new System.Windows.Forms.BindingSource();
    
  3. Add the following code to the ThisAddIn_Startup method. This generates a host item that extends the worksheet. For more information, see Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time.

    Note

    The following example works in projects that target .NET Framework 4. To use this example in projects that target .NET Framework 3.5, see the comments in the code.

    Dim worksheet As Excel.Worksheet = DirectCast(Me.Application.ActiveWorkbook.Worksheets(1), Excel.Worksheet)
    
    ' Create a workhseet host item for .NET Framework 4 projects.
    Dim extendedWorksheet As Worksheet = Globals.Factory.GetVstoObject(worksheet)
    
    ' For .NET Framework 3.5 projects, use the following code to create a worksheet host item.
    ' Dim extendedWorksheet As Worksheet = worksheet.GetVstoObject()
    
    Excel.Worksheet worksheet = (Excel.Worksheet)this.Application.ActiveWorkbook.Worksheets[1];
    // Create a workhseet host item for .NET Framework 4 projects.
    Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(worksheet);
    
    // For .NET Framework 3.5 projects, use the following code to create a worksheet host item.
    // Worksheet extendedWorksheet = worksheet.GetVstoObject();
    
  4. Create a range and add the ListObject control.

    Dim cell As Excel.Range = extendedWorksheet.Range("$A$1:$G$5", System.Type.Missing)
    Me.addressListObject = extendedWorksheet.Controls.AddListObject(cell, "list1")
    
    Excel.Range cell = extendedWorksheet.Range["$A$1:$G$5", System.Type.Missing];
    this.addressListObject = extendedWorksheet.Controls.AddListObject(cell, "list1");
    
  5. Bind the list object to AdventureWorksLTDataSet by using the BindingSource. Pass in the names of the columns you want to bind to the list object.

    Me.addressBindingSource.DataSource = Me.adventureWorksDataSet.Address
    Me.addressListObject.AutoSetDataBoundColumnHeaders = True
    Me.addressListObject.SetDataBinding( _
        Me.addressBindingSource, "", "AddressID", "AddressLine1", _
        "AddressLine2", "City", "StateProvince", "CountryRegion", "PostalCode")
    
    this.addressBindingSource.DataSource = this.adventureWorksDataSet.Address;
    this.addressListObject.AutoSetDataBoundColumnHeaders = true;
    this.addressListObject.SetDataBinding(
        this.addressBindingSource, "", "AddressID", "AddressLine1", 
        "AddressLine2", "City", "StateProvince", "CountryRegion", "PostalCode");
    

Testing the Add-In

When you open Excel, the ListObject control displays the data from the Address table of the AdventureWorksLTDataSet dataset.

To test the add-in

  • Press F5.

    A ListObject control named addressListObject is created in the worksheet. At the same time, a dataset object named adventureWorksLTDataSet and a BindingSource named addressBindingSource are added to the project. The ListObject is bound to the BindingSource, which in turn is bound to the dataset object.

See Also

Tasks

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 Services

How to: Populate Documents with Data from Objects

How to: Scroll Through Database Records in a Worksheet

How to: Update a Data Source with Data from a Host Control

Walkthrough: Simple Data Binding in a Document-Level Project

Walkthrough: Complex Data Binding in a Document-Level Project

Reference

BindingSource Component Overview

Concepts

Using Local Database Files in Office Solutions Overview

Data Sources Overview

Binding Windows Forms Controls to Data in Visual Studio

Using Local Database Files in Office Solutions Overview

Connecting to Data in Windows Forms Applications

Other Resources

Data in Office Solutions

Binding Data to Controls in Office Solutions