Page 1 of 1

Exploring DataGridView (ComboBox, Checkbox, Increment, BindingSource) Rate Topic: -----

#1 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6374
  • View blog
  • Posts: 25,754
  • Joined: 12-December 12

Posted 02 May 2016 - 03:30 AM

This is an exploration of a WinForm's DataGridView which demonstrates and discusses a number of topics.

I am not demonstrating a recommended approach. The recommended approach is to use a DAL (Data Access Layer), with an ORM (object-relational mapping) framework such as the Entity Framework.

Creating a Data Access Layer
(This tutorial is with ASP.NET but I think provides a reasonable introduction to the subject.)

Database To Typed-DataSet, With BindingSource

I am also advised that, even though MS encourages the use of BindingSources, the DataGridView itself is already a BindingSource of a kind. In the long term though, if I venture towards Entity Framework, I'm sure this will become clearer. For the moment, I am happy to continue to use them.

My code does not have any code separation; customizing of DataTable and DataGridView (DGV) columns all occur in the same code block. My purpose was to explore aspects of a DGV, to understand how they work, such as how a DataGridViewComboBoxColumn could work with data from a second, related, table. Topics that I encountered and discuss include:

  • Using DataAdapters and BindingSources
  • Populating a ComboBox from a second, related, table
  • Customizing DataTable columns
  • Handling increment field insertions
  • Handling Null values
  • Customizing DataGridView columns
  • Using ComboBox and Checkbox column
  • Formatting and Aligning columns
  • Navigating the BindingSource
  • Commiting changes to the database
  • Handling default values

Attached Image

I have the following blog entry with similar code that demonstrates how to retrieve increment values (autonumbers) without re-filling the DataSet as I do in this tutorial. It also demonstrates a Master/Details form.

VB.NET Get SQL Server INCREMENT Value

First you need a database with two tables. Mine has Staff and Department tables, where DeptID (int) in the Staff table is a foreign key, DeptID in the Department table is its primary key.

Attached Image

StaffID is an increment field.

Attached Image

Here is the table creation code from SQL Server Management Studio.

Spoiler


Spoiler

(Some of this stuff is superfluous, such as ansi_padding as it defaults to ON anyway.)

You'll probably want to populate these with some data, it is sensible to create a few departments first.

You could use your own tables, as long as you have two tables that are related that can be used with a ComboBox column.



Create a new WinForms application. The form itself is simple, just a DataGridView (dgvStaff) and a number of Buttons whose names are btnFirst, btnPrevious, etc..

Connect to your database and define some class-level variables:
Imports System.Data.SqlClient

Public Class frmStaff
    Private _sConn As String = "Server=.\SQLExpress;Database=StaffDB;Trusted_Connection=Yes"
    Private _conn As New SqlConnection(_sConn)

    Private _dsStaff As New DataSet()
    Private _daStaff As SqlDataAdapter
    Private _daDept As SqlDataAdapter
    Private _bsStaff As New BindingSource()
    Private _bsDept As New BindingSource()


(the full code is shown at the end of this tutorial)

All the action then begins in the form's Load event:
    Private Sub frmStaff_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim sSqlStaff As String = "SELECT StaffID, FirstName, LastName, DeptID, "
        sSqlStaff &= "Grade, Salary, StartDate, EndDate, Bonus FROM Staff"
        Dim cmdStaff As New SqlCommand(sSqlStaff, _conn)
        _daStaff = New SqlDataAdapter(cmdStaff)


I started with SELECT * FROM Staff (using * is not a good idea anyway) and dgvStaff.AutoGenerateColumns = True. This is reasonable to prove that you can retrieve the data from your database. However, it is not possible to simply change a TextBox column to a ComboBox column, and I needed to tailor some of the DataTable columns anyway, so I list the columns explicitly.

Let's get, and Fill, the Department data with some similar steps:
        Dim sSqlDept As String = "SELECT DeptID, Department FROM Department ORDER BY Department"
        Dim cmdDept As New SqlCommand(sSqlDept, _conn)
        _daDept = New SqlDataAdapter(cmdDept)
        _daDept.Fill(_dsStaff, "Departments")


Notice that we have one DataSet (_dsStaff) that will contain two DataTables. Also notice that we do not need to explicitly open and close the connection, the Fill method does this for us. (This is a common misunderstanding.)

