This chapter builds on the content of which covered various ways of selecting and changing data, by showing you how to present data to the user by binding to various
Windows controls, More specifically,
- Displaying data using the DataGridView control
- The .NET data-binding capabilities and how they work
- How to use the Server Explorer to create a connection and generate a DataSet class (all without writing a line of code)
- How to use hit testing and reflection on rows in the DataGrid
You can download the source code for the examples in this chapter from the Csharp Web site at www.csharpaid.com.
The DataGridView Control
The DataGrid control that has been available from the initial release of .NET was functional, but had many areas that made it unsuitable for use in a commercial application – such as an inability to display images, drop-down controls, or lock columns, to name but a few, The control always felt half-completed, so many control vendors provided custom grid controls that overcame these deficiencies and also provided much more functionality.
.NET 2.0 introduced an addition Grid control- the DataGridView, This addresses many of the deficiencies of the original control, and adds significant functionality that previously was available only with add-on products.
The DataGridView control has binding capabilities similar to the old DataGrid, so it can bind to an Array, DataTable, DataView, or DataSet class, or a component that implements either the IListSource or ILht interface, It gives you a variety of views of the same data, In its Simplest guise, data can be displayed (as in a DataSet class) by setting the DataSource and DataMernber properties – note that this control is not cl plugin replacement for the DataGrid, so the programmatic interface to it is entirely different from that of the DataGrid, This control also provides more complex capabilities.
Displaying Tabular Data
Introduced numerous ways of selecting data and reading it into a data table, although the data was displayed in a very basic fashion using Console, WriteLine( ).
The following example demonstrates how to retrieve some data and display it in a DataGridView control. For this purpose, you will build a new application, DisplayTabularData, shown in Figure 32-1.
This simple application selects every record from the Customer table in the Northwind database and displays these records to the user in the DataGridView control, The following snippet shows the code for this example (excluding the form and control definition code):
The form consists of the getData button, which when clicked calls the getData_Click () method shown in the example code.
This constructs a SqlConnection object, using the ConnectionStrings property of the
ConfigurationManager class, Subsequently a data set is constructed and filled from the database table, using a DataAdapter object, The data is then displayed by the DataGridView control by setting the DataSource and DataMember properties, Note that the AutoGenerateColumns property is also set to true because this ensures that something is displayed to the user. If this Bag is not specified, you need to create all columns yourself.
The DataGridView control provides a flexible way to display data; in addition to setting the DataSource to a DataSet and the DataMember to the name of the table to display, the DataSource property can be set to any of the following sources:
- An array (the grid can binD to anyone-dimensional array)
- DataSet or DataViewManager
- Components that implement the IListSource interfaceI
- Components that implement the IList interface
- Any generic collection class or object derived from a generic collection class
The following sections give an example of each of these data sources.
Displaying Data from an Array
At first glance this seems to be easy. Create an array, fill-it with some data, and set the DataSource property on the DataGridView control, Here’s some example code:
string stuff = new string (‘One’, ~Two’, ‘Three’);
dataGridView.DataSource = stuff;
If the data source contains multiple possible candidate tables (such as when using a DataSet or DataViewManager), you need to also set the DataMember property.
Youcould replace the code in the previous example’s getData_Click event handler with the preceding array code, The problem with this code is the resulting display (see Figure 32-2).
Instead of displaying the strings defined within the array, the grid displays the length of those strings, That’s because when using an array as the source of data for a DataGridView control, the grid looks for the first public property of the object within the array and displays,this value rather than the string value, The first (and only) public property of a string is its length, so that is what is displayed, The list of, properties for any class can be obtained by using the GetProperties method of the TypeDescriptor class, This returns a collection of PropertyDescriptor objects, which can then be used when displaying data, The .NET PropertyGrid control uses this method when displaying arbitrary objects:
One way to rectify the problem with displaying strings in the DataGridView is to create a wrapper class:
Figure 32.3 shows the output when an array of this Item class (which could just as well be a struct for all the processing that it does) is added to your data source array code.
You can display a DataTable within a DataGridView control in two ways:
- If you have a standalone DataTable, simply set the DataSource property of the control to the table.
- If your DataTable is contained within a DataSet, you need to set the DataSource to the data set and the DataMemberproperty should be set to the name of the DataTable within the data set.
Figure 32-4 shows the result of running the DataSourceDataTable sample code.
Note the display of the last column; it shows a check box instead of the more common edit control, The DataGridView control, in the absence of any other information, will read the schema from the data source (which in this case is the Products table), and infer from the column types what control is to be displayed, Unlike the original DataGrid control, the DataGridView control has built-in support for
image columns buttons, and combo boxes.
The data in the database does not change when fields are altered in the data grid because the data is stored only locally on the client computer – there is no active connection to the database.
Displaying, Data from a DataView
A DataView provides a means to filter and sort data within a DataTable, When data has been selected “nun the database, it is common to permit the user to sort that data.for example, by clicking on column headings, In addition, the user might want to fjlter the data to show only certain rows, such as all those that have been altered, A DataView can be filtered so that only selected rows are shown to the user however, you cannot filter the columns from the DataTable.
A DataView does not permit the filtering of columns, only rows.
To create a DataYiew based on an existing DataTable, use the following code:
DataView dv = new DataView(dataTable);
Once created, further settings can be altered on the DataView, which affect the data and operations permitted on that data when it is displayed within the data grid, For example:
- Setting AllowEdit = false disables all column edit functionality for rows.
- Setting AllowNew = false disables the new row functionality.
- Setting AllowDelete = false disables the delete row capability.
- Setting the RowStateFilter displays only rows of a given state.
- Setting the RowFilter enables you to filter rows.
The next section explains how to use the RowStateFilter setting; the other options are fairly self-explanatory.
Filtering Rows by Data
After the DataView has been created, the data displayed by that view can be altered by setting the RowFilter property, This property typed as a string, is used as a means of filtering based on certain criteria defined by the value of the string, Its syntax is similar to a WHERE clause in regular SQL, but it is issued against data already selected from the database.
The following table shows some examples of filter clauses.
The runtime will do its best to coerce the data types used within the filter expression into the appropriate types for the source columns. For instance, it is perfectly legal to write ·UnitsInStock > ’50’· in the earlier example, even though the column is an integer, If an invalid filter string is provided, an EvaluateException will be thrown.
Filtering Rows on State
Each row within a DataView has a defined row state, which has one of the values shown in the following table, This state can also be used to filter the rows viewed by the user.
Figure 32-5 shows a grid that can have rows added, deleted, or amended, and a second grid that lists rows in one of the preceding states.
The filter not only applies to the visible rows but also to the state of the columns within those rows, This is evident when choosing the ModifiedOriginal’ or ModifiedCurrent selections, and are based on the DataRowVersion enumeration. For example, when the user has updated a column in the row, the row will be displayed when either ModifiedOriginal or ModifiedCurrent is chosen; however, the actual value will be either the Original value selected from the database (if ModifiedOriginal is chosen) or the current value in the DataColumn (if ModifiedCurrent is chosen).
Apart from filtering data, you might also have to sort jhe data within a DataView To sort data in ascending or descending order, simply click the column header in the DataGridView control (see Figure 32-6).
The only trouble is that the control can sort by only one column, whereas the underlying DataView control can sort by multiple columns.
When a column is sorted, either by clicking the header (as shown on the ProductName column) or in code, the DataGrid displays an arrow bitmap to indicate which column the sort has been applied to.
To set the sort order on a column programmatically, use the Sort property of the DataView:
dataView.Sort = ‘ProductName’;
dataView.Sort = ‘ProductName ASC, ProductID DESC’;
The first line sorts the data based on the ProductName column, as shown in Figure 32-6. The second line sorts the data in ascending order, based on the ProductName column, then in descending order of ProductID.
The DataView supports both ascending (default) and descending sort orders on columns, If more than one column is sorted In-code in the DataView, the DataGridView will cease to display any sort arrows.
Each column in the grid can be strongly typed, so its sort order is not based on the string representation of the column but instead is based on the data within that column. The upshot is that if there is a date column in the DataGrid, the user can sort numerically on the date rather than on the date string representation.
Displaying Data from a DataSet Class
There is one feature of DataSets that-the DataGridView cannot match the DataGrid in – this is when a DataSet is defined that includes relationships between tables, As with the preceding DataGridView examples, the DataGrid can display only a single DataTable at a time However, as shown in the following example, DataSourceDataSet, it is possible to navigate relationships within the DataSet onscreen, The following code can be used to generate such a DataSet based on the Customers and Orders tables in the Northwind’database, This example loads data from these two DataTables and then creates a relationship between these tables called CustomerOrders:
Once created, the data in the DataSet is bound to the DataGrid simply by calling SetDataBinding ():
This produces the output shown in Figure 32-7.
Unlike the DataGridView examples shown in this chapter, there is now a + sign to the left of each record. This reflects the fact that the DataSet has a navigable relationship between customers and orders, Any num~r of such relationships can be defined in code.
When the user clicks the + sign, the list of relationships is shown (or hidden if already visible). Clicking the name of the relationship enables you to navigate to the linked records (see Figure 32-8), in this example, listing all orders placed by the selected customer.
The pataGrid control also includes a couple of new icons in the top-right corner, The arrow permits the user to navigate to the parerit row, and will change the display to that on the previous page, The header row showing details of the parent record can be shown or h!dden by clicking the other button.
Displaying Data In a DataViewManager
The display of data in a DataViewManager is the same as that for the DataSet shown in the previous section, However, when a DataViewManager is created for a DataSet, an individual DataView is created for each DataTable, which then permits the code to alter the displayed rows based on a filter or the row state, as shown in the DataView example., Even if the code doesn’t need to filter data, it is good practice to wrap the DataSet in a DataViewManager for display because it provides more options when revising the source code.
The following creates a DataViewManager based on the DataSet from the previous example and then alters the DataView for the Customer table to show only customers from the United Kingdom:
DataViewManager dvm = new DataViewManager(ds);
dvm.DataViewSettings[“Customers”] .RowFilter = “Country=’UK'”;
dataGrid. SetDataBinding (dvm. “Customers”);
Figure 32-9 shows the output of the’DataSourceDataViewManager sample code.
IListSource and IList Interfaces
The DataGridView also supports any object that exposes one of the interfaces IListSource or IList, IListSource has only one method, GetList (), which returns an IList interface, IList, however, is somewhat more interesting and is implemented by a large number of classes in the runtime, Some of the classes that implement this interface are Array, ArrayList, and StringCollection.
When using IList, the same caveat for the object within the collection holds true as for the Array implementation shown earlier – if a StringCollection is used as the data source for the DataGrid, the length of the strings isdisplayed within the grid, not within the text of the item as expected.
DispiayIng Generic Collections
In addition to the types already described, the DataGridView also supports binding to generic collections, The syntax is just as in the other examples already provided in this chapter – simply set the DataSource property to the collection, and the control will generate an appropriate display.
Once again, the columns displayed are based on the properties of the object – all public readable fields are displayed in the DataGridView, The following example shows the display for a list class defined as follows:
The display shows several instances of the Person class that were constructed within the PersonList class See Figure 32-10.
In some circumstances, it might be necessary to hide certain properties from the grid display – for this you can use the Browsable attribute as shown in the following code snippet, Any properties marked as non-browsable are not displayed in the property grid.
public bool IsEmployed
The DataGridView uses this property to determine whether to display the property or hide it, In the absence of the attribute, the default is to display the property, If a property is read-only, the grid control will display the values from the object, but it will be read-only within the grid.
Any changes made in the grid view are reflected in the underlying objects – so, for example, if in the previous code the name of a person was changed within the user interface, the setter method for that property would be called.