Using the BindingSource Class

You can spend considerable time trying to manage data binding if you stick with the 1.0 and 1.1 .NET Framework classes. One of the innovations introduced with the 2.0 Framework is the BindingSource class that you can instantiate in code, drag from the Toolbox, or get D&D to generate. This new class is especially useful when dealing with complex hierarchies of bound controlsfor example, when you're bound to Customers, Orders, and Items on the same form.

The BindingSource class acts as a proxy between a collection of bound controls and a specified data source. Basically, your code binds form controls to a data sourcejust as I discussed earlier with the control DataBindings propertyexcept backward. In this case, the binding is done from the top downnot from the bottom (the controls) up to the data sources. This means you can manage data binding from a single classassuming you can figure out how to program the BindingSource class. I'm going to try to help.

Tip

Behind the scenes, the BindingSource class creates and manages a list of type BindingList. I'll talk about that class a bit later.

Generally, a BindingSource serves several purposes by providing:

• A layer of abstraction between the data and bound controls

• A set of services used to navigate through the rowset, expose a "current" row, pass the current row data to bound controls, and pass data from bound controls back to the data source's rowset column values

• A set of services to filter and sort the data returned from the data source rowset

• A strongly typed data source to feed any control that can accept simple or complex DataSource binding

In earlier chapters, I saw the BindingSource class automatically included in my forms in a number of scenariosespecially as I toured the Data Source Configuration Wizard and used drag-and-drop to bind to a TableAdapter.

The BindingSource Properties

I'm not going to simply list the properties of the BindingSource, as you can get that information online or from the MSDN helpsuch as it is. I am going to point out properties (in Table 7.1) that you're likely to use when data binding in code and illuminate these. Many of these properties are used when writing tools that do data bindingtools like Visual Studio.

The Current Property

As with ADO classic Recordset objects, a bound rowset has a "current" record/row pointer. Yes, this pointer can be invalid at timesas when you haven't populated the rowset, you've executed Fill (or GetData) and there are no qualifying rows, or you've stepped off the end of the rowset. When binding to a populated DataTable, the Current property simply returns a DataRowView instance that contains the Row (usually a strongly typed row) addressed by the current row pointer. You manipulate this pointer by changing the Position property or by using the CurrencyManager class that I discuss a bit later. Figure 7.11 illustrates using the Current property.

Figure 7.11. Exposing the "current" bound row using the Current property.

The Data Source Property

This property is one of the most important for the BindingSource instanceit points to the source of data. Typically, this will be an instance that supports IBindingList like the DataTable or the DataView. Figure 7.12 shows how I set the DataSource for a set of TextBox controls and for a complex bound controla DataGridView in this case. Note how the Timestamp column in the DataGridView is hidden so it does not throw DataError exceptions when it's rendered.

Figure 7.12. Setting the BindingSource DataSource property.

One problem you might encounter when working with multi-column complex bound controls (like the DataGridView) is that you don't have any control over how the control initially displays the data. That is, when you bind to a complex control, it's an "all or nothing" contract. You bind to either all of the columns or none of themyou don't get to pick and choose. This is not the case when you use drag-and-drop to "details" (individual simple-bound controls).

In some cases, I've seen the DataGridView throw exceptions (firing the DataError event) on each row that contains a Timestamp byte arraybut only when the value is made visible. It seems that there is no way to display the binary data in a byte array. One approach to deal with this would be to hide that column. That's not hard to do when you drag and drop a TableAdapter to the Form, but when you roll the data binding yourself, things get a bit sticky. In this case, you won't have any schema to alter until the control is bound at runtime. If this column is initially visible, you're pooched. About the only thing you can do is make sure that columns containing unrenderable data types (like Timestamp) are not included in the query, or set the Visible property on the troublesome DataGridView Column to False, as shown in Figure 7.12.

No, you don't have to build a DataTable to display data in a bound control. As I said, you can bind to anything that supports one of the acceptable interfaces, like IBindingList. For example, suppose you want to display a pick list, such as a list of valid columns. In this case, you can construct an array list or Collection to hold the data, populate the Collection, and set the DataSource property to point to the collection. An example of this technique is shown in Figure 7.13.

Yes, when the contents of the colColumns Collection are changed, the binding mechanism posts the changes to the bound controlsjust as if they had come from an in-memory DataTable.

The Filter Property

The BindingSource Filter property is very much like the DataView Filter property, as it supports the same syntax. Basically, the Filter property works like a WHERE clause. It's used to filter the in-memory bound data source so that only rows that meet the criteria in the Filter string are made "visible" to the binding mechanism. This way you can temporarily hide rows that the user does not want to see. The Filter property is coded as <Column name> <expression>. All string literals are framed in single quotes. You can use AND or OR or other Boolean operators to combine the filtering criteria. For example, the following strings are valid Filter property settings:

• "Author LIKE 'Va%'"