In order to perform updates against the database we need to generate valid INSERT, UPDATE and DELETE statements. If we had used * (all columns) then, because our table also contains a primary key, these would have been generated automatically for us. We can use a CommandBuilder to generate these statements:
        'need CommandBuilder to complete updates
        Dim cbStaff As New SqlCommandBuilder(_daStaff)


We don't need to do anything else, New SqlCommandBuilder creates the default statements we need. (In my blog entry I customize the InsertCommand in order to retrieve the increment value.)

Having filled a DataTable of the DataSet with the Department data, let's associate it with its BindingSource:
        _bsDept.DataSource = _dsStaff
        _bsDept.DataMember = "Departments"


Before Filling the corresponding Staff DataTable, we will define its columns. The main reason to do this is to set the increment properties of the StaffID column, but I also would like to be explicit about the data types of each column, and to allow null values.
        With _dsStaff.Tables.Add("Staff")
            .Columns.Add("StaffID", System.Type.GetType("System.Int32"))
            .Columns("StaffID").AutoIncrement = True
            .Columns("StaffID").AutoIncrementSeed = -1
            .Columns("StaffID").AutoIncrementStep = -1
            Dim pColumn() As DataColumn = {.Columns("StaffID")}
            .PrimaryKey = pColumn
            .Columns.Add("FirstName", System.Type.GetType("System.String"))
            .Columns.Add("LastName", System.Type.GetType("System.String"))
            .Columns.Add("DeptID", System.Type.GetType("System.Int32"))

            .Columns("DeptID").DefaultValue = _bsDept(0)("DeptID")  'first department

            .Columns.Add("Grade", System.Type.GetType("System.Byte"))
            .Columns.Add("Salary", System.Type.GetType("System.Decimal"))
            .Columns.Add("StartDate", System.Type.GetType("System.DateTime"))
            .Columns.Add("EndDate", System.Type.GetType("System.DateTime"))
            .Columns.Add("Bonus", System.Type.GetType("System.Boolean"))
            'SQL Server Data Type Mappings
            'https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

            .Columns("Grade").AllowDBNull = True
            .Columns("Salary").AllowDBNull = True
            .Columns("StartDate").AllowDBNull = True
            .Columns("EndDate").AllowDBNull = True
            .Columns("Bonus").AllowDBNull = True
        End With


The link mentioned in the code lists the system data-types that correspond to SQL Server data-types. Consider this code:
            .Columns("StaffID").AutoIncrement = True
            .Columns("StaffID").AutoIncrementSeed = -1
            .Columns("StaffID").AutoIncrementStep = -1


The increment values (aka counter, autonumbers) that we will see in the DGV will start at -1, then -2, -3, etc.. Only when the database is updated will these take on values generated by the database.
            .Columns("DeptID").DefaultValue = _bsDept(0)("DeptID")  'first department


The DGV has a DefaultValuesNeeded event but it is simpler to just set the default for the DataTable (because the BindingSource's AddNew method doesn't trigger this event, causing some duplication of code).

Now we can Fill the Staff DataTable:
        _daStaff.Fill(_dsStaff, "Staff")


Let's start to configure the DGV:
        dgvStaff.AutoGenerateColumns = False

        Dim col As New DataGridViewColumn()
        col.CellTemplate = New DataGridViewTextBoxCell()
        col.DataPropertyName = "StaffID"
        col.HeaderText = "Staff ID"
        col.DefaultCellStyle.Format = "d"
        dgvStaff.Columns.Add(col)


You'll see a lot of similar code for each column. This should all be extracted out perhaps to a helper class; perhaps some reflection (introspection) could be used to determine some of the columns' properties based on the field definitions? I suspect something like this happens somewhere within Entity Framework (my knowledge of EF is limited currently).

The DataPropertyName, significantly, will associate the column with a field of the table. I/we should also specify the Name of each column as they aren't assumed from the field names. I was lazy and omitted this step.

The FirstName and LastName are simple text columns:
        col = New DataGridViewColumn(New DataGridViewTextBoxCell())
        col.DataPropertyName = "FirstName"
        col.HeaderText = "First Name"
        dgvStaff.Columns.Add(col)

        col = New DataGridViewColumn(New DataGridViewTextBoxCell())
        col.DataPropertyName = "LastName"
        col.HeaderText = "Last Name"
        dgvStaff.Columns.Add(col)


