DataGridView with SuggestAppend comboBox columns and dynamic Cell assignment

Question

I have been doing some research and could not find an answer for this, probably I'm not asking the right question or what I'm attempting might not be possible.

So, supposing I have a DataTable binded to a DataGridView, is it possible to use SuggestAppend in some of the data source columns?

As an example (code below), I have a DataGridView with columns Employee and Status. The Status column is a DataGridViewComboBoxColumn with items Active and Withdraw. The grid has a DataTable as source which has the same columns (Employee & Status), I'm using this because it's easier to export the data once modified however there might be a better way.

So, is it possible to have my DataTable use the columns I've created for the DataGridView ?

Code for testing:

$mainForm = New-Object System.Windows.Forms.Form
$mainForm.StartPosition = 'CenterScreen'
$mainForm.FormBorderStyle = 'Fixed3D'
$mainForm.Text = 'Test'
$mainForm.WindowState = 'Maximized'

$bounds = ($mainForm.CreateGraphics()).VisibleClipBounds.Size

$dataGrid = New-Object System.Windows.Forms.DataGridView
$dataGrid.Size = New-Object System.Drawing.Size(($bounds.Width-20),($bounds.Height-140))
$dataGrid.Location = New-Object System.Drawing.Size(10,60)
$dataGrid.AllowUserToAddRows = $true
$dataGrid.SelectionMode = 0
$dataGrid.MultiSelect = $true
$dataGrid.ReadOnly = $false
$dataGrid.RowHeadersVisible = $false
$dataGrid.ColumnHeadersBorderStyle = 2
$dataGrid.EnableHeadersVisualStyles = $true

$col1 = New-Object System.Windows.Forms.DataGridViewComboBoxColumn
$col1.Name = 'Status'
$col1.HeaderText = 'Status'
$col1.Items.AddRange('Active','Withdraw')

$dataGrid.Columns.Add([System.Windows.Forms.DataGridViewColumn],'Employee')
$dataGrid.Columns.Add($col1)

$dataGrid.Add_EditingControlShowing({
    
    $box = $_.Control -as [System.Windows.Forms.ComboBox]
    
    if($box)
    {
        $dataGrid.EditingControl.DropDownStyle = [System.Windows.Forms.ComboBoxStyle]::DropDown
        $dataGrid.EditingControl.AutoCompleteMode = [System.Windows.Forms.AutoCompleteMode]::SuggestAppend
    }
})

$dataGrid.AutoSizeColumnsMode = [System.Windows.Forms.DataGridViewAutoSizeColumnMode]::Fill
#$dataGrid.Columns[-1].AutoSizeMode = [System.Windows.Forms.DataGridViewAutoSizeColumnMode]::Fill

$mainForm.Controls.Add($dataGrid)

#### Begin of DataTable as Source here

$source = @'
Employee,Status
user.example1,Active
user.example2,Withdraw
user.example3,Withdraw
user.example4,Active
'@ | ConvertFrom-Csv

$columns = $source[0].PSobject.Properties.Name

$table = New-Object System.Data.DataTable

foreach($column in $columns)
{
    $i = New-Object System.Data.DataColumn
    $i.DataType = [string]
    $i.ColumnName = $column
    $table.Columns.Add($i)
}

foreach($line in $source)
{
    $row = $table.NewRow()   
    foreach($column in $columns)
    {
        $row.$column = $line.$column
    }
    $table.Rows.Add($row)
}

$dataGrid.DataSource = $table

$mainForm.Add_Shown({ $mainForm.Activate() })
$mainForm.ShowDialog()

Answer 1

I got this working thanks to @JohnG's guidance, if this is useful to you thank him not me.

What's included on the example code?

• DataTable bounded to DataGridView
• DataGrid Columns paired with DataTable columns
• DataGridViewComboBoxColumn with DropDown and SuggestAppend
• Dynamic cell assignment, this in theory could be done using DataColumn's expression but I couldn't get IIF conditions working on PowerShell so I used the .Add_CellEndEdit event to update the DataBound item. i.e.: $this.CurrentRow.DataBoundItem['Code'] = '1'

Example Screenshot

Code Snippet

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
Add-Type -AssemblyName PresentationFramework

#### Begin of DataTable as Source here

$source = @'
Employee,Status,Code
user.example1,Active,1
user.example2,Withdraw,0
user.example3,Withdraw,0
user.example4,Active,1
user.example5,Withdraw,0
user.example6,Active,1
'@ | ConvertFrom-Csv

$columns = $source[0].PSobject.Properties.Name

$table = New-Object system.Data.DataTable

$col = New-Object System.Data.DataColumn
$col.DataType = [string]
$col.ColumnName = 'Employee'
$table.Columns.Add($col)

