Walkthrough: Complex Data Binding in a Document-Level Project

Article
04/28/2015

This walkthrough demonstrates the basics of complex data binding in a document-level project. You can bind multiple cells in a Microsoft Office Excel worksheet to fields in the Northwind SQL Server database.

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

This walkthrough illustrates the following tasks:

Adding a data source to your workbook project.
Adding data-bound controls to a worksheet.
Saving data changes back to the database.

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 Customizing Development Settings in Visual Studio.

Prerequisites

You need the following components to complete this walkthrough:

An edition of Visual Studio 2013 that includes the Microsoft Office developer tools. For more information, see Configuring a Computer to Develop Office Solutions.
Excel 2013 or Excel 2010.
Access to a server with the Northwind SQL Server sample database.
Permissions to read from and write to the SQL Server database.

Creating a New Project

The first step is to create an Excel workbook project.

To create a new project

Create an Excel workbook project with the name My Complex Data Binding. In the wizard, select Create a new document.

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

Visual Studio opens the new Excel workbook in the designer and adds the My Complex Data Binding project to Solution Explorer.

Creating the Data Source

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

To create the data source

If the Data Sources window is not visible, display it by, on the menu bar, choosing View, Other Windows, Data Sources.
Choose Add New Data Source to start the Data Source Configuration Wizard.
Select Database and then click Next.
Select a data connection to the Northwind sample SQL Server database, or add a new connection by using the New Connection button.
After a connection has been selected or created, click Next.
Clear the option to save the connection if it is selected, and then click Next.
Expand the Tables node in the Database objects window.
Select the check box next to the Employees table.
Click Finish.

The wizard adds the Employees table to the Data Sources window. It also adds a typed dataset to your project that is visible in Solution Explorer.

Adding Controls to the Worksheet

A worksheet will display the Employees table when the workbook is opened. Users will be able to make changes to the data and then save those changes back to the database by clicking a button.

To bind the worksheet to the table automatically, you can add a ListObject control to the worksheet from the Data Sources window. To give the user the option to save changes, add a Button control from the Toolbox.

To add a list object

Verify that the My Complex Data Binding.xlsx workbook is open in the Visual Studio designer, with Sheet1 displayed.
Open the Data Sources window and select the Employees node.
Click the drop-down arrow that appears.
Select ListObject in the drop-down list.
Drag the Employees table to cell A6.

A ListObject control named EmployeesListObject is created in cell A6. At the same time, a BindingSource named EmployeesBindingSource, a table adapter, and a DataSet instance are added to the project. The control is bound to the BindingSource, which in turn is bound to the DataSet instance.

To add a button

From the Common Controls tab of the Toolbox, add a Button control to cell A4 of the worksheet.

The next step is to add text to the button when the worksheet opens.

Initializing the Control

Add text to the button in the Startup event handler.

To initialize the control

In Solution Explorer, right-click Sheet1.vb or Sheet1.cs, and then click View Code on the shortcut menu.
Add the following code to the Sheet1_Startup method to set the text for the button.
```
Me.Button1.Text = "Save"
```
```
this.button1.Text = "Save";
```
For C# only, add an event handler for the Click event to the Sheet1_Startup method.
```
this.button1.Click += new EventHandler(button1_Click);
```

Now add code to handle the Click event of the button.

Saving Changes to the Database

Any changes have been made to the data exist only in the local dataset until they are explicitly saved back to the database.

To save changes to the database

Add an event handler for the Click event of the button, and add the following code to commit all changes that have been made in the dataset back to the database.

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Button1.Click

    Try 
        Me.EmployeesTableAdapter.Update(Me.NorthwindDataSet.Employees)

    Catch ex As System.Data.DataException
        MessageBox.Show(ex.Message)
    End Try 
End Sub

private void button1_Click(object sender, EventArgs e)
{
    try
    {
        this.employeesTableAdapter.Update(this.northwindDataSet.Employees);
    }
    catch (System.Data.DataException ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Testing the Application

Now you can test your workbook to verify that the data appears as expected, and that you can manipulate the data in the list object.

To test the data binding

Press F5.

Verify that when the workbook opens, the list object is filled with data from the Employees table.

To modify data

Click cell B7, which should contain the name Davolio.
Type the name Anderson, and then press ENTER.

To modify a column header

Click the cell that contains the column header LastName.
Type Last Name, including a space between the two words, and then press ENTER.

To save data

Click Save on the worksheet.
Exit Excel. Click No when prompted to save the changes you made.
Press F5 to run the project again.

The list object is filled with data from the Employees table.
Notice that the name in cell B7 is still Anderson, which is the data change that you made and saved back to the database. The column header LastName has changed back to its original form with no space, because the column header is not bound to the database and you did not save the changes you made to the worksheet.

To add new rows

Select a cell inside the list object.

A new row appears at the bottom of the list, with an asterisk (*) in the first cell of the new row.
Add the following information in the empty row.

EmployeeID

LastName

FirstName

Title

10

Ito

Shu

Sales Manager

EmployeeID	LastName	FirstName	Title
10	Ito	Shu	Sales Manager

To delete rows

Right-click the number 16 (row 16) on the far left side of the worksheet, and then click Delete.

To sort the rows in the List

Select a cell inside the list.

Arrow buttons appear in each column header.
Click the arrow button in the Last Name column header.
Click Sort Ascending.

The rows are sorted alphabetically by last names.

To filter information

Select a cell inside the list.
Click the arrow button in the Title column header.
Click Sales Representative.

The list shows only those rows that have Sales Representative in the Title column.
Click the arrow button in the Title column header again.
Click (All).

Filtering is removed and all the rows appear.

Next Steps

This walkthrough shows the basics of binding a table in a database to a list object. Here are some tasks that might come next:

Cache the data so that it can be used offline. For more information, see How to: Cache Data for Use Offline or on a Server.
Deploy the solution. For more information, see Deploying an Office Solution.
Create a master/detail relation between a field and a table. For more information, see Walkthrough: Creating a Master/Detail Relation Using a Cached Dataset.

Partager via