Wednesday, April 05, 2017

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.

Monday, April 03, 2017

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

Monday, March 20, 2017

PowerShell: Understanding Windows Forms Data Binding

Technorati Tags:
A simple example of a data bound form

I decided that it was time to post a cleaned up example of how to do data binding with PowerShell Forms.  I pulled up an old demo and set it up to use an XML extract from the NorthWind demo database.  For a demo I though that it would be better if the example could be run on any system so I have converted the form to load its data set from the XML extract.

In this post I will just point out the features and show a couple of images as well as a link to the code.


 
 
The form is intentionally simple and the colors were only changed to highlight the elements.
Features:
  • ‘Select Customer” is a live search box that autocompletes against the dataset.  Code (cost 2 line)
  • The two text box are populated with values from the selected grid row.  (code cosr one line per textbox)
  • Top navigation bar actively moves through the data set with the buttons. (code cost 3 lines.




  • Nav bar allows deleting and editing.  (code cost nothing)
  • Double clicking launches edit form.  (code cost 1 line)






















  • Edit Form doubles as a “new” record editor by adding one code line to the main form.
  • Save and cancel for edits take about 4 lines.
  • Main form detects data changes and prompts for saving.



Scripts:
The scripts are available in a Zip.  A Sapien PowerShell Studio Project is included and a PS1 file that runs without the project.
Three files are included for the forms in Demo-FormXmlDataBinding.zip
  1. Demo-FormXmlDataBinding PSS folder
  2. Standalone folder
    1. Demo-FormXmlDataBinding.ps1 –- the main file
    2. northwind.xml – the data set.

  3. The project folder is included as Demo-FormXmlDataBinding
    1. PowerShell Studio project files


Download:
Demo-FormXmlDataBinding.zip
Small code footprint:
For all of its functionality the main form contains fewer than 50 lines of code
PowerShell active code:
# 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
$formXMLDataBindingExample_Load={
    
    # We are loading only one table.  It is  possible to 
    # load many related tables as a relationship and they
    # will all be synchronized
    $ds.ReadXml($northwindXml)

    # We can set events on the dataset
    $ds.Tables['Customer'].add_RowChanged({ $script:modified = $true;Write-Host 'Row Changed' })
    
    # Assign the DataSet as the DataSource for the BindingSource.
    $customerBindingSource.DataSource = $ds.Tables["Customer"]
    
    # Bind to the controls.
    $textboxContactName.DataBindings.Add((New-Object System.Windows.Forms.Binding('Text', $customerBindingSource, 'ContactName', $true)))
    $textboxPhone.DataBindings.Add((New-Object System.Windows.Forms.Binding('Text', $customerBindingSource, 'Phone', $true)))
    $dgvCustomerTable.DataSource = $customerBindingSource
    $cbCompany.DataSource = $customerBindingSource
    $cbCompany.DisplayMember = 'CompanyName'
}


$dgvCustomerTable_MouseDoubleClick=[System.Windows.Forms.MouseEventHandler]{
    
    # show child form with current row data
    Show-DataChild_psf ([System.Data.DataRowView]$customerBindingSource.Current)
}

$formXMLDataBindingExample_FormClosing=[System.Windows.Forms.FormClosingEventHandler]{
    
    if($modified){
        $answer = [System.Windows.Forms.MessageBox]::Show('Do you want to save your changes and close this form?','Data has changed','YesNoCancel')
        $_.Cancel = switch ($answer){
            Yes {$ds.AcceptChanges()}
            No {$false}
            Cancel {$true}
        }
    }
    
}
Code discussion:
I will post a second and maybe third blog with a more detailed discussion of how this code works and how data binding works in Windows Forms.  Grab the RSS/Atom link and stay tuned for more.

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