Binding-Related Extensions to Host Items and Host Controls

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:

  • BindingContext BindingContext { get; set; }

  • ControlBindingsCollection DataBindings { get; }

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.

Extensions to the List Object Host Control in Excel

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:

object DataSource { get; set; }
string DataMember { get; set; }

The reason that the list object divides this information up into two properties is because some data sources contain multiple lists, called "members." For example, you could set the DataSource property to a dataset 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. However, it is usually easier to use one of the SetDataBinding methods to set both properties at once:

void SetDataBinding(object dataSource)
void SetDataBinding(object dataSource, string dataMember)
void SetDataBinding(object dataSource, string dataMember,
  params string[] mappedColumns)

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. For instance, 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.

-7. Setting Up the Binding Source
this.OrderBookBindingSource = new System.Windows.Forms.BindingSource();
this.OrderBookBindingSource.DataMember = "Book";
this.OrderBookBindingSource.DataSource = this.orderDataSet1;
this.BookList.SetDataBinding(this.OrderBookBindingSource, "",
  "Title", "ISBN", "Price");

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

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. However, if you have a data-bound list object in the designer, 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 explicitly rebind the list objects every time the customization starts up. 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:

void ResetPersistedBindingInformation()

The data source of the list object must implement either IList or IListSource. 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 presently complex-data-bound by checking the IsBinding property:

bool IsBinding { get; }

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

int SelectedIndex { get; set; }

Note that the selected index is one-based, not zero-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:

bool AutoSelectRows { get; set; }

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

XlRangeAutoFormat DataBoundFormat { get; set; }
FormatSettings DataBoundFormatSettings { get; set; }
bool AutoSetDataBoundColumnHeaders { get; set; }

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 from; the default is xlRangeAutoFormatNone. If you want no formatting at all, choose xlRangeAutoFormatNone. You can also pick and choose which aspects of the formatting you want applied by setting the bit flags in the DataBoundFormatSettings property. (By default, all the flags are turned on.)

enum FormatSettings
    Number      = 0x00000001,
    Font        = 0x00000010,
    Alignment   = 0x00000100,
    Border      = 0x00001000,
    Pattern     = 0x00010000,
    Width       = 0x00100000

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

New Events Associated with List Object

Delegate Type

Event Name















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. However, note 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 failsfor instance, if the data binding layer attempts unsuccessfully to add a row or column to the list, or if a value typed into 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.

-8. The BeforeAddDataBoundRow Event Types
delegate void BeforeAddDataBoundRowEventHandler(object sender,
  BeforeAddDataBoundRowEventArgs e);
class BeforeAddDataBoundRowEventArgs : EventArgs
  Object Item { get; }
  bool Cancel { get; set; }

The item passed to the event handler is the row that is about to be added. The event can be used to either programmatically edit the row just before it is actually 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.

-9. The ErrorAddDataBoundRow Event Types
delegate void ErrorAddDataBoundRowEventHandler(object sender,
  ErrorAddDataBoundRowEventArgs e);
class ErrorAddDataBoundRowEventArgs : EventArgs
  object Item { get; }
  Exception InnerException { get; }
  bool Retry { get; set; }

The exception is copied into the event arguments; the handler can then 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.

-10. The OriginalDataRestored Event Types
delegate void OriginalDataRestoredEventHandler(object sender,
  OriginalDataRestoredEventArgs args)

class OriginalDataRestoredEventArgs : EventArgs
  ChangeType ChangeType { get; }
  ChangeReason ChangeReason { get; }
enum ChangeType

public enum ChangeReason

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.

-11. The SetDataBindingFailedException Types
[Serializable] class SetDataBindingFailedException : Exception
  SetDataBindingFailedException(string message)
  SetDataBindingFailedException(string message,
    Exception innerException)
  void GetObjectData(SerializationInfo info, StreamingContext context)
  FailureReason Reason { get; }

enum FailureReason

 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows 

blauber 6 years, 6 months ago #
Hi you write "The exception is copied into the event arguments; the handler can then 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." Is there an option to NOT delete the row, show a message box to the user with the information which data is not correct, and give the user a second chance to correct the data? I can do that in the BeforeAddDataBoundRow event handler. But when I set e.Cancel = true then the row will be deleted. Many thanks Bernhard