The more complicated column is the DeptID, which is a DataGridViewComboBoxColumn:
        Dim comboCol = New DataGridViewComboBoxColumn()
        comboCol.DataSource = _bsDept
        comboCol.ValueMember = "DeptID"
        comboCol.DisplayMember = "Department"
        'staff table..
        comboCol.DataPropertyName = "DeptID"


  • The DataSource is the Department BindingSource;
  • ValueMember says what value (of the BindingSource) the column is associated with;
  • DisplayMember says what we will see in the DGV;
  • DataPropertyName says what value is stored for the column.

A few simpler properties follow:
        comboCol.HeaderText = "Department"
        comboCol.DefaultCellStyle.Format = "d"


The only remaining line for the ComboBox column is to add it to the grid:
        dgvStaff.Columns.Add(comboCol)


The next few columns are just TextBoxCells, the only significant difference between each being the formatting of the cells (e.g. "c" is for currency):
        col = New DataGridViewColumn(New DataGridViewTextBoxCell())
        col.DataPropertyName = "Grade"
        col.HeaderText = "Grade"
        col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        col.DefaultCellStyle.Format = "d"
        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        dgvStaff.Columns.Add(col)

        col = New DataGridViewColumn(New DataGridViewTextBoxCell())
        col.DataPropertyName = "Salary"
        col.HeaderText = "Salary"
        col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        col.DefaultCellStyle.Format = "c"
        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        dgvStaff.Columns.Add(col)

        col = New DataGridViewColumn(New DataGridViewTextBoxCell())
        col.DataPropertyName = "StartDate"
        col.HeaderText = "Start Date"
        col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        col.DefaultCellStyle.Format = "d"
        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        dgvStaff.Columns.Add(col)

        col = New DataGridViewColumn(New DataGridViewTextBoxCell())
        col.DataPropertyName = "EndDate"
        col.HeaderText = "End Date"
        col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        col.DefaultCellStyle.Format = "d"
        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        dgvStaff.Columns.Add(col)


It is possible to embed calendars in a DGV for DateTime columns:

How to: Host Controls in Windows Forms DataGridView Cells
Embedding Calendar (DateTimePicker) Control Into DataGridView Cell

Using a CheckBox for the Bonus column was straight-forward because it is a 'bit' field (in other databases, a binary or boolean field):
        Dim checkCol = New DataGridViewCheckBoxColumn()
        checkCol.DataPropertyName = "Bonus"
        checkCol.HeaderText = "Bonus?"
        checkCol.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        dgvStaff.Columns.Add(checkCol)


All that remains to do in the Load event is to wire up the Staff BindingSource (to the DataSet/DataTable) and set the DataSource of the DGV to this BindingSource:
        _bsStaff.DataSource = _dsStaff
        _bsStaff.DataMember = "Staff"

        dgvStaff.DataSource = _bsStaff
        dgvStaff.EditMode = DataGridViewEditMode.EditOnEnter
        'prevents having to click the ComboBox twice
        dgvStaff.AutoResizeColumns()
    End Sub


If you comment the EditMode line then you'll notice that the ComboBox has to be clicked twice before it displays its drop-down list.

AutoResizeColumns will adjust the column widths to suit the filled data.

The code for the first few buttons is straight-forward, simply moving to a different item of the BindingSource's list, if there is such an item:
    Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
        If _bsStaff.Count > 0 Then
            _bsStaff.MoveFirst()
        End If
    End Sub

    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
        If _bsStaff.Position > 0 Then
            _bsStaff.MovePrevious()
        End If
    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If _bsStaff.Position + 1 < _bsStaff.Count Then
            _bsStaff.MoveNext()
        End If
    End Sub

    Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
        If _bsStaff.Count > 0 Then
            _bsStaff.MoveLast()
        End If
    End Sub


New is also (apparently) straight-forward:
    Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
        _bsStaff.AddNew()
    End Sub


This does add a new row and takes us to it, but it is not the "next new row", the blank row that is always at the bottom, it appears before this. This is not the same behaviour as just clicking into the blank new row. This apparently minor difference is something that we'll have to keep an eye on.



