101 Ways to Manipulate the DataGridView Control (Bagian 2)

Binding Using Typed Dataset
One very common use of the DataGridView control is binding to a table in a database. To illustrate this, I'll add a Typed DataSet to the current project. In Visual Studio 2005, right-click on the project name in Solution Explorer and select Add | New Item…. Select the DataSet template (use the default name of DataSet1.xsd) and click Add.

Launch Server Explorer (View | Server Explorer) and navigate to the Northwind sample database (assuming you have it installed on SQL Server/SQL Server Express). Drag and drop the Customers table onto the design surface of DataSet1.xsd. Figure 5 shows the creation of the typed dataset.
You have a choice of what to do for your next step. You can either bind the DataGridView control directly to the table adapter of the Customers table, like this:

'---create an instance of the table adapter---
Dim adapter As New CustomersTableAdapter
'---data bind to the DataGridView control---
DataGridView1.DataSource = adapter.GetData

Alternatively, you can use a BindingSource control:

'---create an instance of the table adapter---
Dim adapter As New CustomersTableAdapter
'---create an instance of the bindingsource control---
Dim bindingSrc As New BindingSource
'---set the datasource for the bindingsource control---
bindingSrc.DataSource = adapter.GetData
'---data bind to the DataGridView control---
DataGridView1.DataSource = bindingSrc

Note that before the code above could work, you'd need to import the namespace as follows (DVG is the name of my project):

Imports DGV.DataSet1TableAdapters

Figure 6 shows the result of the data-binding.

Figure 5. Create a typed dataset as shown.

Figure 6. The result of binding to a typed dataset is shown.

Binding Using a Dataset
If you are creating a Dataset by hand, you can bind the DataGridView control by first setting its DataSource property to the dataset, followed by setting the table to display via its DataMember property:

Dim connStr As String = _
"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;" & _
"Integrated Security=True"
Dim sql As String = "SELECT * FROM Customers"
Dim conn As SqlConnection = New SqlConnection(connStr)
Dim comm As SqlCommand = New SqlCommand(sql, conn)
Dim dataadapter As SqlDataAdapter = New SqlDataAdapter(comm)
Dim ds As DataSet = New DataSet()
'---open the connection and fill the dataset---
conn.Open()
'---fill the dataset---
dataadapter.Fill(ds, "Customers_table")
'---close the connection---
conn.Close()
'---bind to the DataGridView control---
DataGridView1.DataSource = ds
'---set the table in the dataset to display---
DataGridView1.DataMember = "Customers_table"

Detecting Which Cell Is Clicked
If you want to retrieve the value of the cell in the DataGridView control clicked by the user, service the CellEnter event:

'---when the user clicks on the datagridview control---
Private Sub DataGridView1_CellEnter( _
ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
Handles DataGridView1.CellEnter
'---prints the content of the cell---
Console.WriteLine( _
DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value)
End Sub

The RowIndex and ColumnIndex properties will contain the row and column numbers, respectively, of the cell currently selected.Filtering Using DataView
You can perform filtering on the data displayed in the DataGridView control using a DataView object:

'---create an instance of the table adapter---
Dim adapter As New CustomersTableAdapter
'---create an instance of the dataview class---
Dim dv As New DataView(adapter.GetData)
With dv
.AllowNew = False
.AllowDelete = True
.Sort = "ContactTitle ASC, Address ASC"
.RowFilter = "CustomerID LIKE 'B*'"
End With
DataGridView1.DataSource = dv

The above code snippet shows that the DataView object allows users to add new rows (via the AllowNew property) in the DataGridView control and also allows rows to be deleted (via the AllowDelete property). In addition, you can sort the rows by specifying the field(s) and their corresponding sort order (via the Sort property). Finally, you filter the records by specifying an SQL-like expression using the RowFilter property.The above code snippet sorts the rows according to the ContactTitle field (in ascending order), followed by the Address field (also in ascending order). If you want to sort the Address field in descending order, set it as follows:

.Sort = "ContactTitle ASC, Address DESC"

Figure 7 shows the rows sorted based on the ContactTitle and Address fields in ascending order, versus that for ContactTitle in ascending order and Address in descending order.

Figure 7. Here are the different sort orders for ContactTitle and Address columns.

Figure 8. Sorting the first column in descending order.

Sorting Columns
Besides using the DataView object for filtering and sorting, you can also control sorting using the Sort() method from the DataGridView control itself, as demonstrated below:

'---sort based on the first column---
DataGridView1.Sort(DataGridView1.Columns(0), _
System.ComponentModel.ListSortDirection.Descending)

Here, you are sorting the rows based on the first column, in descending order (see Figure 8).Apart from programmatically sorting the columns, the user can also click on the sorting glyph (the triangular icon displayed next to the column header) to sort the rows either in ascending or descending order. To prevent a row from being sorted, set the SortMode to NotSortable:

'---set the sort mode for a column---
DataGridView1.Columns(1).SortMode = _
DataGridViewColumnSortMode.NotSortable

Adding Columns Programmatically
So far I have shown you how to data bind a DataGridView control to a database (through a dataset or BindingSource control). Sometimes it is necessary to programmatically create the various fields in the DataGridView control and let the user populate it (or you can also add the new rows using code). To create columns in the DataGridView control, use the Add() method from the Columns property in the DataGridView control. The following code snippet creates four fields in the DataGridView control:

'---adding columns---
DataGridView1.Columns.Add("ID", "Product ID")
DataGridView1.Columns.Add("Name", "Product Name")
DataGridView1.Columns.Add("Description", "Description")
DataGridView1.Columns.Add("Price", "Price")

Figure 9 shows what the DataGridView control looks like.

Figure 9. Create these four columns in the DataGridView control as shown.

Figure 10. Programmatically add new rows to the DataGridView control as shown.

Adding Rows
To add a new row to the DataGridView control, use the Add() method from the Rows property:

'---add an empty row---
DataGridView1.Rows.Add()

To add a row and populate it with content, create an instance of the DataGridViewRow class and then use the CreateCells() method to create a row template:

For i As Integer = 0 To 9
'---create a row---
Dim item As New DataGridViewRow
item.CreateCells(DataGridView1)
With item
.Cells(0).Value = i
.Cells(1).Value = "Product " & i
.Cells(2).Value = "Description of Product " & i
.Cells(3).Value = "99.99"
End With
'---add the row---
DataGridView1.Rows.Add(item)
Next

Alternatively, you can also add a new row by creating an array containing the values for the various fields in the row:

For i As Integer = 0 To 9
DataGridView1.Rows.Add(New String() _
{i, _
"Product " & _
i, _
"Description of Product " & i, _
"99.99"})
Next

Figure 10 shows the output for the above two code snippets.