Walkthrough: Complex Data Binding in a Document-Level Project
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. |
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.
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.
Megjegyzés
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:
Visual Studio Tools for Office (an optional component of Visual Studio 2008 Professional and Visual Studio Team System).
Microsoft Office Excel 2003 or Microsoft Office Excel 2007.
Access to a server with the Northwind SQL Server sample database.
Permissions to read from and write to the SQL Server database.
The first step is to create an Excel Workbook 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 Visual Studio Tools for Office Projects.
Visual Studio opens the new Excel workbook in the designer and adds the My Complex Data Binding project to Solution Explorer.
Use the Data Sources window to add a typed dataset to your project.
If the Data Sources window is not visible, on the Data menu, click Show Data Sources.
Megjegyzés
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 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.
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.
Verify that the My Complex Data Binding.xls 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.
- 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.
Add text to the button in the Startup event handler.
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.
Any changes have been made to the data exist only in the local dataset until they are explicitly saved back 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); } }
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.
Press F5.
Verify that when the workbook opens, the list object is filled with data from the Employees table.
Click cell B7, which should contain the name Davolio.
Type the name Anderson, and then press ENTER.
Click the cell that contains the column header LastName.
Type Last Name, including a space between the two words, and then press ENTER.
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.
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
- Right-click the number 16 (row 16) on the far left side of the worksheet, and then click Delete.
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.
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.
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 Office Solutions.
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.
Walkthrough: Simple Data Binding in a Document-Level Project