Binding Data to User Controls in Excel Using Visual Studio 2005 Tools for the Office System SE

With Microsoft Visual Studio 2005 Tools for Office Second Edition, you can bind data to Office-specific controls called host controls. You bind data to these controls the same way you bind data to Windows Forms controls. This visual how-to topic explains how to bind data to host controls, such as list objects and named ranges, in Microsoft Office Excel 2003. It also explains how to simulate the same behavior using an add-in in Microsoft Office Excel 2007.

The data-binding feature applies only to document-level customizations. Because Visual Studio 2005 Tools for Office SE only supports document-level customizations at design time for Office 2003 solutions, Excel 2003 is used to illustrate the basic use of this feature.

Visual Studio 2005 Tools for Office SE only supports add-in customizations at design time for 2007 Microsoft Office system applications. Because you cannot directly take advantage of the Visual Studio 2005 Tools for Office data-binding feature for 2007 Office system add-ins at design time, this topic explains how to bind to an Excel 2007 ListObject at run time.

You can bind to user controls in an Excel 2003 document-level customization at design time without writing any code. But to simulate the same type of functionality with an add-in in Excel 2007, you must create a procedure to create a list object and bind it to a data source.

Add-in Binding with Excel 2007

The Add method of the ListObjects collection performs the bulk of the work in the BindData() procedure in this example. The Add method takes five parameters:

• SourceType (XlListObjectSourceType)

• Source (Object)

• LinkSource (Object)

• XlListObjectHasHeaders (XlYesNoGuess)

• Destination (Object)

If you are binding to a database, use xlSrcExternal for the SourceTypeparameter and true for the LinkSource parameter.

The Source parameter should be an object array where the first and only element contains the connection string to the database. The format of the connection string is slightly different from traditional ADO.NET connection strings. For example, a connection to SQL Server should look similar to the following.

OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<initial catalog>;Data Source=<your server>

Note

The binding demonstrated is read-only binding.

The following code creates a list object and binds it to a data source.

// This is a method to create a
// database-bound list object.

internal void BindData()
{

   // The source variable holds the 
   // connection string information.
   // It is an object array as required 
   // by the ListObjects.Add method.

   object[] source = new object[1];
   source[0] = 
      (object)Properties.Settings.Default.NorthwindConnectionString;

   Excel.Worksheet ws = (Excel.Worksheet)Application.ActiveSheet;

   // Add a list object to the active cell.

   Excel.ListObject lo = ws.ListObjects.Add( 
      Excel.XlListObjectSourceType.xlSrcExternal,
      source,
      true,
      Excel.XlYesNoGuess.xlGuess,
      this.Application.ActiveCell);

   // Specify the command type and text for the 
   // underlying query table.

   lo.QueryTable.CommandType = Excel.XlCmdType.xlCmdTable;
   lo.QueryTable.CommandText = "Northwind.dbo.Suppliers";

   // Force the list object to refresh with the updated
   // query table properties.

   lo.QueryTable.Refresh(missing);
}

' This is a method to create a database-bound 
' list object.

Friend Sub BindData()

   ' The source variable holds the connection 
   ' string information.
   ' It is an object array as required
   ' by the ListObjects.Add method.

   Dim source(1) As Object
   source(0) = My.Settings.NorthwindConnectionString

   Dim ws As Excel.Worksheet
   ws = Application.ActiveSheet

   ' Add a list object to the active cell.

   Dim lo As Excel.ListObject
   lo = ws.ListObjects.Add( _
      Excel.XlListObjectSourceType.xlSrcExternal, _
      source, _
      True, _
      Excel.XlYesNoGuess.xlGuess, _
      Me.Application.ActiveCell)

   ' Specify the command type and text for the 
   ' underlying query table.

   lo.QueryTable.CommandType = Excel.XlCmdType.xlCmdTable
   lo.QueryTable.CommandText = "Northwind.dbo.Suppliers"

   ' Force the list object to refresh with the 
   ' updated query table properties.

   lo.QueryTable.Refresh()
End Sub

Data binding is a fundamental programming task that involves connecting some property of a control (usually a display-oriented property) to a data element from a data store. The data store might be a database, XML file, or some other entity, including in-memory objects such as arrays.

Types of Data Binding

There are two types of data binding: simple and complex.Simple binding is the ability to bind a property to a single data element. TextBox and Label controls are good examples of controls that are typically associated with simple binding because they are generally only used to display a single value.

Complex binding refers to the ability of a control to bind to more than one data element. This frequently involves binding to multiple records. ListBox or ComboBox controls are good examples of controls that support complex binding.

You can also think about data binding in terms of direction. One-way data binding typically refers to read-only data binding, where a control displays the most recent copy of the data element it is bound to. Two-way data binding is a read/write binding mechanism that transmits changes to the data-bound element back to the database.

Host Controls in Visual Studio 2005 Tools for Office SE

One benefit of using Visual Studio Tools for Office 2005 Second Edition is that it extends data binding to Office solutions by letting you bind data to Word bookmarks, Excel named ranges, and Excel list objects. These objects are called host controls: they are controls that apply to a given application or host. Additionally, with Visual Studio 2005 Tools for Office SE, you can add Windows Forms controls directly onto the surface of an Office document. Of course, these Windows Forms controls also support data binding.

Data binding between a data source and a host control is a two-way data update using Visual Studio 2005 Tools for Office SE. For simple data binding, changes in the data source are automatically reflected in the host control. However, changes in the host control require an explicit call to update the data source. The reason for this behavior is to allow for situations in which additional validation must occur before the changes are transmitted to the database. For example, if you have two separate but related named ranges, you might have to update both named ranges before a valid update can occur. An automatic update would not work in this scenario because it would be triggered immediately after a change to the first named range, before you can make the change to the second named range.

Creating a Project Data Source

The process of binding user controls in Excel 2003 begins with creating a project data source.

To create a data source

The Data Sources window shows a tree view list that shows all datasets that are defined in a project. Under each data set, the tree view shows the tables and fields.

Binding to User Controls with Excel 2003

With a data set defined in the project, creating a bound host item control such as a named range or list object is a drag-and-drop operation. For example, to create a bound named range, highlight the desired field in the Data Sources window and drag it to the worksheet. To create a bound list object, perform the same operation, except select a data table rather than a single field (although you can have single field list objects).

Visual Studio Tools for Office handles all the details involved with creating data-bound items. It adds a typed dataset object, table adapter object, and a binding source to the project. The dataset is responsible for storing disconnected data that is retrieved from a data source. The main purpose of the table adapter is to enable communication between the dataset and a database. Finally, the binding source is responsible for connecting the data source to controls.

One-Way Add-In Binding with Excel 2007

As mentioned earlier, Visual Studio 2005 Tools for Office SE only supports application-level add-ins at design time for 2007 Office system applications. Consequently, the data-binding feature in Visual Studio 2005 Tools for Office SE is unavailable at design time. However, it is easy to provide one-way data binding functionality in Excel add-ins by using Excel list objects. See the Code It section in this topic for implementation details.

One difference between this method and the method that Visual Studio 2005 Tools for Office SE provides for document-level customizations is that the binding is one-way only. To support two-way binding, updates must be handled by custom code.

Watch the video

Length: 00:07:08

Size: 8.9 MB

Type: WMV file

• Office Developer Center on MSDN

• Visual Studio Tools for Office Developer Portal

• Binding Data to Controls Overview

• VSTO 2005 - Enhanced Data Binding Step-by-Step