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

14 comments:

  1. Another awesome bit of tech information look forward to part 3

    ReplyDelete
  2. Adam, All of the basic information on how to bind a form has been presented. If you think there is something that is unclear or that you do not understand I will try to address it in the next post.

    ReplyDelete
  3. There was one thing...what if you want to join more than one table? I get the error dynamic SQL not supported on updating more than one table. Never seen any code to produce more than the basic Select * FROM. Like how would you get the dataset bound to two different tables and update?

    ReplyDelete
    Replies
    1. That will be handled much later as it is an advanced technique. Simply we need to create a multi-table data set and set any relations then we can update related tables. In SQL a view is not updatable if it does not define a unique key and the update can only apply to a table that is correctly defined in a view. In a dataset we can avoid this issue in many ways.

      This is actually not about data biding directly but is more about how SQL works.

      Delete
    2. $connStr = 'Data Source=omega\SQLEXPRESS;UID=jsmith;Pwd=PassWrd'
      $conn = new-object System.Data.SqlClient.SqlConnection($connStr)
      $cmd = $conn.CreateCommand()
      $cmd.CommandText = @'
      select * from Northwind..Customers
      Select * from Northwind..Orders
      '@
      $adpter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
      $ds = New-Object System.Data.DataSet
      $adpter.Fill($ds)
      $ds.Relations.Add('CustomerOrders', $ds.Tables[0].Columns['CustomerID'], $ds.Tables[1].Columns['CustomerID'])
      $ds.Tables[0].DefaultView.RowFilter = "CustomerId = 'WILMK'"
      $childView = $ds.Tables[0].DefaultView[0].CreateChildView($ds.Relations['CustomerOrders'])

      Delete
  4. Thanks for the sample bit of code. I managed to now load my datagridview with two different tables and have the navbar bound. I tried to add a save button but get:-
    "Update requires a valid InsertCommand when passed DataRow collection with new rows."
    I found some c# detailing this issue. Plus just seen you done a new post so will check that out in detail. Thanks again jv for uploading all this information. Really helpful

    ReplyDelete
    Replies
    1. More coming. I just finished the initial sample for a multi-table datatset.

      Updates have to be hand written or use the command builder.

      Note that updates can only update one table of a set. To update more in a relation takes a pretty good bit of coding skill. The update needs to consider the modified set, deleted set and inserted set.

      Delete
  5. JV your keeping me in suspense. I have spent about 10 hours looking at C# sites that I can only find relating multiple dataset. http://stackoverflow.com/questions/5889102/ado-net-updating-multiple-datatables I feel I am close but not quite there yet. So I am looking forward to seeing how you put this together

    ReplyDelete
    Replies
    1. Did you get the RSS feed updates?

      http://tech-comments.blogspot.com/2017/04/binding-sqlserver-data-to-winforms-with.html

      Delete
    2. Thanks JV yes I did look at this, but my problem is I am trying to update multiple sql tables, so I adapted the bit of code you kindly posted:-
      $global:adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
      $adapter.TableMappings.Add("Table", "POmain")
      $adapter.TableMappings.Add("Table1", "POItem")
      $global:cmdBuilder = New-Object System.Data.SqlClient.SqlCommandBuilder($adapter)

      $global:adapter.Fill($ds)
      $ds.Relations.Add("PurchaseOrder", $ds.Tables['POmain'].Columns['POID'], $ds.Tables['POItem'].Columns['POItemPOID'])
      $childView = $ds.Tables['POmain'].DefaultView.CreateChildView($ds.Relations["PurchaseOrder"])
      So I can load my datagridview with information from two sql tables and have this bound to the NAVBAR, but I still cannot get my head around on how to save changes made to datagrid?

      Delete
    3. Yes thanks JV I did look at the above link you posted. Which has enabled me to update the relevant SQL tables one at a time. I am loading two sql tables having adapted the code you kindly posted:-

      $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
      $adapter.TableMappings.Add("Table", "POmain")
      $adapter.TableMappings.Add("Table1", "POItem")
      $global:cmdBuilder = New-Object System.Data.SqlClient.SqlCommandBuilder($adapter)
      $global:adapter.Fill($ds)
      $ds.Relations.Add("PurchaseOrder", $ds.Tables['POmain'].Columns['POID'], $ds.Tables['POItem'].Columns['POItemPOID'])
      $childView = $ds.Tables['POmain'].DefaultView.CreateChildView($ds.Relations["PurchaseOrder"])
      $bs = ConvertTo-DataTable $childView
      $BindingSource.DataSource = $bs
      $global:bs.add_RowChanged({ $modified = $true; Write-Host 'Row Changed' })
      $datagridview1.DataSource = $BindingSource
      $datagridview1.DataBindings.DefaultDataSourceUpdateMode = 'OnPropertyChanged'

      Delete
    4. Unfortunately this blog is not really good as a forum. TO answer your question quickly - you must create an update statement and add it to the adapter. You must have a primary key on the table you wish to update. You can then update each table but you cannot alter the key and all constraints must be correctly satisfied.

      A multi-bound adapter cannot generate an update statement with the builder. It must be added manually. Alternatively you can attach code to the uprate event of the adapter to preform a custom, complex update.

      I will eventually post examples of using this with SQLServer and discuss various methods for performing the update,

      Delete
  6. Thanks JV these articles of yours have really helped me.

    ReplyDelete
    Replies
    1. I hope to make it fairly simple for scripters to understand and use data. We can also use a standalone SQLServer instance that can be deployed as a DLL assembly and loaded on demand. This has implications for many admin tools as it allows us to use a database without a database being installed. A whole tool plus data can reside on a thumb drive.

      Delete