Visual Studio Tools for Office: Using C# with Excel, Word, Outlook, and InfoPath [Electronic resources] نسخه متنی

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.

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

Choose Microsoft Access Database File and click Continue to go on to the Connection dialog. The Northwind database file is typically in the Program Files\Microsoft Office\Office11\Samples 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.

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

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.

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.

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.

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.

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.