Binding SQlServer Data to WinForms with PowerShell

An updatable dataset

(Preliminiary - to be updated)

I have decided to jump ahead and post an example project that retrieves data from SQLServer and produces an updatable dataset and binding source.
We cannot do this in this way with the XML dataset but SQLServer and most databases support direct binding and updating. The SQLDataAdapter class allows for direct updates of disconnected back-end data.
Here is the trick in a nutshell:

$ds = New-Object System.Data.DataSet

$conn = New-Object System.Data.SqlClient.SQLConnection($connStr)

$cmd = $conn.CreateCommand()
$cmd.CommandText = $sql

$script:adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)

$cmdBldr = New-Object System.Data.SqlClient.SqlCommandBuilder($adapter)

$adapter.Fill($ds) $binding = New-Object System.Windows.Forms.BindingSource

$binding.DataSource = $ds

$nav.BindingSource = $binding # bind to a gridview

$datagridview1.DataSource = $binding$

datagridview1.DataMember = 'Table'

That is all.  Just add a SQLDataAdapter to the load and and use it to fill the dataset. The adapter uses the SqlCommandBuilder to generate the insert, update and delete commands that allow the data to be updated with one line.
The update is accomplished via this line:

Here is the Sapien PowerShell Studio PSF file for the example: PSF file
Here is the PS1 export of the PSS project: ps1file
The example assumes you have a SQLServer instance and that you have loaded a copy of the NorthWind” sample database. The sample database can be downloaded here: NorthWind.
I highly recommend creating a simple SQLExpress instance and loading this and other sample databases.  They are good learning samples that work well with ADO and Forms Data Binding.
You can change any non-FK field and hit save and the database will be updated.  I have protected the primary key as changing it will create errors.
The assumption for this example is that you are proficient in PowerShell and Windows Forms and that you are mostly interested in how to use data binding.  The example shows a basic form use and does not attempt to explore advanced binding techniques.  With a small amount of research you should be able to discover how to load multiple tables and have them remain updatable.  Skills with SQL relational technology is required for this and some skill with the documentation for the Net Framework System.Data classes.
I will revisit this in more detail after I fully explore the XML dataset use and, hopefully push on to create a very complex dataset with techniques for updating relations and views.