Added: To get entire consistency between clicking the New button and just clicking directly into the new row, we could use this code:
    Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
        '_bsStaff.AddNew()
        dgvStaff.CurrentCell = dgvStaff.Rows(dgvStaff.NewRowIndex).Cells(1)
        dgvStaff.BeginEdit(True)
    End Sub




Now comes the trickiest part, and something that I spent a long time wrestling with and investigating. The Delete button. Originally I had just this:
    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        _bsStaff.RemoveCurrent()
    End Sub


but then I noticed that if I click into the new row at the bottom and immediately press Delete, it deletes the row before this one?! Look at the screenshot:

Attached Image

Notice that, although there is a cell selected in the new (blank) row, the record pointer - the little arrow - is on the previous (current) row.

Anyway, after a lot of time and effort, I realise that I cannot just RemoveCurrent(), I have to take action according to the current state of the row. Here's the result of my endeavours:
    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        If _bsStaff.Current Is Nothing Then
            Exit Sub        'nothing to consider deleting
        End If
        Dim currentRow = CType(_bsStaff.Current, DataRowView).Row

        Dim state = currentRow.RowState
        Select Case state
            Case DataRowState.Added
                'newly added, just remove it
                _bsStaff.RemoveCurrent()
            Case DataRowState.Deleted
                MessageBox.Show("Row already deleted.", "Delete")
            Case DataRowState.Detached
                _bsStaff.CancelEdit()
            Case DataRowState.Modified, DataRowState.Unchanged
                If dgvStaff.SelectedCells.Count > 0 AndAlso _
                        dgvStaff.SelectedCells(0).RowIndex = dgvStaff.NewRowIndex Then
                    'a new, blank, row, is attempted to be deleted
                    '(the current row is the one before this)
                    _bsStaff.CancelEdit()
                    Exit Sub
                End If
                Dim sMemberMessage = String.Format("Delete record ({0} {1} {2}) ?", _
                        _bsStaff.Current("StaffID"), _bsStaff.Current("FirstName"), _bsStaff.Current("LastName"))
                If MessageBox.Show(sMemberMessage, "Confirm", MessageBoxButtons.YesNo, _
                                   MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                    _bsStaff.RemoveCurrent()
                End If
            Case Else
                'do nothing
        End Select
    End Sub


Most of this is clear and straight-forward, it is this part that was an effort:
            Case DataRowState.Modified, DataRowState.Unchanged
                If dgvStaff.SelectedCells.Count > 0 AndAlso _
                        dgvStaff.SelectedCells(0).RowIndex = dgvStaff.NewRowIndex Then
                    'a new, blank, row, is attempted to be deleted
                    '(the current row is the one before this)
                    _bsStaff.CancelEdit()
                    Exit Sub


dgvStaff.SelectedCells(0).RowIndex = dgvStaff.NewRowIndex Because the selected cell is still in the new row, this tells me that the user has clicked into it and immediately pressed Delete, so the row can be abandoned (rather than removing the previous row). They must have pressed Delete immediately, without typing anything else, as otherwise the row would not be in an Unchanged state (and the test condition would not be met); in which case, the earlier code would already have handled the abandoning of the row.

So, I've been able to utilise BindingSource methods and properties until this point but, for this very specific circumstance, I've had to examine properties of the DGV itself (SelectedCells and NewRowIndex).

You'll recognise when testing this that any new rows will just be discarded on pressing Delete (similar behaviour occurs if you just press the Escape key a couple of times); it is only when deleting existing rows that you will be asked to confirm the action.

The remaining code is much simpler. Here is the Save button:
    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        SaveAll()
    End Sub

    Private Sub SaveAll(Optional messages As Boolean = True)
        Try
            _bsStaff.EndEdit()
            If _dsStaff.HasChanges Then
                _daStaff.Update(_dsStaff.Tables("Staff"))
                If messages Then
                    MessageBox.Show("Database updated.", "Save")
                End If
            Else
                If messages Then
                    MessageBox.Show("Nothing to update.", "Save")
                End If
            End If
        Catch ex As SqlException
            MessageBox.Show(ex.Message, "Data Exception")
            Debug.Print(ex.Message)
        End Try
    End Sub


The messages argument determines whether confirmation messages should be displayed to the user as SaveAll might be called as part of a longer process where communication with the user is not necessary.

All we need to do is to check whether there are any changes in the DataSet and, if so, Update the database using the adapter. (I am not updating the Department table as there is no provision for new departments to be added with this application. My blog-entry shows how to update related tables.)

The Exception is most likely to be because of null values or invalid date-values. This could be expanded upon with validation features.

To obtain the actual increment values I use a Reload button to re-fill the DataSet. My blog-entry mentioned earlier shows how to obtain these values without reloading the entire DataSet.
    Private Sub btnReload_Click(sender As Object, e As EventArgs) Handles btnReload.Click
        Dim confirm As Windows.Forms.DialogResult = Windows.Forms.DialogResult.Cancel

        confirm = MessageBox.Show("Save current changes?", "Confirm", MessageBoxButtons.YesNoCancel, _
                           MessageBoxIcon.Question)
        If confirm = Windows.Forms.DialogResult.Yes Then
            SaveAll(True)
        ElseIf confirm = Windows.Forms.DialogResult.Cancel Then
            Exit Sub
        End If
        'either has confirmed saving changes, or chosen to abandon them
        _dsStaff.Tables("Staff").Clear()
        _daStaff.Fill(_dsStaff, "Staff")    'Departments are unlikely to change
    End Sub


Finally, I include a process on FormClosing to check whether data changes should be abandoned or the user can return to the form and decide whether to commit these changes.
    Private Sub frmStaff_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        _bsStaff.EndEdit()
        If _dsStaff.HasChanges Then
            If MessageBox.Show("Abandon changes?", "Abandon", MessageBoxButtons.YesNoCancel, _
                               MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) <> Windows.Forms.DialogResult.Yes Then
                e.Cancel = True
            End If
        End If
    End Sub




I will stress again that this tutorial is not to be treated as a pattern for working with databases. It is an exploration intended to increase our knowledge of certain aspects of DataGridViews, DataTables and BindingSources.



Here is the full code:

Spoiler


Is This A Good Question/Topic? 2
  • +

Replies To: Exploring DataGridView (ComboBox, Checkbox, Increment, BindingSource)

#2 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6374
  • View blog
  • Posts: 25,754
  • Joined: 12-December 12

Posted 02 May 2016 - 04:48 AM

I feel the urge to add a comment.

Many of my tutorials are quite long and detailed. This is, firstly, because they are usually on quite "big" subjects, but also because I like to be "thorough". This is in part because I use my tutorials myself for reference.

I also, if they are of reasonable(?) length (in terms of scrolling), like to keep them as one tutorial, rather than splitting them across two or more tutorials. (I have split them a number of times.)

If you wander through other tutorials you'll notice many that are much shorter and are split across several threads.

The point I want to make is that, if you are considering writing a tutorial, it can be fairly brief and focussed. You can also, if appropriate, split it across more than one tutorial/thread. (The only proviso I will add is that the transition from one section to another should be smooth, not just an abrupt cutting of the text at one point and continuing from the next section. For example, provide a direct link from one section to the next.)

Tutorial submissions are encouraged :)
Was This Post Helpful? 1
  • +
  • -

