How to: Populate Worksheets with Data from a Database
Applies to |
|---|
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type
Microsoft Office version
For more information, see Features Available by Application and Project Type. |
You can access data in document-level Visual Studio Tools for Office projects in the same way that you access data in Windows Forms projects. You use the same tools and code to bring the data into your solution, and you can even use Windows Forms controls to display the data. In addition, you can take advantage of controls called host controls, which are native objects in Microsoft Office Excel that have been enhanced with events and data-binding capability. For more information, see Host Items and Host Controls Overview.
Starting in Visual Studio 2008 Service Pack 1 (SP1), you can also access data in application-level projects, but only programmatically at run time.
The following example shows how to add data-bound controls in document-level projects using a designer. For an example of how to add data-bound controls in application-level projects at run time, see Walkthrough: Complex Data Binding in an Application-Level Project.
.gif "link to video")
For a related video demonstration, see How Do I: Transfer Data Into an Excel Worksheet?, and How Do I: Consume Database Data in Excel?.
Adding a Data-Bound Control to a Worksheet at Design Time
To populate a worksheet with data from a database
Open an Excel document-level project in Visual Studio, with the worksheet open in the designer.
Open the Data Sources window and create a data source for your project. For more information, see How to: Connect to Data in a Database.
Drag the field or table you want from the Data Sources window to your worksheet.
One of the following controls is created on the worksheet:
If you drag a field, a NamedRange control is created on the worksheet. For more information, see NamedRange Control.
If you drag a table, a ListObject control is created on the worksheet. For more information, see ListObject Control.
You can add a different control by selecting the table or field in the Data Sources window and then choosing a different control from the drop-down list.
Objects in the Project
In addition to the control, the following data-related objects are automatically added to your project:
A typed dataset that encapsulates the data tables that you connected to in the database. For more information, see Datasets in Visual Studio Overview.
A BindingSource that connects the control to the typed dataset. For more information, see BindingSource Component Overview.
A TableAdapter that connects the typed dataset to the database. For more information, see TableAdapter Overview.
A TableAdapterManager, which is used to coordinate table adapters in the dataset to enable hierarchical updates. For more information, see Hierarchical Update and TableAdapterManager Overview.
When you run the project, the control displays the first record in the data source. You can use the BindingSource to enable users to scroll through the records.
To scroll through the records
- Use BindingSource methods such as MoveNext and MovePrevious.
For information about how to send updates to the typed dataset and the database, see How to: Update a Data Source with Data from a Host Control.
See Also
Tasks
How to: Populate Documents with Data from Objects
How to: Populate Documents with Data from a Database
How to: Populate Documents with Data from Services
How to: Update a Data Source with Data from a Host Control
Concepts
Binding Data to Controls in Office Solutions
Change History
Date |
History |
Reason |
|---|---|---|
July 2008 |
Added information about binding data in application-level add-ins. |
SP1 feature change. |