Creating a Data-Bound Customized Spreadsheet with VSTO

Creating a no-frills data-bound customized document using the VSTO designer requires no coding but a whole lot of mouse clicking. What we are going to do is first tell Visual Studio about a data sourcein this case, the Northwind sample database that comes with Officeand then drag and drop some data-bound controls onto the spreadsheet.

Defining a Data Source

Let's start up Visual Studio and create a new Excel project. From Visual Studio's Data menu, choose Show Data Sources to display the Data Sources pane. Click Add New Data Source to start the Data Source Wizard.

Figure 17-1. Starting up the Data Source Wizard.

Choose Database and click Next. Click New Connection. A second wizard will appear.

Figure 17-2. Starting up the Data Source Wizard.

Choose Microsoft Access Database File and click Continue to go on to the Connection dialog. The Northwind database file is typically in the Program FilesMicrosoft OfficeOffice11Samples directory. Click Browse and find the Northwind database. No security is enforced on this database file, so the default username Admin and a blank password are fine.

Figure 17-3. Creating the database connection.

Click OK to close the Connection Wizard and continue with the Data Source Wizard.

Figure 17-4. Viewing the connection string.

When you click Next, Visual Studio notes that you are creating a connection to a local database file that is not part of the current project. If you want this project to have its own copy of the database rather than modifying the original, you can do so and Visual Studio will automatically update the connection to point to the new location. In this first example, we do not have any reason to make a copy of the database, so click No.

As you can see, all the information about the database connection that you have just created is saved in a connection string. For both convenience and security, it is a good idea to save that connection string in a configuration file rather than hard-coding it into your program. Again, see the section below on security best practices for more details.

Figure 17-5. Save the connection string in the application configuration file.

The database to which we are connecting might have an enormous number of queries, tables, and columns within those tables and so on. To manage some of this complexity, Visual Studio enables you to choose which portions of the database will display in Visual Studio. Let's select the entire Suppliers table and the ProductName, SupplierId, QuantityPerUnit and UnitPrice columns from the Products table.

Figure 17-6. Choose your tables.

Finally, click Finish to exit the Data Source Wizard.

Creating Data-Bound Controls the Easy Way

The Data Sources window now contains an entry for the NorthwindDataSet. (Why dataset rather than database? We explain what exactly we mean by dataset later on in this chapter.) Expand the nodes in the tree view.

Figure 17-7. The Data Sources pane contains the dataset tree view.

Notice a few interesting things here. First, Visual Studio has discovered from the database that the Products table has a relationship with the Suppliers table; the Products table appears both as a table in its own right, and as a child node of the Suppliers table. This will allow us to more easily create master-detail views.

Second, notice that the icons for the columns have "named range" icons, indicating that if you drag and drop the icon onto the worksheet, you will get a data-bound named range to this column. The default for a column is a named range, and the default for an entire table is a list object, but you can choose other controls by clicking the item and selecting a drop-down. Suppose we want to have a combo box bound to the CompanyName, for instance. You can choose ComboBox from the drop-down as the control to use for CompanyName, as shown in Figure 17-8.

Figure 17-8. Choosing the control type.

Drag the CompanyName as a combo box, the ContactName as a named range, and the entire Products table onto the worksheet. Use the Products table that is the child of the Suppliers table in the tree view and we will get a nice master-detail view.

Drag the CompanyName as a combo box, the ContactName as a Bookmark, and the entire Products table as a data grid. Use the Products table that is the child of the Suppliers table in the tree view and we will get a nice master-detail view.

A whole lot of stuff has magically appeared in the component tray below the Excel designer: a dataset, two binding sources, and two table adapters. We get into the details of what these components are for later in this chapter. For now, compile and run the application. Without writing a single line of code, we have gotten a data-bound master-detail view on an Excel spreadsheet. As you select different items from the combo box, the named range and list object automatically update themselves.

Figure 17-9. Creating the data-bound view.

Figure 17-10. A data-bound master-detail spreadsheet.