Binding-Related Extensions to Host Items and Host Controls

All data-bindable host items and host controls allow you to bind any single datum to any writable property. These objects implement IBindableComponent, which defines two properties:

• Property BindingContext As BindingContext

• ReadOnly Property DataBindings As ControlBindings- Collection

Typically, you will have only one binding context. Should you need to have two controls bound to the same list data source, but with different currency for each, you can create new binding contexts and assign them to the controls as you want. Each host item and host control will raise a BindingContextChanged event if you do.

The ControlBindingsCollection object has many methods for adding and removing binding objects; there is one binding for each bound property on the control. It also has a read-only indexer that maps the name of a property to its binding object.

The list object aggregate in Excel has a large number of new properties, methods, and events added on to support complex data binding. We described the view extensions earlier; now that we have covered how data binding works, we can discuss the data model extensions.

New Data-Related List Object Host Control Properties and Methods

The two most important properties on the ListObject host control determine what data source is actually complex-data-bound to the control:

   Public Property DataSource As Object    Public Property DataMember As String 

The reason that the list object divides this information into two properties is because some data sources contain multiple lists, called members. You could set the DataSource property to a dataset, for example, and the DataMember property to the name of a data table contained by the dataset.

The properties can be set in any order, and binding will not commence until both are set to sensible values. It usually is easier, however, to use one of the SetDataBinding methods to set both properties at the same time:

Public Sub SetDataBinding(ByVal dataSource As Object) Public Sub SetDataBinding(ByVal dataSource As Object, _   ByVal dataMember As String) Public Sub SetDataBinding(ByVal dataSource As Object, _   ByVal dataMember As String, _   ByVal ParamArray mappedColumns As String()) 

Notice that in the last overload, you can specify which columns in the data table are to be bound. Doing so proves quite handy if you have a large, complicated table that you want to display only a portion of, or if you want to change the order in which the columns display.

In some cases, the data source needs no further qualification by a data member, so you can leave it blank. In the preceding example, the designer automatically generates code that creates a BindingSource proxy object, which needs no further qualification. The generated code looks something like the code in Listing 17.7.

Listing 17.7. Setting up the Binding Source

Me.OrderBookBindingSource = New System.Windows.Forms.BindingSource() Me.OrderBookBindingSource.DataMember = "Book" Me.OrderBookBindingSource.DataSource = this.orderDataSet1 Me.BookList.SetDataBinding(Me.OrderBookBindingSource, "",   "Title", "ISBN", "Price") 

Because the binding source knows what table to proxy, the list object needs no further qualification.

Note

Unlike the DataGrid control, the list object does not allow you to set the bound columns using a column chooser in the list object's Properties pane. If you have a data-bound list object in the designer, however, you can simply delete columns at design time; Visual Studio will update the automatically generated code so that the deleted column is no longer bound when the code runs.

The information about which columns and tables are bound to which list objects is persisted in the document; you do not need to rebind the list objects explicitly every time the customization starts. Should you want to ensure that all the persisted information about the data bindings is cleared from the document, you can call the ResetPersistedBindingInformation method:

   Public Sub ResetPersistedBindingInformation() 

The data source of the list object must implement either IList or IList-Source. Should you pass an invalid object when trying to set the data source, the list object will throw a SetDataBindingFailedException (as described later in this chapter).

You can check whether the data source and data members have been set properly and the list object is complex-data-bound by checking the IsBinding property:

  Public ReadOnly Property IsBinding As Boolean 

Complex-data-bound list objects keep the currencythe selected row in the currency manager for the data sourcein sync with the selected row in the host. You can set or get the currency of the data source's binding manager with this property:

  Public Property SelectedIndex As Integer 

Note that the selected index is 1-based, not 0-based; 1 indicates that no row is selected. When the selected index changes, the list object raises the SelectedIndexChanged event. It raises IndexOutOfRangeException should you attempt to set an invalid index.

If the AutoSelectRows property is set to TRue, the view's selection is updated whenever the currency changes:

Public Property AutoSelectRows As Boolean 

Three other properties directly affect the appearance of data-bound list objects:

Public Property DataBoundFormat As XlRangeAutoFormat Public Property DataBoundFormatSettings As FormatSettings Public Property AutoSetDataBoundColumnHeaders As Boolean 

The DataBoundFormat property determines whether Excel does automatic reformatting of the list object cells when the data change. You have several dozen formats to choose among; the default is xlRangeAutoFormatNone. If you want no formatting, choose xlRangeAutoFormatNone. You can also choose which aspects of the formatting you want to apply by setting the bit flags in the DataBoundFormatSettings property (by default, all the flags are turned on):

Public Enum FormatSettings   Alignment = 256   Border = 4096   Font = 16   Number = 1   Pattern = 65536   Width = 1048576 End Enum 

The AutoSetDataBoundColumnHeaders property indicates whether the list object data binding should automatically create a header row in the list object that contains the column names. It is set to False by default.

