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.

PowerShell:Understanding Windows Forms Data Binding 2

Example of a data bound form.

# Set up the form objects and bind$customerBindingSource = New-Object System.Windows.Forms.BindingSource$nav = New-Object System.Windows.Forms.BindingNavigator($true)
$ds = New-Object System.Data.DataSet# configure navigator$formXMLDataBindingExample.Controls.Add($nav)
$nav.BackColor = 'LightBlue'$nav.ShowItemToolTips = $true$nav.Dock = 'top'$nav.BindingSource = $customerBindingSource

That is all it takes to set up a form for data binding.  We create a BindingSource, a BindingNavigator and a DataSet.

The BindingNavigator is optional. I added it to the demo forms to test and show how all controls are synchronized to the DataSet. Ignoring the navigator control we just need two things; a dataset and a binding source.

In the linked example I load the data set from an XML source but this can be loaded from any ADO.Net data provider such as SQLServer, Oracle, MS Access, Excel, CSV or dozens of others.
Once the data is loaded and added to the binding source we can then bind the binding source to any data bindable properties of a control.

 # Set up the form objects and bind
$customerBindingSource = New-Object System.Windows.Forms.BindingSource
$ds = New-Object System.Data.DataSet

Create the basic objects then load the dataset.

    $ds.ReadXml($northwindXml)

All we have left to do is to connect the binding source to the control.
In the demo code, I embed the binding into s single line.  Here is the breakdown.

$binding = New-Object System.Windows.Forms.Binding(
    'Text', 
    $customerBindingSource, 
    'ContactName', 
    $true
)
$textboxContactName.DataBindings.Add($binding)

See: System.Windows.Forms.Binding

The ‘Text” argument is the name of the property to bind to.  Next, we use the binding source object and then the name of the field in the dataset.  Last, we can add 4true/$false to choose whether or no to enable the formatting of the control.

Note that this control has a “bindings” collection.  We can add multiple bindings to the control bound between different data sets and properties.  This is a very powerful capability allow s to define forms in a database and auto-generate the forms at runtime.  For our basic forms, we will not explore this but it is nice to know.

Now go back and look at the project form and see how this is done and how we only need to assign each control to a property in the binding source and the controls will all be synchronized to each otter and to the record movements in the dataset.  We can move, filter and search with almost no code.

In the next post I will discuss how this can be used in some practical applications and the implications of data binding for automation of nearly all aspects of a form.

Posts in this article:

PowerShell: Understanding Windows Forms Data Binding
PowerShell:Understanding Windows Forms Data Binding 2

Extra
Binding SQlServer Data to WinForms with PowerShell