$col = New-Object System.Data.DataColumn
$col.DataType = [string]
$col.ColumnName = 'Status'
$table.Columns.Add($col)

$col = New-Object System.Data.DataColumn
$col.DataType = [string]
$col.ColumnName = 'Code'
$table.Columns.Add($col)

foreach($line in $source)
{
    $row = $table.NewRow()
    foreach($column in $columns)
    {
        $row.$column = $line.$column
    }
    $table.Rows.Add($row)
}

##########

$mainForm = New-Object System.Windows.Forms.Form
$mainForm.StartPosition = 'CenterScreen'
$mainForm.FormBorderStyle = 'Fixed3D'
$mainForm.Text = 'Test'
$mainForm.WindowState = 'Maximized'

$bounds = ($mainForm.CreateGraphics()).VisibleClipBounds.Size

$dataGrid = New-Object System.Windows.Forms.DataGridView
$dataGrid.Size = New-Object System.Drawing.Size(($bounds.Width-20),($bounds.Height-140))
$dataGrid.Location = New-Object System.Drawing.Size(10,60)
$dataGrid.AllowUserToAddRows = $false
$dataGrid.SelectionMode = 4
$dataGrid.MultiSelect = $true
$dataGrid.ReadOnly = $false
$dataGrid.RowHeadersVisible = $false
$dataGrid.ColumnHeadersBorderStyle = 2
$dataGrid.EnableHeadersVisualStyles = $true

# Pairing DataPropertyName with DataTable's column Names
$col0 = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
$col0.HeaderText = 'Employee'
$col0.DataPropertyName = 'Employee'
$col0.SortMode = 'NotSortable'
$dataGrid.Columns.Add($col0)

$col1=New-Object System.Windows.Forms.DataGridViewComboBoxColumn
$col1.HeaderText = 'Status'
$col1.DataPropertyName = 'Status'
# Using DataTable 'Status' column unique values as DataSource
$col1.DataSource = $table.Status | Select-Object -Unique
$col1.SortMode = 'NotSortable'
$dataGrid.Columns.Add($col1)

$col2 = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
$col2.HeaderText = 'Code'
$col2.DataPropertyName = 'Code'
$col2.ReadOnly = $True
$col2.SortMode = 'NotSortable'
$dataGrid.Columns.Add($col2)

$dataGrid.Add_EditingControlShowing({
    
    # Not entirely sure how this works but it works lol
    # Basically the only column that can behave as WinForms ComboBox is
    # DataGridViewComboBoxColumn. When this is True we can enable DropDown and SuggestAppend
    # to the EditingControl. This code is emulated from C# and not sure if it's the right approach
    # but still it works.

    if($_.Control -as [System.Windows.Forms.ComboBox])
    {
        $this.EditingControl.DropDownStyle = [System.Windows.Forms.ComboBoxStyle]::DropDown
        $this.EditingControl.AutoCompleteMode = [System.Windows.Forms.AutoCompleteMode]::SuggestAppend
    }
})

$dataGrid.Add_CellEndEdit({

    $row = $this.CurrentRow.DataBoundItem

    # I tried this to work in both ways, meaning, the value of 'Status' would
    # dynamically update the value of 'Code' and vice versa but the DGV gets super buggy
    # Seems like you need 'INotifyPropertyChanged' on the bound DataTable but couldn't
    # figure out how to make it work in PowerShell yet.
    # Source: https://stackoverflow.com/questions/1516252/how-to-programmatically-set-cell-value-in-datagridview

    if($row.Status -eq 'Active')
    {
        $this.CurrentRow.DataBoundItem['Code'] = '1'
    }
    else
    {
        $this.CurrentRow.DataBoundItem['Code'] = '0'
    }
})

$dataGrid.AutoSizeColumnsMode = [System.Windows.Forms.DataGridViewAutoSizeColumnMode]::Fill

$dataGrid.Add_DataError({

    # Error handling here
    # https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.datagridview.dataerror?view=net-5.0
    $_.Cancel = $true
})

$mainForm.Controls.Add($dataGrid)

$dataGrid.DataSource = $table

$bulkUpdateBtn = New-Object System.Windows.Forms.Button
$bulkUpdateBtn.Size = New-Object System.Drawing.Size(85,30)
$bulkUpdateBtn.Location = New-Object System.Drawing.Size(($dataGrid.Width-74),($dataGrid.Height+70))
$bulkUpdateBtn.Text = "Bulk Update"
$bulkUpdateBtn.Add_Click({
    
    # TO DO

})
$mainForm.Controls.Add($bulkUpdateBtn)

$mainForm.Add_Shown({ $mainForm.Activate() })
$mainForm.ShowDialog()