New Data-Related List Object Events

There are also several new data-related events on the List Object, listed in Table 17.1.

Table 17.1. New Events Associated with List Object

Event Name	Delegate Type
DataSourceChanged	EventHandler
DataMemberChanged	EventHandler
SelectedIndexChanged	EventHandler
DataBindingFailure	EventHandler
BeforeAddDataBoundRow	BeforeAddDataBoundRowEventHandler
ErrorAddDataBoundRow	ErrorAddDataBoundRowEventHandler
OriginalDataRestored	OriginalDataRestoredEventHandler

The DataSource and DataMember properties on the list object aggregate determine to what data source the list object is complex-data-bound. The DataSourceChanged and DataMemberChanged events are raised when the corresponding properties are changed.

The SelectedIndexChanged event is primarily a view event; when the user clicks a different row, the event is raised. Note, however, that changing the selected row also changes the currency of the binding manager. This can be used to implement master-detail event binding.

If for any reason an edit to the list object failsif the data binding layer attempts unsuccessfully to add a row or column to the list, for example, or if a value typed in the list object cannot be copied back into the bound data sourcethe DataBindingFailure event is raised.

The BeforeAddDataBoundRow event has two primary uses. Listing 17.8 shows its delegate.

Listing 17.8. The BeforeAddDataBoundRow Event Types

Public Delegate Sub BeforeAddDataBoundRowEventHandler( _   ByVal sender As Object, _   ByVal e As BeforeAddDataBoundRowEventArgs) Public NotInheritable Class BeforeAddDataBoundRowEventArgs   Inherits EventArgs   Public Property Cancel As Boolean   Public ReadOnly Property Item As Object End Class 

The item passed to the event handler is the row that is about to be added. The event can be used either to edit the row programmatically just before it is added or to do data validation and cancel the addition should the data be somehow invalid.

After the BeforeAddDataBoundRow event is handled, the list object attempts to commit the new row into the data source. If that operation throws an exception for any reason, the list object deletes the offending row. Before it does so, however, it gives you one chance to fix the problem by raising the ErrorAddDataBoundRow event. Listing 17.9 shows its delegate.

Listing 17.9. The ErrorAddDataBoundRow Event Types

Public Delegate Sub ErrorAddDataBoundRowEventHandler( _   ByVal sender As Object, _   ByVal e As ErrorAddDataBoundRowEventArgs) Public NotInheritable Class ErrorAddDataBoundRowEventArgs   Inherits EventArgs   Public ReadOnly Property InnerException As Exception   Public ReadOnly Property Item As Object   Public Property Retry As Boolean End Class 

The exception is copied into the event arguments; then the handler can analyze the exception, attempt to patch up the row, and retry the commit operation. Should it fail a second time, the row is deleted. The exception thrown in this case may be the new SetDataBindingFailedException, which is documented below.

A data source may have a fixed number of rows or a fixed number of columns. A data source can also contain read-only data or read-only column names. Therefore, attempting to edit cells, add rows, remove rows, add columns, or remove columns can all fail. In these cases, the list object disallows the change and restores the original shape. When it does so, it raises the OriginalDataRestored event. Listing 17.10 shows its delegate.

Listing 17.10. The OriginalDataRestored Event Types

Public Delegate Sub OriginalDataRestoredEventHandler( _   ByVal sender As Object, _   ByVal e As OriginalDataRestoredEventArgs) Public NotInheritable Class OriginalDataRestoredEventArgs   Inherits EventArgs   Public ReadOnly Property ChangeReason As ChangeReason   Public ReadOnly Property ChangeType As ChangeType End Class Public Enum ChangeType   ColumnAdded = 1   ColumnHeaderRestored = 5   ColumnRemoved = 2   RangeValueRestored = 0   RowAdded = 3   RowRemoved = 4 End Enum Public Enum ChangeReason   DataBoundColumnHeaderIsAutoSet = 3   ErrorInCommit = 4   FixedLengthDataSource = 1   FixedNumberOfColumnsInDataBoundList = 2   Other = 5   ReadOnlyDataSource = 0 End Enum 

New Exception

Data binding can fail under many scenarios; the SetDataBindingFailedException is thrown in three of them:

• If the data source of the list object is not a list data source

• If the data source of the list object has no data-bound columns

• If the list object cannot be resized when the data change

The exception class has these public methods and a Reason property, shown in Listing 17.11.

Listing 17.11. The SetDataBindingFailedException Types

Public NotInheritable Class SetDataBindingFailedException   Inherits Exception   Public Sub New()   Public Sub New(ByVal message As String)   Public Sub New(ByVal message As String, _     ByVal innerException As Exception)   Public ReadOnly Property Reason As FailureReason End Class Public Enum FailureReason   CouldNotResizeListObject = 0   InvalidDataSource = 1   NoDataBoundColumnsSpecified = 2 End Enum