Datagridview Class Hierarchy

The class hierarchy for the main parts of the DataGridView control is shown in Figure 24-11.

Figure 24-11

The control utilizes objects derived from DataGridViewColumn when displaying data — as you can see from Figure 24-11, there are now far more options for displaying data than there were with the original DataGrid. One major omission was the display of drop-down columns within the DataGrid — this functionality has now been provided for the DataGridView in the form of the DataGridViewComboBoxColumn.

When you specify a data source for the DataGridView, by default it will construct columns for you automatically. These will be created based on the data types in the data source, so for example any Boolean field will be mapped to the DataGridViewCheckBoxColumn. If you would rather handle the creation of columns yourself, you can set the AutoGenerateColumns property to false and construct the columns yourself.

The following example shows how to construct columns and includes an image and also a ComboBox column. The code utilizes a DataSet and retrieves data into two data tables. The first DataTable contains the employee information from the Northwind database. The second table consists of the EmployeeID column and a generated Name column, which is used when rendering the ComboBox:

 using (SqlConnection con =  new SqlConnection (  ConfigurationSettings.ConnectionStrings["northwind"].ConnectionString ) ) { string select = "SELECT EmployeeID, FirstName, LastName, Photo, IsNull(ReportsTo,0) as ReportsTo FROM Employees"; SqlDataAdapter da = new SqlDataAdapter(select, con); DataSet ds = new DataSet(); da.Fill(ds, "Employees"); select = "SELECT EmployeeID, FirstName + ' ' + LastName as Name FROM Employees UNION SELECT 0,'(None)'"; da = new SqlDataAdapter(select, con); da.Fill(ds, "Managers"); // Construct the columns in the grid view SetupColumns(ds); // Set the default height for a row dataGridView.RowTemplate.Height = 100 ; // Then setup the datasource dataGridView.AutoGenerateColumns = false; dataGridView.DataSource = ds.Tables["Employees"]; } 

Here there are two things to note. The first select statement replaces NULL values in the ReportsTo column with the value zero. There is one row in the database that contains a NULL value in this field, indicating that the individual has no manager. However, when data binding, the ComboBox needs a value in this column, otherwise an exception will be raised when the grid is displayed. In the example, the value zero is chosen because it does not exist within the table — this is commonly termed a sentinel value because it has special meaning to the application.

The second SQL clause selects data for the ComboBox and includes a manufactured row where the values Zero and (None) are created. In Figure 24-12, the second row displays the (None) entry.

Figure 24-12

The custom columns are created by the following function:

 private void SetupColumns(DataSet ds) { DataGridViewTextBoxColumn forenameColumn = new DataGridViewTextBoxColumn(); forenameColumn.DataPropertyName = "FirstName"; forenameColumn.HeaderText = "Forename"; forenameColumn.ValueType = typeof(string); forenameColumn.Frozen = true; dataGridView.Columns.Add(forenameColumn); DataGridViewTextBoxColumn surnameColumn = new DataGridViewTextBoxColumn(); surnameColumn.DataPropertyName = "LastName"; surnameColumn.HeaderText = "Surname"; surnameColumn.Frozen = true; surnameColumn.ValueType = typeof(string); dataGridView.Columns.Add(surnameColumn); DataGridViewImageColumn photoColumn = new DataGridViewImageColumn(); photoColumn.DataPropertyName = "Photo"; photoColumn.Width = 100; photoColumn.HeaderText = "Image"; photoColumn.ReadOnly = true; photoColumn.ImageLayout = DataGridViewImageCellLayout.Normal; dataGridView.Columns.Add(photoColumn); DataGridViewComboBoxColumn reportsToColumn = new DataGridViewComboBoxColumn(); reportsToColumn.HeaderText = "Reports To"; reportsToColumn.DataSource = ds.Tables["Managers"]; reportsToColumn.DisplayMember = "Name"; reportsToColumn.ValueMember = "EmployeeID"; reportsToColumn.DataPropertyName = "ReportsTo"; dataGridView.Columns.Add(reportsToColumn); } 

The ComboBox is created last in this example — and uses the Managers table in the passed data set as its data source. This contains Name and EmployeeID columns, and these are assigned to the DisplayMember and ValueMember properties, respectively. These properties define where the data is coming from for the ComboBox.

The DataPropertyName is set to the column in the main data table that the combo box links to — this provides the initial value for the column, and if the user chooses another entry from the combo box, this value would be updated.

The only other thing this example needs to do is handle null values correctly when updating the database. At present, it will attempt to write the value zero into any row if you were to choose the (None) item onscreen. This would cause an exception from SQL Server because this would violate the foreign key constraint on the ReportsTo column. To overcome this, you would need to preprocess the data before sending it back to SQL Server, and set to NULL the ReportsTo column for any rows where this value was zero.