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

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"/>

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"/>

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