Walkthrough: Simple Data Binding in a Document-Level Project
This walkthrough demonstrates the basics of data binding in a document-level project. A single data field in a SQL Server database is bound to a named range in Microsoft Office Excel. The walkthrough also shows how to add controls that enable you to scroll through all the records in the table.
Applies to: The information in this topic applies to document-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:
Creating a data source for an Excel project.
Adding controls to a worksheet.
Scrolling through database records.
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.
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.
Excel 2007 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.
In this step, you will create an Excel workbook project.
To create a new project
Create an Excel workbook project with the name My Simple Data Binding, using either Visual Basic or C#. Make sure that Create a new document is selected. 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 Simple Data Binding project to Solution Explorer.
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, click Show Data Sources on the Data menu.
If Show Data Sources is not available, click inside the Excel workbook and then check again.
Click 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 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 Customers table.
The wizard adds the Customers table to the Data Sources window. It also adds a typed dataset to your project that is visible in Solution Explorer.
For this walkthrough, you need two named ranges and four buttons on the first worksheet. First, add the two named ranges from the Data Sources window so that they are automatically bound to the data source. Next, add the buttons from the Toolbox.
To add two named ranges
Verify that the My Simple Data Binding.xls workbook is open in the Visual Studio designer, with Sheet1 displayed.
Open the Data Sources window and expand the Customers node.
Select the CompanyName column, and then click the drop-down arrow that appears.
Select NamedRange in the drop-down list, and then drag the CompanyName column to cell A1.
A NamedRange control named companyNameNamedRange is created in cell A1. At the same time, a BindingSource named customersBindingSource, 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.
Select the CustomerID column in the Data Sources window, and then click the drop-down arrow that appears.
Click NamedRange in the drop-down list, and then drag the CustomerID column to cell B1.
To add four buttons
From the Common Controls tab of the Toolbox, add a Button control to cell A3 of the worksheet.
This button is named Button1.
Add three more buttons to the following cells in this order, so that the names are as shown:
The next step is to add text to the buttons, and in C# add event handlers.
Set the button text and add event handlers during the Startup event.
To initialize the controls
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 each button.
For C# only, add event handlers for the button click events to the Sheet1_Startup method.
Now add code to handle the Click events of the buttons so that the user can browse through the records.
Add code to the Click event handler of each button to move through the records.
To move to the first record
Add an event handler for the Click event of the Button1 button, and add the following code to move to the first record:
To move to the previous record
Add an event handler for the Click event of the Button2 button, and add the following code to move the position back by one:
To move to the next record
Add an event handler for the Click event of the Button3 button, and add the following code to advance the position by one:
To move to the last record
Now you can test your workbook to make sure that you can browse through the records in the database.
To test your workbook
Press F5 to run your project.
Confirm that the first record appears in cells A1 and B1.
Click the > (Button3) button and confirm that the next record appears in cell A1 and B1.
Click the other scroll buttons to confirm that the record changes as expected.
This walkthrough shows the basics of binding a named range to a field in a database. 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.
Bind cells to multiple columns in a table, instead of to one field. For more information, see Walkthrough: Complex Data Binding in a Document-Level Project.
Use a BindingNavigator control to scroll through the records. For more information, see How to: Navigate Data with the Windows Forms BindingNavigator Control.