• netDataTable

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.

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  ← AplToDT apl  ⍝ returns a DataTable from an Apl matrix
      apl ← DTtoApl dt   ⍝ returns the Apl matrix from a DataTable
            ShowDT  dt   ⍝ show the DataTable in a Syncfusion DataGrid

To Modify a DataTable

             dt AddRow apl          ⍝ Add a Row at the end of a DataTable
             dt GetCol colNumber    ⍝ Get the values of a single column of a DataTable
                GetColNames dt      ⍝ Get the names of all the columns
             dt GetRow rowNumber    ⍝ Get the values of a single row of a DataTable
 (dt rowNumber) InsertRow apl       ⍝ Insert a new Row in a DataTable
 (dt rowNumber) SetRow apl          ⍝ Update the values of a row in a DataTable

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/

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 in 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.2.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)

CategoryDyalog - CategoryDyalogDotNet - CategoryDyalogDotNetUtilities - CategoryDotNet