• "Year_Born > 1940 AND Year_Born < 1948" (no, the BETWEEN operator is not supported)

• "Author LIKE 'O''M%'". Note that the apostrophe is doubled to deal with the "O'Malley" issue.

• "NOT Author IS NULL". This filters (out) rows where the Author column is NULL.

• "Au_ID > 35 AND Author LIKE 'B%' OR Year_Born = 1947

Note the use of the SQL Server "%" wildcard character. All string literals are framed in single quotes (the apostrophe character,'), so you'll have to deal with the "O'Malley" issue when you collect filters from your UI.

As new rows are added to the DataTable bound to the BindingSource, if they don't meet the Filter criteria, they'll disappear once the add is committed.

Remember, the Filter property does not delete rows that don't meet the Filter criteriait simply hides them. This does not reduce the memory footprint your data consumes, and it does not reduce the amount of time it takes to get the data to your client or improve performanceunless you consider the round-trip overhead to fetch a filtered resultset. That is, it might make more sense to fetch fewer (filtered) rows in the first place instead of filtering a larger rowset on the client. However, if you've already reduced the number of rows to a scalable number, using the Filter property to further refine the bound rows can make a lot of sense.

Using the Position Property

The BindingSource Position property is used to determine or set the "current" row index. I discuss how to set and manage this property a bit latersee "Using the Move Methods."

Using the Sort Property

Again, this property is implemented like the ADO.NET DataView Sort property. This means you can name any column and indicate "ASC" (the default) for an ascending sort or "DESC" for a descending sort. Yes, you can sort on more than one column by simply separating individual sort expressions with commas. Actually, this property might not be needed, as complex bound controls like the DataGridView have sorting built in. It's a lot more interesting for ASP applications or in situations where you need a multi-column sort.

Of course, you can pre-sort the data being fetched from the server using an ORDER BY clause in the initial SELECT query. However, consider that SQL Server's query optimizer might be forced to sort millions of rows on the server before picking the few rows to return to your client. In this case, accepting an unsorted rowset and using the Sort property here (or on a DataView) makes more sense.

The BindingSource Events

As rows are added and changes are made to the bound controls, the BindingSource serves as a point source for events that can report these operations. It's well beyond the scope of this book to wade into best practice or implementation of these events, so I'll just touch on the high points.

In the example application used to illustrate these events, I drag the Toolbox BindingSource to the form. This makes it easy to set up the event handlers in the Visual Studio UI. Another approach (in Visual Basic) is to declare the BindingSource With Events to enable the event handlers in the Visual Studio UI. However, the Visual Studio UI can make this even easier by using a new feature that exposes events of any item you can select with the Design window openassuming the object is present on the form. That's why I dropped a BindingSource Toolbox icon on the form. To demonstrate this new feature, follow these steps:

Table 7.2 lists and briefly describes the events exposed by the BindingSource class. I wrote an example that monitors each of these events (as shown in Figure 7.15). As you can tell by running this example yourself (it's on the DVD), there are quite a few events to monitor as rows are added to the binding list and as the current row is changed. Only the ListChanged event returns any additional information. It returns the type of change and a PropertyDescriptor that describes the change. The "list" referred to in the table is the rowset managed by the BindingSource.

Figure 7.15. Example application tracks BindingSource events.

The BindingSource Methods

The BindingSource has a rich complement of methods that help you manage its collection of data bindings. However, after consulting with Brian Noyes, I've discovered that many of these methods can't be used when binding to DataTable instances. The list in Table 7.3 shows the most interesting of the methods you can use along with a brief description. I'll further illuminate some of the less intuitive or more complex of these methods. I've also left off some methods that are implemented to help the inner workings of Visual Studio or its code generators, as they are far too complex for general use.

Using the EndEdit and CancelEdit Methods

The EndEdit method is probably one of the most overlooked methods when it comes to data binding and updating. It's used to inform the databinding mechanism that the changes made to the bound controls are ready to be posted back to the bound control column(s). Wait a minute. Isn't that supposed to happen automatically with data bound values? Sure, you're right, it is. However, how is the binding mechanism supposed to know when you're done with your edits? Should the changes be posted as you type each letter or paste in a new value from the Clipboard? What about when you move the cursor, tab from column to column, or simply click on a new column in the same row? Does that mean you're done with the changes? What about when you click outside the scope of the bound control on another button, or outside the scope of the application on another application or on a system-level function? Actually, few, if any, of these events consistently trigger the operation that posts changes made to a control(s) back to the bound DataTable columns. It's for this reason that you need to remember to execute the EndEdit on the BindingSource to inform it that any changes need to be written to the DataTable. In Chapter 13, "Managing SQL Server CLR Executables," I code the EndEdit as part of an update routine. It doesn't really matter, as long as it's done before you call Update or test a DataTable for changes (with the HasChanges property).

Let's walk through a typical scenario that trips up a lot of developers. For example, when working with the DataGridView (or any grid control) and editing a cell in the grid, developers don't always realize that changes are being made to the gridnot to the underlying bound DataTable. If they happen to navigate to another grid row, this triggers the "end edit" functionalitythe cell contents are persisted to the DataTable. Typically, this works in testing, but when actual users work with your application, they might simply enter the data into cellsassuming that the changes are being posted to the DataTable (and the database). Sometimes they are, but sometimes they're notdepending on the sequence of events. It's for this reason that you need to ensure that before focus leaves the DataGridView, you fire the EndEdit event or provide a "Save" button on the form to execute this method. No, this does not write anything to the database. Your code still needs to execute the DataAdapter or TableAdapter Update method to post changes to the database.

The CancelEdit method simply tells ADO.NET that any changes not already posted to the DataTable are to be ignored. That is, if a bound control's contents are refreshed (as when you execute the ResetBindings method), these changes are lost.

Using the Find Method

The BindingSource Find method is fairly intuitive and brutally simple. It searches the bound column specified and returns the row index of the first row that exactly matches the search argument. No, it does not support ambiguous search, and, no, it does not permit you to set the starting row (which limits the usability of this method). The snippet shown in Figure 7.16 illustrates how to call the method. I use the integer returned from the method to change the DataGridView currently selected row indexassuming it points to the underlying data row I found.

Figure 7.16. The Find method returns the first row index that matches.

This method won't be that useful if you need to search for ambiguous values or repeat the search when there are two or more matches in the bound column. In this case, I recommend binding to a DataView class exposed by the DataTable. It has a far richer set of search methods and properties. I discussed these in Chapter 6, "Building Data Sources, DataSets, and TableAdapters."

Using the Move Methods

Reminiscent of the ADO classic Recordset object, the BindingSource "move" methods (MoveFirst, MoveNext, MovePrevious, MoveLast) are used to reposition the "current" bound row index when using simple bound controls. This index is exposed in the Position property, which can be used to set a specific row index. The move methods are quite intuitive to code and deal automatically with "end of file" and "beginning of file" (EOF/BOF) issues. That is, no exception is thrown if you try to set the current row off the end of the bound list. This approach is especially useful when simple binding to "display" controlsindividual simple bound controls instead of a complex bound control like the DataGridView.

To demonstrate these methods, the example application includes a custom ToolStrip control I created and a UI that has buttons for each method. Nope, there is no other exception handling or other code that the ADO classic Recordset navigation code requiredit's really simple to implement. Incidentally, the populated ToolStrip, shown in Figure 7.17, can be automatically generated if you use drag-and-drop binding. It's also an ideal candidate for use as a compiled custom control.

As I said, the Position property reflects the current row index. It's a zero-based index (as is the Row collection index). If it's set to an out of range value, the .NET Framework sets it within the valid range of bound rows0 to the number of rows in the bound DataTable. All you need to do is verify that the value passed is numeric, as shown in the event handler in Figure 7.18.

Data Binding Using the BindingSource Class

At this point, I think that we're all ready to walk through some typical data binding scenarios. As I see it, there are several segments to this discussionassuming you've already executed a query and your data is ready to display and (possibly) edit:

• Displaying data in "complex" bound controls: This is really pretty easy, as the control itself (like the DataGridView) knows how to handle much of the overhead involved in rowset population, current row navigation, data validation, and other issues.

• Displaying data in "simple" bound controls: In this case, you have a bit more work to do. First, you have to expose a separate control for each column of the data structure to display. Each control (like a TextBox, Label, or ListBox control) must be added to the list of controls in the BindingSource. You'll also want to implement some type of "cursor" (current row) positioning mechanism. That's what the CurrencyManager is forI'll talk about that later.

• When the data is changed: You have more work to do. What most of the Microsoft help topics don't discuss is how to perform UI data validation. While I didn't include a chapter on that subject, in essence, this is the process of testing the values entered to ensure that they conform to specific business rule criteria. I hope to write an EBook to tour my ideas on UI design and implementation. These rules can be implemented in business classes, by morphing the bound control properties to match the rules, or in a number of other ways that I discuss later.

• When data is removed or added: The bound controls need to be made aware of these changes. I'll talk about how to refresh simple and complex bound controls later in this chapter.

• Moving data back to the local data source: Once the data is committed, some process needs to move the data from the bound control back to the data source. This seemingly simple step is often overlooked by developers who assume that because the data is bound, the Framework somehow magically moves the data after it's changedit doesn't. I'll discuss later how to know when the data has changed and how to force the Framework classes to write it back to the data source.

• Writing data back to the database: Once the changes are written back to the data source, you'll need to somehow get it written back to the database, if you expect it to be there the next time someone queries the data. This is the subject of yet another chapter that discusses how to write updated data back to SQL Server. See Chapter 13 for more details.