#3 maceysoftware  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 348
  • View blog
  • Posts: 1,493
  • Joined: 07-September 13

Posted 03 May 2016 - 12:55 AM

Hello Andrew,

Really good tutorial like always, with lots of really good examples and techniques, I especially liked how you handled the delete code block, never seen it done like that however it makes more sense than what I am currently using.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6374
  • View blog
  • Posts: 25,754
  • Joined: 12-December 12

Posted 03 May 2016 - 09:50 AM

Thank you.

It still niggles me a little though that Microsoft encourages the use of BindingSources, for example:

Quote

The DataGridView control supports the standard Windows Forms data binding model, so it will bind to a variety of data sources. In most circumstances, however, you will bind to a BindingSource component which will manage the details of interacting with the data source.

yet I still had to tap into the DataGridView properties (SelectedCells and NewRowIndex) in order to resolve the obscure delete issue.

Still, I have to bear in mind that I'm not using a DAL and, with an architectural pattern in place, there will be a clearer separation of code.

Quote

with lots of really good examples and techniques..

I was debating for a long time whether to post it as a tutorial or not, because it isn't following best practices. But, as you say, it contains so much information that I decided it should be posted.



Concerning the DGV in general, I came across this old (2005) document DataGridView FAQs (NB this is a direct download link) which is still very useful.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1