[VB.NET] DataGridView 팁 정리

Tip 1 – DataGridView로 데이터 불러오기

In this short snippet, we will populate a DataGridView using the LoadData() method. This method uses the SqlDataAdapter to populate a DataSet. The table ‘Orders’ in the DataSet is then bound to the BindingSource component which gives us the flexibility to choose/modify the data location.

Public Class Form1

Inherits Form

Private da As SqlDataAdapter

Private conn As SqlConnection

Private bsource As BindingSource = New BindingSource()

Private ds As DataSet = Nothing

Private sql As String

Public Sub New()

InitializeComponent()

End Sub

Private Sub btnLoad_Click(ByVal sender As Object, ByVal e As EventArgs)

LoadData()

End Sub

Private Sub LoadData()

Dim connectionString As String = "Data Source=localhost;Initial Catalog=Northwind;" & _

"Integrated Security=SSPI;"

conn = New SqlConnection(connectionString)

sql = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, Freight," & "ShipName, ShipCountry FROM Orders"

da = New SqlDataAdapter(sql, conn)

conn.Open()

ds = New DataSet()

Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)

da.Fill(ds, "Orders")

bsource.DataSource = ds.Tables("Orders")

dgv.DataSource = bsource

End Sub

End Class

Tip 2 – DataGridView를 수정하고 데이터베이스에 저장하기

After editing the data in the cells, if you would like to update the changes permanently in the database, use the following code:

Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs)

Dim dt As DataTable = ds.Tables("Orders")

Me.dgv.BindingContext(dt).EndCurrentEdit()

Me.da.Update(dt)

End Sub

Tip 3 – DataGridView 레코드(row)삭제하기 전에 물어보기

Handle the UserDeletingRow event to display a confirmation box to the user. If the user confirms the deletion, delete the row. If the user clicks cancel, set e.cancel = true which cancels the row deletion.

Private Sub dgv_UserDeletingRow(ByVal sender As Object, ByVal e As DataGridViewRowCancelEventArgs)

If (Not e.Row.IsNewRow) Then

