The Data Sources Window and Visual Studio Tools for Office, Version 2005 Solutions
The Data Sources window -- new to Microsoft Visual Studio 2005 -- combines two features:
- The Toolbox, which enables you to drag controls onto your design surface.
- The Server Explorer, which enables you to connect to a data source and then bind the data to the controls.
With the Data Sources window, you can accomplish all of this in one easy step. Microsoft Visual Studio Tools for the Microsoft Office System, Version 2005, expands the capabilities of the Data Sources window. In addition to dragging data-bound Window Forms controls, you can now drag data-bound view controls onto your document design surface without writing any code.
Here's how it works.
When I create a Visual Studio Tools for Office, Version 2005 project, the Data Sources window enables me to easily connect to my data source.
There are several data source types to choose from: a database server, a single-file database, a Web service, or a code component that encapsulates data access and exposes data fields as properties in code.
For this example, I'll choose a Microsoft SQL Server.
After I connect to the data source (in this case, the Northwind database), I can choose the items that I'm interested in programming against, for example, the Customers table.
Now the Data Sources window is able to query the data source and map controls to data in the data source.
I can drag a NamedRange view control, data bound to the CompanyName field, to cell B2 and drag a ListObject view control, data bound to the Customers table, to cell B4.
Note If you ever want to change the control type before you drag it, simply click the table or field in the Data Sources window, click the drop-down arrow, and select the replacement control type.
As I drag the NamedRange and ListObject view controls onto the worksheet, Visual Studio Tools for Office, Version 2005 creates a bunch of programmable fields to code against. Let's take a look at some the code that's autogenerated to see what's available to us programmatically.
private Microsoft.Office.Tools.Excel.NamedRange NamedRange1;
private Microsoft.Office.Tools.Excel.ListObject List1;
The code declares a NamedRange field named NamedRange1 (in cell B2) and a ListObject field named List1 (in cell B4).
private ExcelApplication1.NorthwindDataSet northwindDataSet;
private System.Windows.Forms.DataConnector customersDataConnector;
private ExcelApplication1.CustomersTableAdapter customersTableAdapter;
The underlying dataset that holds the data coming from the Northwind database is declared in a field named northwindDataSet. A new ADO.NET object called a DataConnector enables a convenient way to rebind data-bound objects (in our case, view controls) at once instead of rebinding them one at a time directly to another data source. The customersDataConnector field provides the strongly-typed DataConnector for this purpose.
A new ADO.NET object called a TableAdapter is basically a strongly-typed version of the existing ADO.NET DataAdapter object. The customersTableAdapter field is then basically a DataAdapter object that is strongly typed to work with the Customers table.
this.NamedRange1 = new Microsoft.Office.Tools.Excel.NamedRange(...);
this.List1 = new Microsoft.Office.Tools.Excel.ListObject(...);
The NamedRange1 and List1 fields are initialized.
if ((this.northwindDataSet == null))
{
this.northwindDataSet = new ExcelApplication1.NorthwindDataSet();
}
this.customersDataConnector = new System.Windows.Forms.DataConnector(...);
this.customersTableAdapter = new ExcelApplication1.CustomersTableAdapter();
If the northwindDataSet doesn't already exist as a cached data island, the northwindDataSet field is initialized. Then the customersDataConnector and customersTableAdapter fields are initialized.
Note If you want to cache a dataset in the document for offline use, select northwindDataSet in the Properties window, then change the Modifiers property to Public and the CacheInDocument property to True.
this.northwindDataSet.DataSetName = "NorthwindDataSet";
this.customersDataConnector.DataMember = "Customers";
this.customersDataConnector.DataSource = this.northwindDataSet;
The northwindDataSet1 field is given the name NorthwindDataSet. The customersDataConnector field is assigned to the northwindDataSet field as its data source and the Customers table as its data member.
this.NamedRange1.DataBindings.Add("Value2",
this.customersDataConnector, "CompanyName", true);
this.List1.SetDataBinding(this.customersDataConnector, "");
The NamedRange1 field is data bound to the Customer table's CompanyName field. The List1 field is data bound to the Customer table via the DataConnector object.
By the way, the preceding code is available in the autogenerated partial class file; in this case it's a hidden code module named Sheet1.designer.cs. You can view it by clicking Show All Files in Solution Explorer -- just make sure not to modify any of the code in the file. At compile time, the partial class Sheet1 in Sheet1.cs is merged with the partial class Sheet1 in Sheet1.designer.cs to make a single class Sheet1. (Additional strongly-typed ADO.NET helper fields and members are created in the hidden NorthwindDataSet.Designer.cs code module. You can explore the code, but make sure not to modify any of the code in the file.)
Now, the only line of code I have left is one that fills the northwindDataSet with the data. Fortunately, this code has automatically been generated for me in the Sheet1.cs file's Sheet1_Startup event handler:
this.customersTableAdapter1.Fill(this.northwindDataSet1.Customers);
All I need to do is press F5 to run the project, and my data appears.
In future posts, we'll do things like extend this type of solution to integrate the actions pane, offline data caching, server-based solutions, and so on.
Thanks to Eric Carter, Kathleen McGrath, and Harry Miller for their help with this post.
-- Paul Cornell
-----
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.