= netDataTable =

~-<<SeeSaw(section="table-of-contents", show="true", seesaw="false", toshow="<<(Show>> table-of-contents)", tohide="<<(Hide>> table-of-contents)", speed="Slow")>>-~

<<TableOfContents>>

== Overview ==

'''netDataTable''' is a Dyalog namespace of methods with the basic functions to create, modify and store such [[https://msdn.microsoft.com/en-us/library/system.data.datatable(v=vs.110).aspx|DataTable]]. A .Net !DataTable is used to store tabular data in memory. Each column must be of the same type (all characters, all numbers, etc.).  The !DataTable is an important tool to share large quantity of APL data with .Net controls like a Grid, Chart or a !ListView. This is a complement to the tutorial of Dyalog on !DataTable and the `⌶` beams 2010 and 2011. The namespace [[sfExcel]] could be used also to save, modify and retrieve a !DataTable using an Excel file while the namespace [[netCSV]] could be used to save and retrieve a !DataTable using a CSV file.

=== To Create a DataTable ===
The function `AplToDT` will accept an APL matrix and detect if the columns are either numeric or character and will set the type to `Double` or `String` accordingly and return a !DataTable. No other conversion is possible.
{{{
      dt  ← {colNames}AplToDT apl  ⍝ returns a DataTable from an Apl matrix
                                   ⍝ colNames = Optional column names

      apl ← DTtoApl dt   ⍝ returns the Apl matrix from a DataTable

            ShowDT  dt   ⍝ show the DataTable in a Syncfusion DataGrid
            Shape   dt   ⍝ equivalent to  ⍴dt
}}}
=== To Modify a DataTable ===
{{{
             dt GetRow rowNumber    ⍝ Get the values of a single row of a DataTable
 (dt rowNumber) SetRow apl          ⍝ Update the values of a row in a DataTable
 (dt rowNumber) InsertRow apl       ⍝ Insert a new Row in a DataTable
             dt AddRow apl          ⍝ Add a Row at the end of a DataTable
             dt DeleteRow rowNumber ⍝ Delete one row of the DataTable

   (dt colName) AddColumn apl             ⍝ Add a column to the DataTable
             dt GetColumn colNumber       ⍝ Get the values of a single column of a DataTable
             dt DeleteColumn colNumber    ⍝ Delete one column of the DataTable
                GetColNames dt            ⍝ Get the names of all the columns

             dt SortAscending colNumber   ⍝ Sort Ascending a DataTable based on the value of one Column
             dt SortDescending colNumber  ⍝ Sort Descending a DataTable based on the value of one Column
}}}
=== To Save a DataTable ===
{{{
     xmlDoc ← DTtoXml dt            ⍝ Xml representation of a Data Table
         dt ← XmlToDT xmlDoc        ⍝ DataTable from an XmlDoc made with DTtoXml

           dt DTtoXmlFile fileName  ⍝ Saves an Xml representation of the DataTable to a file name
         dt ← XmlFileToDT fileName  ⍝ Retrieves a DataTable from an Xml representation made by DTtoXmlFile

           dt DTtoBinFile fileName  ⍝ Saves a Binary representation of the DataTable to a file name
         dt ← BinFileToDT fileName  ⍝ Retrieves a DataTable from a Binary representation made by DTtoBinFile
}}}
=== Example ===
{{{
      dt ← AplToDT 3 2⍴ 1 'two' 3 'four' 5 'six'

      ShowDT dt   ⍝ Changes made in the grid are saved in the DataTable
}}}
{{attachment:ShowDT.png || width=446}}

=== To Query a DataTable (Contributed by Richard Proctor) ===
`SelectRows` can be used to query a !DataTable in plain English:
{{{
      dt ← 'Apples' 'Bananas' 'Oranges' 'Lemons' AplToDT 3 4⍴⍳12

      GetColNames dt
 Apples  Bananas  Oranges  Lemons

      ShowDT dt
}}}
{{attachment:ShowDT2.png || width=367}}
{{{
      dt SelectRows 'Apples=1 or Bananas=6'
 1 2 3 4  5 6 7 8

      dt SelectRows 'Apples>1 and Bananas<=9'
 5 6 7 8
}}}
Some examples of query strings can be found at [[http://www.csharp-examples.net/dataview-rowfilter/]]. To query for a single item use the function `GetItem`
{{{
      dt GetItem 2 3  ⍝ dt GetItem rowNumber colNumber  (Origin 1)
7
}}}
=== Using the Compute Method ===
The `.Compute` method on a !DataTable is a powerful way to make some calculations on the columns values with optionally a filter.  The full description can be found [[https://msdn.microsoft.com/en-us/library/system.data.datatable.compute%28v=vs.110%29.aspx|here]]
{{{
      dt.Compute 'Sum(Apples)' ''
15
      dt.Compute 'Sum(Bananas)' ''
18
}}}
=== Binding a DataTable ===
To Bind a !DataTable, in APL you set the .!DataContext of the control with the !DataTable and in the XAML you reference the name of the columns used when creating the !DataTable. For example:
{{{
      win.DataContext ← dt
}}}
and in the XAML the `ItemsSource` is set to `{Binding}`:
{{{
      <sfGrid:SfDataGrid ItemsSource="{Binding}">
      <sfGrid:GridNumericColumn HeaderText="Apples" MappingName="Apples"/>
      <sfGrid:GridNumericColumn HeaderText="Bananas" MappingName="Bananas"/>
      <sfGrid:GridNumericColumn HeaderText="Oranges" MappingName="Oranges"/>
      <sfGrid:GridNumericColumn HeaderText="Lemons" MappingName="Lemons"/>
}}}
Another way to bind a !DataTable is to use a [[https://msdn.microsoft.com/en-us/library/system.data.dataview%28v=vs.110%29.aspx|DataView]] (the binding syntax is the same). A !DataView represent a customized view of a !DataTable. Typically you can select some rows and sort some columns in a !DataView.
=== Constructing a DataView ===
{{{
      ⎕USING ← 'System.Data,System.Data.dll'
      dv ← ⎕NEW DataView(dt)
}}}
=== Selecting Rows ===
{{{
      dv.RowFilter ← 'Apples=1 or Bananas=6'
}}}
=== Sorting Columns ===
{{{
      dv.Sort ← 'Apples ASC'               ⍝ Sort the column 'Apples' in ascending order
      dv.Sort ← 'Apples ASC, Bananas DESC' ⍝ Sort the column 'Apples' in ascending order
                                           ⍝ with the column 'Bananas' in descending order
}}}
=== Binding a DataView ===
{{{
      win.DataContext ← dv
}}}
== DataSet ==
A [[https://msdn.microsoft.com/en-us/library/system.data.dataset(v=vs.110).aspx|DataSet]] is a collection of !DataTable objects. It is constructed from a nameless namespace, this is permitting to specify the type of the data (Boolean and !DateTime) by adding a suffix to the name of the property.
|| '''Type''' || '''Name''' || '''APL ↔ .Net''' ||
|| Boolean || _b || 1 0 ¯1 ↔ 'True' 'False' 'Indeterminate' ||
|| !DateTime || _d1 || numeric [[https://msdn.microsoft.com/en-us/library/system.datetime.tooadate%28v=vs.110%29.aspx|OADate|target='_blank']] ↔ !DateTime ||
|| !DateTime || _d2 || numeric ⎕TS ↔ !DateTime ||
|| !DateTime || _d3 || character representation of a Date ↔ !DateTime ||

=== To Create a DataSet ===
{{{
      ds ← AplToDS ns  ⍝ returns a DataSet from a nameless namespace
      ns ← DStoApl ds  ⍝ returns a nameless namespace from a DataSet
           ShowDS  ds  ⍝ show the DataSet in a Syncfusion DataGrid
}}}

=== To Save a DataSet ===
{{{
        ds DStoXmlFile fileName  ⍝ Saves an Xml representation of the DataSet to a file name
      ds ← XmlFileToDS fileName  ⍝ Retrieves a DataSet from an Xml representation made by DStoXmlFile
}}}
=== Example ===
For the following nameless namespace:
{{{
 ns←⎕NS''
 ns.Books←⎕NS''
 ns.Books.Author←'author1' 'author2' 'author3'
 ns.Books.Title←'title1' 'title2' 'title3'
 ns.Books.Price←100 200 300
 ns.Books.IsAvailable_b←1 0 ¯1        ⍝ _b  suffix is Boolean conversion
 ns.Books.Date_d1←42000 43000 44000   ⍝ _d1 suffix is OADate conversion

 ns.Inventory←⎕NS''
 ns.Inventory.Title←'title1' 'title2' 'title3'
 ns.Inventory.Quantity←100 200 300
 ns.Inventory.IsShipped_b←1 0 ¯1
 ns.Inventory.DatePurchased_d2←(2014 12 1)(2013 8 4)(2012 6 2)  ⍝ _d2 suffix is 3↑⎕TS or 6↑⎕TS conversion
 ns.Inventory.DateShipped_d3←'2014-12-5' '2013/8/9' '2012 6 5'  ⍝ _d3 suffix is for character representation of the date
}}}
The !DataSet is obtained by doing:
{{{
      ds←AplToDS ns

      ds.Tables.Count         ⍝ Quantity of DataTable(s) in the DataSet
2
      ds.Tables[0].TableName  ⍝ Name of the first DataTable
Books
      ds.Tables[1].TableName  ⍝ Name of the second DataTable
Inventory

      ds.Tables[⊂'Books']     ⍝ To Get the DataTable named 'Books'
Books
      ds.Tables[⊂'Inventory'] ⍝ To Get the DataTable named 'Inventory'
Inventory

      ShowDS ds   ⍝ Changes made in the grid are saved in the DataSet
                  ⍝ Each Tab is a DataTable included in the DataSet
}}}
{{attachment:ShowDS1.png || width=640}}{{attachment:ShowDS2.png || width=640}}
=== Binding a DataSet ===
To Bind a !DataSet is similar as binding a !DataTable. The difference reside in `ItemsSource` where you declare which !DataTable to use for the binding. For that you use the keyword 'Tables' and the name of the !DataTable surrounded by square brackets without any apostrophe (').
{{{
      win.DataContext ← ds
}}}
and in the XAML for using the !DataTable 'Books' of the !DataSet included in the .!DataContext `ItemsSource` would be set to `{Binding Tables[Books]}`:
{{{
      <sfGrid:SfDataGrid ItemsSource="{Binding Tables[Books]}">
      <sfGrid:GridTextColumn HeaderText="Author" MappingName="Author"/>
      <sfGrid:GridTextColumn HeaderText="Title" MappingName="Title"/>
      <sfGrid:GridNumericColumn HeaderText="Price" MappingName="Price"/>
}}}
== Binding Across Threads ==
For a multi-thread application it is not permitted to do `win.DataContext ← dt` across threads. The two solutions are either use a Dispatcher or bind the name of a variable containing the `DataTable`. The simpler is the binding using the following function:
{{{
 object BindVarNameToObjDataContext varName;binding;⎕USING
⍝ Binds a fully qualified existing apl variable name to the DataContext of a .Net objet.
⍝ The apl variable can contain a .Net object
⍝
⍝ varName = fully qualified existing apl variable name
⍝ object  = .Net object

 ⎕USING←'System.Windows.Data,WPF/PresentationFramework.dll' 'System.Windows,WPF/PresentationFramework.dll'

⍝ Prepare for binding:
 ⎕EX varName       ⍝ To reset a previous binding with that variable
 ⍎varName,'←'''''  ⍝ The name of the variable must exist before doing the binding

 binding        ← ⎕NEW Binding(⊂,{(-⊥⍨⍵≠'.')↑⍵}varName)  ⍝ varName is without the path here
 binding.Source ← 2015⌶varName                           ⍝ varName is fully qualified with path here
 binding.Mode   ← BindingMode.OneWay                     ⍝ Setup as One Way Binding
 binding.UpdateSourceTrigger ← UpdateSourceTrigger.PropertyChanged

⍝ Setting-up the Binding to the DataContext Property:
 {}object.SetBinding(FrameworkElement.DataContextProperty binding)
}}}
That way you can do the following:
{{{
      ⍝ You bind the variable named 'dt_bind' to the DataContext of 'win'
      ⍝ on the thread that is creating the .Net object
       win BindVarNameToObjDataContext 'dt_bind'

      ⍝ On the callback that is on another thread the following is done
      ⍝ to bind the DataTable 'dt' to the 'win' object
       dt_bind ← dt
}}}
Binding with 2015⌶ is asynchronous and if you need to do some action after that binding in the same function it may not work. In that case a Dispatcher is more appropriate since it is synchronous. Here is an example of the function to use:
{{{
 obj DispatcherInvoke action;dispatchName;or;⎕USING
⍝ Function to write synchronously to UI thread from another thread.
⍝ With DispatcherInvoke, the calling thread wait for the operation to finish.
⍝
⍝ The function 'RunBackgroundDispatcherInvoke' will be created in the calling namespace.

 ⎕USING ← 'System,System.dll' 'System.Windows.Threading,WPF/WindowsBase.dll'

 dispatchName ← 'RunBackgroundDispatcherInvoke_',(⍕⎕TS)~' '

 or ← (⎕IO⊃⎕RSI).⎕OR(⎕IO⊃⎕RSI).⎕FX dispatchName action('⎕EX ''',dispatchName,'''')

 obj.Dispatcher.Invoke(DispatcherPriority.Normal(⎕NEW Action or))
}}}
and the usage will be in our case as follow:
{{{
      win DispatcherInvoke 'win.DataContext ← dt'
}}}
In case that you want to write asynchronously and not use data binding the function `DispatcherBeginInvoke` can be used with the same syntax:
{{{
 obj DispatcherBeginInvoke action;dispatchName;or;⎕USING
⍝ Function to write asynchronously to UI thread from another thread.
⍝ With DispatcherBeginInvoke, the calling thread does not wait for the operation to finish.
⍝
⍝ The function 'RunBackgroundDispatcherBeginInvoke' will be created in the calling namespace.

 ⎕USING←'System,System.dll' 'System.Windows.Threading,WPF/WindowsBase.dll'

 dispatchName←'RunBackgroundDispatcherBeginInvoke_',(⍕⎕TS)~' '

 or←(⎕IO⊃⎕RSI).⎕OR(⎕IO⊃⎕RSI).⎕FX dispatchName action('⎕EX ''',dispatchName,'''')

 {}obj.Dispatcher.BeginInvoke(DispatcherPriority.Normal(⎕NEW Action or))
}}}
== How to install netDataTable in your workspace ==

 1. Download [[attachment:netDataTable.v1.4.txt]]
 1. Do a Select all (Ctrl+A) and a copy (Ctrl+C).
 1. In your workspace execute `)ed ⍟ netDataTable`
 1. Paste (Ctrl+V) the text into the Dyalog editor
 1. Press Escape and ')save' your workspace

Optionally to de-script the namespace you can do:
{{{
'netDataTable' ⎕SE.SALTUtils.CopyNs netDataTable
}}}

== Version Information ==
{{{
February 2015   - Initial version (1.0)

April 2015      - DataSet function added (1.1)

September 2015  - SelectRows function added (1.2)

February 2016   - Methods added (1.3):
                - DeleteRow, DeleteColumn, GetItem, SortAscending, SortDescending
                - Method renamed: GetCol renamed GetColumn

February 2016   - Methods added (1.4): Shape, AddColumn
}}}
||Original author: ||Pierre Gilbert ||
||Responsible: ||PierreGilbert ||
||Email: || <<MailTo(apgil AT SPAMFREE videotron DOT ca)>> ||

----
CategoryDyalog - CategoryDyalogDotNet - CategoryDyalogDotNetUtilities - CategoryDotNet