• 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 functions AplToDT will detect if the columns of the Apl matrix are either numeric or character and will set the type to Double or String accordingly. 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
                GetColumnNames 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

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

How to install netDataTable in your workspace

1. Download netDataTable.v1.1.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

Version Information

February 2015   - Initial version (1.0)

April 2015      - DataSet functions added (1.1)

CategoryDyalog - CategoryDyalogDotNet - CategoryDyalogDotNetUtilities - CategoryDotNet