PowerShell Studio: Working with the DataGridView Control

We often receive questions in our support forum about how to use the DataGridView Control in PowerShell Studio forms. Many of the inquiries are about editing, highlighting, working with control events, and the dataset (DataTable Class) properties. This article will answer some of these questions and provide examples for using the DataGridView control.

About the DataGridView Control

The DataGridView control provides a visual interface to a data object. It gives the ability to display and allow editing in a tabular format. In a GUI application, the scripters can take advantage of using this control to present the data to the user.

The dataset displayed in this control needs to be a [System.Data.DataTable] .NET object type. This allows us to add, update, and delete rows in the dataset object.

A screenshot of a cell phone

Description automatically generated

Sample Application

PowerShell Studio provides a series of form templates to get you started. For this sample application, we are going to pick the “Grid” template.

The application will have the ability to manipulate data and export the updated dataset object to a CSV file. The form will have two DataGridView controls: one to display/manipulate the data, and the other display the changes to the data. Then, when changes are accepted, the data will be exported to a CSV file.

The application will have four button controls to work with the dataset object in the DataGridView controls:

  1. Create – This button will create and display the data into the first DataGridView.
  2. Changes – This button will display all rows that have changed in the second DataGridView (deleted rows are not displayed).
  3. Accept – This button will approve the changes and export the data to a CSV file.
  4. Deny – This button is used to reverse all changes, putting back the original data.
A screenshot of a cell phone

Description automatically generated

The code behind the form will include a series of control events to handle dataset object manipulation in the DataGridView control.

Customizing the Template

To start, we will create a project using the “Grid” template found in the “Form Project” section.

To create the project, follow these steps:

  1. From the File ribbon tab, click on “File -> New“.
  2. Select the “New Form Project -> Form Project“.
  3. Give it a project name and click on the “Create” button to load the form project in the editor.
  4. Then, select the “Grid” template from the list of forms templates.
A screenshot of a social media post

Description automatically generated

The form project will open three files in the editor: “Startup.pss“, “Globals.ps1“, and “MainForm.ps1“.

A screenshot of a social media post

Description automatically generated

Note: You can close the “Startup.pss” file.

We are going to work with both the “Globals.ps1” and “Mainform.ps1” files.

In “Globals.ps1“, we will add the code to initialize the dataset object to be used globally throughout the application.

This sample dataset object will contain three columns:

[int32] Id
[string] FirstName
[string] LastName

Below is the code to be added to the “Globals.ps1” file containing the dataset table structure to be displayed to the DataGridView controls:

## - [ Section to initialize DataTable objects] - ##
## - Create DataTable:
$table = New-Object System.Data.DataTable;

## - Defining DataTable object columns and rows properties:
# - Column1 = "ID".
$column = New-Object System.Data.DataColumn;
$column.DataType = [System.Type]::GetType("System.Int32");
$column.ColumnName = "Id";
$table.Columns.Add($column);

# - Column2 = "First Name".
$column = New-Object System.Data.DataColumn;
$column.DataType = [System.Type]::GetType("System.String");
$column.ColumnName = "FirstName";
$table.Columns.Add($column);

# - Column3 = "Last Name".
$column = New-Object System.Data.DataColumn;
$column.DataType = [System.Type]::GetType("System.String");
$column.ColumnName = "LastName";
$table.Columns.Add($column);

Next, we will change the name of the existing DataGridView control:

  1. Select the “MainForm.ps1” file.
  2. Click on the Designer tab; in the form, click on the DataGridView control.
  3. Go to the Properties pane, “Design” section, and find the “Name” property.
  4. Change the name from “DataGridViewResults” to “DataGridView1“.
A screenshot of a computer

Description automatically generated

Then, resize the existing DataGridView control to make room for the second DataGridview control.

Next, click on the “Load” button control, then move it to the top of the “DataGriudView1” control. Proceed to rename the text from “Load” to “Create”:

  1. In the Properties pane, go to the “Appearance” section.
  2. In the “Text” property, change the text from “Load” to “Create”.