Dim res As DialogResult = MessageBox.Show("Are you sure you want to delete this row?", "Delete confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

If res = DialogResult.No Then

e.Cancel = True

End If

End Sub

Tip 4 – Column 폭 자동조절하기

The snippet shown below, first auto-resizes the columns to fit its content. Then the AutoSizeColumnsMode is set to the ‘DataGridViewAutoSizeColumnsMode.AllCells’ enumeration value which automatically adjust the widths of the columns when the data changes.

Private Sub btnResize_Click(ByVal sender As Object, ByVal e As EventArgs)

dgv.AutoResizeColumns()

dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

End Sub

Tip 5 - Select and Highlight an entire row in DataGridView

Dim rowToBeSelected As Integer = 3 ' third row

If dgv.Rows.Count >= rowToBeSelected Then

' Since index is zero based, you have to subtract 1

dgv.Rows(rowToBeSelected - 1).Selected = True

End If

Tip 6 - 맨 마지막 행으로 스크롤하기

The DataGridView has a property called FirstDisplayedScrollingRowIndex that can be used in order to scroll to a row programmatically.

Dim jumpToRow As Integer = 5 '이동시킬 최소 레코드 갯수

If dgv.Rows.Count >= jumpToRow AndAlso jumpToRow >= 1 Then

dgv.FirstDisplayedScrollingRowIndex = jumpToRow

dgv.Rows(jumpToRow).Selected = True

End If

Tip 7 - 필드합계를 계산하고 textbox에 출력하기

A common requirement is to calculate the total of a currency field and display it in a textbox. In the snippet below, we will be calculating the total of the ‘Freight’ field. We will then display the data in a textbox by formatting the result (observe the ToString("c")) while displaying the data, which displays the culture-specific currency.

Private Sub btnTotal_Click(ByVal sender As Object, ByVal e As EventArgs)

If dgv.Rows.Count > 0 Then

txtTotal.Text = Total().ToString("c")

End If

End Sub

Private Function Total() As Double

Dim tot As Double = 0

Dim i As Integer = 0

For i = 0 To dgv.Rows.Count - 1

tot = tot + Convert.ToDouble(dgv.Rows(i).Cells("Freight").Value)

Next i

Return tot

End Function

Tip 8 - 헤더 이름 바꾸기

If the columns being retrieved from the database do not have meaningful names, we always have the option of changing the header names as shown in this snippet:

Private Sub btnChange_Click(ByVal sender As Object, ByVal e As EventArgs)

dgv.Columns(0).HeaderText = "MyHeader1"

dgv.Columns(1).HeaderText = "MyHeader2"

End Sub

Tip 9 - DataGridView 필드, 레코드, 테두리 색상 바꾸기

Private Sub btnCellRow_Click(ByVal sender As Object, ByVal e As EventArgs)

' Change ForeColor of each Cell

Me.dgv.DefaultCellStyle.ForeColor = Color.Coral

' Change back color of each row

Me.dgv.RowsDefaultCellStyle.BackColor = Color.AliceBlue

' Change GridLine Color

Me.dgv.GridColor = Color.Blue

' Change Grid Border Style

Me.dgv.BorderStyle = BorderStyle.Fixed3D

End Sub

Tip 10 - DataGridView 필드 숨기기

If you would like to hide a column based on a certain condition, here’s a snippet for that.

Private Sub btnHide_Click(ByVal sender As Object, ByVal e As EventArgs)

Me.dgv.Columns("EmployeeID").Visible = False

End Sub

Tip 11 - DataGridView에 포함된 ComboBox의
SelectedIndexChanged 이벤트 다루기

To handle the SelectedIndexChanged event of a DataGridViewComboBox, you need to use the DataGridView.EditingControlShowing event as shown below. You can then retrieve the selected index or the selected text of the combobox.

Private Sub dataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs)

Dim editingComboBox As ComboBox = CType(e.Control, ComboBox)

If Not editingComboBox Is Nothing Then

AddHandler editingComboBox.SelectedIndexChanged, AddressOf editingComboBox_SelectedIndexChanged

End If

End Sub

Private Sub editingComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

Dim comboBox1 As ComboBox = CType(sender, ComboBox)

' Display index

MessageBox.Show(comboBox1.SelectedIndex.ToString())

' Display value

MessageBox.Show(comboBox1.Text)

End Sub

Tip 12 - DataGridView 레코드 색상 번갈아서 바꾸기

Private Sub btnAlternate_Click(ByVal sender As Object, ByVal e As EventArgs)

Me.dgv.RowsDefaultCellStyle.BackColor = Color.White

Me.dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.Aquamarine

End Sub

Tip 13 - 문자열 포맷 바꾸기

The DataGridView exposes properties that enable you to format data such as displaying a currency column in the culture specific currency or displaying nulls in a desired format and so on.

Private Sub btnFormat_Click(ByVal sender As Object, ByVal e As EventArgs)

' display currency in culture-specific currency for

Me.dgv.Columns("Freight").DefaultCellStyle.Format = "c"

' display nulls as 'NA'

Me.dgv.DefaultCellStyle.NullValue = "NA"

End Sub

Tip 14 – DataGridView 필드 순서바꾸기

In order to change the order of columns, just set the DisplayIndex property of the DataGridView to the desired value. Remember that the index is zero based.

Private Sub btnReorder_Click(ByVal sender As Object, ByVal e As EventArgs)

dgv.Columns("CustomerID").DisplayIndex = 5

dgv.Columns("OrderID").DisplayIndex = 3

dgv.Columns("EmployeeID").DisplayIndex = 1

dgv.Columns("OrderDate").DisplayIndex = 2

dgv.Columns("Freight").DisplayIndex = 6

dgv.Columns("ShipCountry").DisplayIndex = 0

dgv.Columns("ShipName").DisplayIndex = 4

End Sub