netDataTable

(Show (Hide table-of-contents) table-of-contents)

Overview

netDataTable is a Dyalog namespace of methods with the basic functions to create, modify and store such 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

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

      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 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 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 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.

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

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 ← ds 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
 ⍎varName,'←⎕NULL'

 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

How to install netDataTable in your workspace

1. Download netDataTable.v1.4.txt

2. Do a Select all (Ctrl+A) and a copy (Ctrl+C).

3. In your workspace execute )ed ⍟ netDataTable

4. Paste (Ctrl+V) the text into the Dyalog editor
5. 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

CategoryDyalog - CategoryDyalogDotNet - CategoryDyalogDotNetUtilities - CategoryDotNet