This step will automatically rename the button control from “$ButtonLoad” to “$ButtonCreate”.

A screenshot of a cell phone

Description automatically generated

To add the second DataGridView control, drag/drop the control from the Toolbox pane. The control will be automatically named: “DataGridView2“.

A screenshot of a social media post

Description automatically generated

Now we have two DataGridView controls:

  • DataGridView1” – Displays a dataset with the ability to Add, Change, and *Delete rows.
  • DataGridView2” – Displays dataset changes.

*Note: Deleted rows are not displayed in the form.

Next we will work with the “DataGridView1” control properties.

DataGridView Properties

As previously mentioned, when you drag/drop a control into the form there are predefined settings to help speed up the development process. Using an existing PowerShell Studio template also speeds up the development time.

Below is an example of a few properties that can alter the behavior of the DataGridView control (highlighted in yellow):

A screenshot of a computer

Description automatically generated

For this application, the following changes are needed in the “DataGridView1” control:

  1. Select the “MainForm.ps1” file.
  2. Click on the Designer tab; in the form, click on the “DataGridView1” control.
  3. In the Properties pane, change the properties value in the following two sections:
    1. Behavior – Properties:
      • AllowUserToAddRows = True
      • AllowUserToDeleteRows = True
      • ReadOnly = False
    2. Layout – Property:
      • AutoSizeColumnsMode = “DisplayedCells”

There are no changes to the “DataGridView2” control, so we will leave it with the default property values.

Next we will add the three Button controls to the form.

Adding the Button Controls

The form “Grid” Template initially includes two buttons: the “Load” and the “Exit” buttons. We already renamed the text of the “Load” button to “Create”, which automatically changes the button control name to “$ButtonCreate”.  We will leave the “Exit” button unchanged.

To add three new button controls to the form, go to the Toolbox pane and drag/drop the Button control into the form and rename accordingly: “Changes“, “Accept“, and “Deny“.

A screenshot of a cell phone

Description automatically generated

Now we will work with the code to manipulate the dataset object in these control events.

Understanding the Dataset Object

To start building the code, we need to understand the dataset object from the .NET perspective. The dataset object needs to be a [System.Data.DataTable] .NET type, which allows us to use the object properties and methods in our code to interact with the DataGridView control.

You can explore the Microsoft .NET framework “DataTable” class documentation for both .NET Framework 4.x and .NET Core documentation.

There are three methods we are going to use in our code:

  • .NewRow()” – To add data rows in the DataGridView1.
  • .AcceptChanges()” – To accept and display the changes in both DataGridView controls.
  • .RejectChanges()” – To deny and return changes to the original state in the DataGridView1 control.

What about handling deleted rows? Deleted rows cannot be displayed in the DataGridView control; they are removed from the dataset object.

Now that we have identified the dataset object methods to use, we can start working on creating the code for the control events.

Code Behind

Below is the flow for manipulating the data in this application as we add the code.

The Button Controls flow:

  • Create -> Changes ->Accept (Export to Csv file) or Deny (Changes Rejected).

The DataGridView events are the action taken while editing the dataset object row:

  • CellClick/CellEndEdit/CellLeave – These events happen when editing a row.
  • KeyPress – When pressing the Esc key, backs out while editing the row (except when the row has been updated or deleted).
  • UserDeletingRow – When pressing the Delete key on the selected row, enables some buttons.

Below is the scriptblock code for each of the four Button Control events:

  • Create” – The “$buttonCreate_Click”: (replace existing template code with this one)
$buttonCreate_Click = {
	#TODO: Place custom script here
	
	## - Add Records to datatable dataset object:
	$i = 0;
	$row = $table.NewRow();
	$row["Id"] = ++$i;
	$row["FirstName"] = "Lou";
	$row["LastName"] = "Feriggno";
	$table.Rows.Add($row);
	
	$row = $table.NewRow();
	$row["Id"] = ++$i;
	$row["FirstName"] = "Tony";
	$row["LastName"] = "Zabala";
	$table.Rows.Add($row);
	
	$row = $table.NewRow();
	$row["Id"] = ++$i;
	$row["FirstName"] = "Cesar";
	$row["LastName"] = "Romero";
	$table.Rows.Add($row);
	
	## - Save changes to the table:
	$table.AcceptChanges();
	
	## - Display custom data created:
	$datagridview1.DataSource = $table;

	## - Enable button to display changes to the DataTable:
	$buttonChanges.Enabled = $false;
	$buttonCreate.Enabled = $false;
	
}
  • Changes” – The “$buttonChanges_Click”:
$buttonChanges_Click = {
	
	#TODO: Place custom script here
	
	## - Display changes made form the original into the 2nd DataGridView:
	$datagridview2.DataSource = $table.GetChanges();
	
	## - Enable both "Accept" and "Deny" buttons: (True)
	$buttonAccept.Enabled = $true;
	$buttonDeny.Enabled = $true;
	
}
  • Accept” – The “$buttonAccept_Click”:
$buttonAccept_Click={
	#TODO: Place custom script here
	
	## - Display messagebox:
	$OkCancel = [System.Windows.Forms.MessageBox]::Show("Ok! To Accept, or Cancel?", "Accepted Changes", [System.Windows.Forms.MessageBoxButtons]::OKCancel)
	if ($OkCancel -eq [System.Windows.Forms.DialogResult]::OK)
	{
		## - To keep edited changes:
		$table.AcceptChanges();
		
		## - Creating Output file with Accepted changes:
		[System.Windows.Forms.MessageBox]::Show("Export to a CSV file: $($env:USERPROFILE)\Documents\UpdatedUserList.csv", "Accepted with Changes Exported", [System.Windows.Forms.MessageBoxButtons]::OK)
		$table | Export-Csv -Path $env:USERPROFILE\Documents\UpdatedUserList.csv -Encoding 'UTF-8' -NoTypeInformation;
		
		## - Refresh DataGridView changes:
		$datagridview1.DataSource = $table;
		$datagridview2.DataSource = $null;
		
	};
	
}
  • Deny” – The “$buttonCreate_Click”:
$buttonDeny_Click={
	#TODO: Place custom script here
	
	## - To deny changes and put back the original data:
	$table.RejectChanges();
	
	#$view = New-Object System.Data.DataView($table);
	$datagridview1.DataSource = $table;
	$datagridview2.DataSource = $null;
	
	## - Enable both "Accept" and "Deny" buttons: (FALSE)
	$buttonAccept.Enabled = $false;
	$buttonDeny.Enabled = $false;
	
}

Notice that the code in these Button controls is where we use the dataset object methods to manipulate the data: “$table.AcceptChanges()”, “$table.GetChanges()”, and “$table.RejectChanges()”.

Next, we add the code for the “DataGridView1” control events that are triggered when the user is manipulating the dataset in the form.

Follow these steps:

  1. In the “Designer” tab, select the DataGridView1 Control.
  2. Right-Click on the DataGridView1 Controls, and select “Add Event”.
  3. From the list of Events, select the following five events:
    • CellClick
    • CellEndEdit
    • CellLeave
    • KeyPress
    • UserDeletingRow
A screenshot of a cell phone

Description automatically generated

These events are individually triggered while performing the above editing action against the dataset object in the form.

While editing the dataset object, the selected row is highlighted, and the Button controls are enabled or disabled depending on the event conditions.

Below are the five DataGridView1 control code scriptblock events:

  • $datagridview1_CellClick” event:
$datagridview1_CellClick=[System.Windows.Forms.DataGridViewCellEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellEventArgs]
	#TODO: Place custom script here
	
	## - Troubleshooting - Get current row number:
	#Write-Host "Value => $($datagridview1.CurrentRow.Index.ToString())";
	
	## - On "Cell-Click" highlight changed row to color - "Coral":
$datagridview1.Rows[$datagridview1.CurrentRow.Index].DefaultCellStyle.BackColor = [System.Drawing.Color]::Coral;
	
}
  • $datagridview1_CellEndEdit” event:
$datagridview1_CellEndEdit=[System.Windows.Forms.DataGridViewCellEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellEventArgs]
	#TODO: Place custom script here
	
	## - On "Cell-End-Edit" highlight row to color - "White":
	$datagridview1.Rows[$datagridview1.CurrentRow.Index].DefaultCellStyle.BackColor = [System.Drawing.Color]::White;
	
	## - Enable both "Accept" and "Deny" buttons: (TRUE)
	$buttonChanges.Enabled = $true;
	$buttonAccept.Enabled = $true;
	$buttonDeny.Enabled = $true;
	
}
  • $datagridview1_CellLeave” event:
$datagridview1_CellLeave=[System.Windows.Forms.DataGridViewCellEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellEventArgs]
	#TODO: Place custom script here
	
	## - On "Cell-Leave" highlight row to color - "White":
	$datagridview1.Rows[$datagridview1.CurrentRow.Index].DefaultCellStyle.BackColor = [System.Drawing.Color]::White;
	
}
  • $datagridview1_KeyPress” event:
$datagridview1_KeyPress=[System.Windows.Forms.KeyPressEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.KeyPressEventArgs]
	#TODO: Place custom script here
	
	## - On "Key-Press" when the Esc-Key is press, change the highlighted color back to "White":
	$datagridview1.Rows[$datagridview1.CurrentRow.Index].DefaultCellStyle.BackColor = [System.Drawing.Color]::White;
	$buttonChanges.Enabled = $false;
	
}
  • $datagridview1_UserDeletingRow” event:
$datagridview1_UserDeletingRow=[System.Windows.Forms.DataGridViewRowCancelEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.DataGridViewRowCancelEventArgs]
	#TODO: Place custom script here
	
	## - On "User-Deleting-Row" enable both "Accept" and "Deny" buttons: (TRUE)
	$buttonAccept.Enabled = $true;
	$buttonDeny.Enabled = $true;
	
}

There is one last thing to do.

Form BorderStyle and Anchor Controls

Sometimes the data displayed might not fit in the DataGridView control. To remedy this, we set the “Layout | Scrollbars” property value to “Both” in the control Properties pane, which allows the control to show both “Horizontal” and “Vertical” scroll bars when the data has overfilled the DataGridView area.

By default, the “FormBorderStyle” property in the forms Properties pane is set to “Sizable“, which allows the application to be resized manually.

So what happened to the added controls when resizing the form? If we neglect to use the property to “Anchor” the controls, it would show them out of place.

There are two ways to handle this issue:

  • Set the form “Appearance | FormBorderStyle” property value from “Sizable” to “FixedSingle“, which prevents the resizing of the application. No control anchoring is required.
  • Or, set the form “Layout | Anchor” property for all controls added in the form. This will keep it lined up when resizing the application. Also, in this scenario the form “Appearance | FormBorderStyle” property value will remain “Sizable“.

For this example we will change the “Appearance | FormBorderStyle” property value to “FixedSingle“. This way we don’t need to change the “Anchor” property for any of the controls.

Now the application is complete and ready to run.

A screenshot of a computer

Description automatically generated

Download the sample project Grid application: ProjectDataGridView01.zip

Summary

As you can see, this sample application shows how to work with your dataset object in a DataGridView control. Use any of the available templates to start developing your Windows PowerShell (or PowerShell 7) application.

Although we hard-coded the data in the sample application, the template provides two helper functions:

  • ConvertTo-DataTable – This function converts any object to [System.Data.DataTable] type.
  • Update-DataGridTable – This function assist in updating an existing DataGridView control with the [System.Data.DataTable] object.
A screenshot of a cell phone

Description automatically generated

In this example we did not make use of these functions because there was no data to be converted, but they can improve the application by enabling a way to convert results for display in the DataGridView control in the form.

Now it is all in your hands! Be creative and have fun creating Windows applications in PowerShell Studio!

Max Trinidad is a Technology Evangelist at SAPIEN Technologies Inc., and a Microsoft PowerShell MVP. You can reach him at maxt@sapien.com