1 :Namespace netDataTable
   2 
   3 ⍝ Methods related to .Net DataTable and DataSet.
   4 
   5 ⍝ February 2015   - Initial version (1.0)
   6 ⍝
   7 ⍝ April 2015      - DataSet function added (1.1)
   8 ⍝
   9 ⍝ September 2015  - SelectRows function added (1.2)
  10 ⍝
  11 ⍝ February 2016   - Methods added (1.3):
  12 ⍝                 - DeleteRow, DeleteColumn, GetItem, SortAscending, SortDescending
  13 ⍝                 - Method renamed: GetCol renamed GetColumn
  14 ⍝
  15 ⍝ February 2016   - Methods added (1.4): Shape, AddColumn
  16 
  17 
  18     (⎕IO ⎕ML ⎕WX)←1 3 3
  19 
  20     ∇ dt AddRow apl;newRow
  21      ⍝ Add a Row At the End of An Existing DataTable
  22      ⍝ dt   = DataTable
  23      ⍝ apl  = Data to Add as Vector or Matrix of proper dimension.
  24      
  25      ⍝ If a Vector, set as a one row matrix:
  26       :If 1=⍴⍴apl
  27           apl←(1,⍴apl)⍴apl
  28       :EndIf
  29      
  30      ⍝ Add the data:
  31       2010⌶dt apl
  32      
  33      ⍝ Equivalent way to add only one row:
  34 ⍝      newRow←dt.NewRow
  35 ⍝      newRow.ItemArray←,apl
  36 ⍝      dt.Rows.Add(newRow)
  37 ⍝      or
  38 ⍝      dt.Rows.Add(⊂apl)
  39     ∇
  40 
  41     ∇ dt←la AddColumn apl;col;colName;colType;IsChar;IsNum;⎕USING
  42     ⍝ Add a column to an existing DataTable
  43     ⍝
  44     ⍝ apl = apl data for the new column
  45     ⍝ colName = Name of the new column
  46     ⍝ dt      = DataTable to add the column
  47      
  48       (dt colName)←la  ⍝ parse the left argument
  49      
  50       ⎕USING←'System' 'System.Data,System.Data.dll'
  51      
  52       IsChar←{' '=↑1↑0⍴⍵}
  53       IsNum←{0=↑1↑0⍴⍵}
  54      
  55     ⍝ Determine the Type of the new column:
  56       :If ∧/IsChar¨apl
  57         ⍝ Default Type for characters
  58           colType←String
  59      
  60       :ElseIf ∧/IsNum¨apl
  61         ⍝ Default Type for numbers
  62           colType←Double
  63      
  64       :Else
  65         ⍝ Mixed Type in the same column
  66           ⎕←'Don''t know what to do with new column'
  67           apl←⍕¨apl
  68           colType←String
  69      
  70       :EndIf
  71      
  72     ⍝ Add the new column:
  73       col←⎕NEW DataColumn(colName colType)
  74       dt.Columns.Add(col)
  75      
  76     ⍝ Populate the new column:
  77       dt{⍺.Rows[⍵-1].Item[⊂colName]←⍵}¨apl
  78     ∇
  79 
  80     ∇ ds←{dataSetName}AplToDS nss;array;colName;colNames;colType;dt;index;IsChar;IsNum;ns;nsName;parseDate;ptr;test;⎕USING
  81     ⍝ Function to convert namespaces within a namespace to a .Net DataSet
  82     ⍝
  83     ⍝ nss = namespaces within a nameless namespace
  84     ⍝ ds  = .Net DataSet that contains a collection of DataTable(s)
  85     ⍝
  86     ⍝ The properties name of the namespace can finish with _? to apply a two way converter:
  87     ⍝ _b  : Boolean converter 1, 0 ¯1 is True, False, Indeterminate
  88     ⍝ _d1 : OADate
  89     ⍝ _d2 : 3↑⎕TS or 6↑⎕TS
  90     ⍝ _d3 : Character representation of a date
  91     ⍝
  92     ⍝ Typical usage:
  93     ⍝ ns←⎕NS''
  94     ⍝ ns.Books←⎕NS''
  95     ⍝ ns.Books.Author←'author1' 'author2' 'author3'
  96     ⍝ ns.Books.Title←'title1' 'title2' 'title3'
  97     ⍝ ns.Books.Price←100 200 300
  98     ⍝ ns.Books.IsAvailable_b←1 0 ¯1                        ⍝ Boolean converter
  99     ⍝ ns.Books.Date_d1←42000 43000 44000                   ⍝ OADate converter
 100     ⍝ ns.Books.Date_d2←(2014 12 1)(2013 8 4)(2012 6 2)     ⍝ 3↑⎕TS converter
 101     ⍝ ns.Books.Date_d3←'2014-12-5' '2013/8/9' '2012 6 5'   ⍝ Characters of date converter
 102     ⍝
 103     ⍝ ds←AplToDS ns
 104     ⍝ ShowDT ds.Tables[⊂'Books']  ⍝ Show and modify the DataTable
 105     ⍝ ns←DStoApl ds               ⍝ Convert back to Apl the DataSet
 106     ⍝
 107     ⍝ or if you are binding use the following statement:
 108     ⍝ ItemsSource="{Binding Tables[Books]}"
 109     ⍝ and set the DataContext to the DataSet created with AplToDS
 110      
 111       :If 9≠⎕NC'nss'
 112       :OrIf '[Namespace]'≢¯11↑⍕nss
 113           ⎕←'AplToDS Error: Argument must be a nameless Namespace'
 114           →0
 115       :EndIf
 116      
 117       IsChar←{' '=↑1↑0⍴⍵}
 118       IsNum←{0=↑1↑0⍴⍵}
 119      
 120       :If 0=⎕NC'dataSetName'
 121           dataSetName←'DataSet'
 122      
 123       :ElseIf ~IsChar dataSetName
 124           ⎕←'AplToDS Error: dataSetName must be Characters'
 125           →0
 126       :End
 127      
 128       ⎕USING←'System' 'System.Data,System.Data.dll' 'Dyalog' 'System.Windows.Controls,WPF/PresentationFramework.dll'
 129       ds←⎕NEW DataSet(⊂dataSetName)     ⍝ Default value in case of error
 130      
 131     ⍝ TryParse will return 1 if successful with the parsed value in 'ptr'
 132       ptr←⎕NEW ByRef(⎕NEW DateTime ⎕TS)                 ⍝ Pointer to receive the conversion of 'parseData' if successfull
 133       parseDate←{(DateTime.TryParse(⍵ ptr)):ptr.Value   ⍝ Parse characters to a .Net DateTime Object
 134           ⎕NULL}                                        ⍝ Default value if characters are not a valid date
 135      
 136       :Trap 0
 137         ⍝ Convert each simple namespaces into a DataTable and Add it to the DataSet:
 138           :For nsName :In nss.⎕NL-9
 139               ns←nss.⍎nsName            ⍝ Current namespace of variables
 140               colNames←(ns.⎕NL-2)       ⍝ Get the variable names (colNames) of the namespace
 141               array←⍉⊃ns.⍎¨colNames     ⍝ Get all the values of the colNames as a matrix
 142      
 143             ⍝ Convert the namespace into a DataTable
 144               dt←⎕NEW DataTable(⊂nsName)
 145      
 146             ⍝ Determine the Type of each column
 147               colType←''
 148      
 149               :For index :In ⍳2⌷⍴array
 150      
 151                   colName←index⊃colNames
 152      
 153                   :If '_b'≡¯2↑colName
 154                     ⍝ 1 and 0 to Boolean. ¯1 is Inderminate state (System.DBNull)
 155                       :If ∧/IsNum¨array[;index]
 156                           colType,←Boolean
 157                           test←array[;index]=¯1                                    ⍝ ¯1 = Indeterminate state
 158                           array[{⍵/⍳⍴⍵}test;index]←DBNull.Value                    ⍝ Change the ¯1 for DBNull.Value
 159                           array[{⍵/⍳⍴⍵}~test;index]←1⌊0⌈array[{⍵/⍳⍴⍵}~test;index]  ⍝ Coerce to 1 or 0 all the other values
 160                       :Else
 161                           colType,←String
 162                           array[;index]←⍕¨array[;index]
 163                           ⎕←'AplToDS Error: Don''t know what to do with: ',nsName,'.',colName
 164                       :End
 165      
 166                   :ElseIf '_d1'≡¯3↑colName
 167                     ⍝ Numeric OADates to DateTime
 168                       :If ∧/IsNum¨array[;index]
 169                           colType,←DateTime
 170                           array[;index]←DateTime.FromOADate¨array[;index]
 171                       :Else
 172                           colType,←String
 173                           array[;index]←⍕¨array[;index]
 174                           ⎕←'AplToDS Error: Don''t know what to do with: ',nsName,'.',colName
 175                       :End
 176      
 177                   :ElseIf '_d2'≡¯3↑colName
 178                     ⍝ 3↑⎕TS or 6↑⎕TS to DateTime
 179                       colType,←DateTime
 180                       array[;index]←parseDate¨⍕¨array[;index]   ⍝ parseDate cannot bug
 181      
 182                   :ElseIf '_d3'≡¯3↑colName
 183                     ⍝ Character representation of a date
 184                       colType,←DateTime
 185                       array[;index]←parseDate¨⍕¨array[;index]   ⍝ parseDate cannot bug
 186      
 187                   :ElseIf ∧/IsNum¨array[;index]
 188                     ⍝ Only numbers with no converters
 189                       colType,←Double
 190      
 191                   :ElseIf ∧/IsChar¨array[;index]
 192                     ⍝ Only characters with no converters
 193                       colType,←String
 194      
 195                   :Else
 196                       ⎕←'AplToDS Error: Don''t know what to do with: ',nsName,'.',colName
 197                       colType,←String
 198                       array[;index]←⍕¨array[;index]
 199      
 200                   :EndIf
 201               :EndFor
 202      
 203             ⍝ Set the Column's Types and Names of the DataTable:
 204               {}colNames{dt.Columns.Add ⍺ ⍵}¨colType
 205      
 206             ⍝ Fill the DataTable
 207               2010⌶dt array
 208      
 209             ⍝ Add the DataTable to the DataSet
 210               ds.Tables.Add(dt)
 211      
 212           :EndFor
 213       :EndTrap
 214     ∇
 215 
 216     ∇ dt←{colNames}AplToDT aplArray;colType;index;IsChar;IsNum;tableName;⎕USING
 217      ⍝ Create a DataTable from an Apl Array.
 218      ⍝ EACH COLUMN MUST BE OF THE SAME TYPE.
 219      ⍝ For dates, convert ⎕TS to an OADate before making the DataTable.
 220      ⍝ apl      = An APL array of Numbers and Characters.
 221      ⍝ colNames = Optional Column names for the DataTable. Works best with 2 characters per name.
 222      ⍝ dt       = Resulting DataTable
 223      
 224      ⍝ If a Vector, set as a one row matrix:
 225       :If 1=⍴⍴aplArray
 226           aplArray←(1,⍴aplArray)⍴aplArray
 227       :EndIf
 228      
 229      ⍝ Check if aplArray is a 2 dimensional array:
 230       :If 2≠⍴⍴aplArray
 231           ⎕←'AplToDT Error: Argument must be of rank equal or smaller than 2'
 232           →0
 233       :End
 234      
 235      ⍝ Check if columnNames is properly formed:
 236       :If 0=⎕NC'colNames'            ⍝ There is no columnNames
 237       :OrIf (⍴colNames)≠1↓⍴aplArray  ⍝ Wrong Shape
 238       :OrIf 1∊0=1↑¨0⍴¨colNames       ⍝ Not characters
 239       :OrIf 0∊≡¨colNames             ⍝ Wrong Depth
 240      
 241         ⍝ 'colNames' is not supplied or invalid:
 242         ⍝ Generate the Column Names as 'C1' 'C2', etc.:
 243           colNames←'C',¨⍕¨⍳1↓⍴aplArray
 244       :End
 245      
 246       tableName←'Data'  ⍝ Default TableName
 247       IsChar←{' '=↑1↑0⍴⍵}
 248       IsNum←{0=↑1↑0⍴⍵}
 249      
 250     ⍝ Default value in case of error:
 251       ⎕USING←'System' 'System.Data,System.Data.dll'
 252       dt←⎕NEW DataTable(⊂tableName)
 253      
 254       :Trap 0
 255          ⍝ Determine the Type of each column:
 256           colType←''
 257           :For index :In ⍳2⌷⍴aplArray
 258               :If ∧/IsChar¨aplArray[;index]
 259                 ⍝ Default Type for characters
 260                   colType,←String
 261      
 262               :ElseIf ∧/IsNum¨aplArray[;index]
 263                 ⍝ Default Type for numbers
 264                   colType,←Double
 265      
 266               :Else
 267                 ⍝ Mixed Type in the same column
 268                   ⎕←'Don''t know what to do with column no: ',⍕index
 269                   aplArray[;index]←⍕¨aplArray[;index]
 270                   colType,←String
 271      
 272               :EndIf
 273           :EndFor
 274      
 275          ⍝ Set the Column's Types and Names of the DataTable:
 276           {}colNames{dt.Columns.Add ⍺ ⍵}¨colType
 277      
 278          ⍝ Fill the DataTable:
 279           2010⌶dt aplArray
 280      
 281       :EndTrap
 282     ∇
 283 
 284     ∇ dt←BinFileToDT fileName;binaryFormatter;fileStream;⎕USING
 285      ⍝ Retrieves a DataTable from a Binary representation made by DTtoBinFile
 286      ⍝ fileName = fully qualified file name
 287      ⍝ dt       = DataTable
 288      
 289      ⍝ Read the fileName
 290       ⎕USING←',mscorlib.dll'
 291       fileStream←⎕NEW System.IO.FileStream(fileName System.IO.FileMode.Open)
 292      
 293      ⍝ Get a BinaryFormatter and Deserialize the file stream
 294       binaryFormatter←⎕NEW System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
 295       dt←binaryFormatter.Deserialize fileStream
 296       fileStream.Close
 297     ∇
 298 
 299     ∇ dt DeleteColumn colNumber
 300     ⍝ Delete one column of the DataTable
 301     ⍝ colNumber = Index of the column (Origin 1)
 302     ⍝ dt        = Data Table
 303      
 304       dt.Columns.RemoveAt(colNumber-1)
 305     ∇
 306 
 307     ∇ dt DeleteRow rowNumber
 308     ⍝ Delete one row of the DataTable
 309     ⍝ rowNumber = Index of the row (Origin 1)
 310     ⍝ dt        = Data Table
 311      
 312       dt.Rows[rowNumber-1].Delete
 313     ∇
 314 
 315     ∇ apl←DStoApl ds;array;colDataType;colDataTypes;colName;colNames;dateCvt;dt;index;ns;test;⎕USING
 316     ⍝ Function to convert a .Net DataSet made with AplToDS to an APL namespace
 317     ⍝
 318     ⍝ ds  = .Net DataSet
 319     ⍝ apl = Namespaces within a nameless namespace
 320      
 321       :If 9≠⎕NC'ds'
 322       :OrIf 'System.Data.DataSet'≢⍕ds
 323           ⎕←'DStoApl Error: Argument must be a DataSet'
 324           →0
 325       :EndIf
 326      
 327       ⎕USING←'System'
 328       apl←⎕NS''    ⍝ Default value in case of error
 329      
 330     ⍝ Convert each DataTable to a named namespace
 331       :For dt :In ⌷ds.Tables
 332      
 333           ns←⎕NS''                             ⍝ Empty nameless namespace
 334           colNames←(⌷dt.Columns).ColumnName    ⍝ Column names of the DataTable
 335           colDataTypes←(⌷dt.Columns).DataType  ⍝ Type of the column
 336           array←2011⌶dt                        ⍝ Convert the DataTable to an APL array
 337      
 338           :For index :In ⍳2⌷⍴array
 339               colName←index⊃colNames
 340               colDataType←⍕index⊃colDataTypes
 341      
 342               :If '_d1'≡¯3↑colName
 343                 ⍝ Return the .Net DateTime object as numeric OADates
 344                   :If 'System.DateTime'≡colDataType
 345                       test←array[;index]≠DBNull.Value
 346                       array[{⍵/⍳⍴⍵}~test;index]←0  ⍝ Default value if DBNull is returned
 347                       array[{⍵/⍳⍴⍵}test;index]←(array[{⍵/⍳⍴⍵}test;index]).ToOADate
 348                   :Else
 349                       ⎕←'DStoApl Error: Don''t know what to do with: ',(dt.TableName),'.',colName
 350                   :End
 351      
 352               :ElseIf '_d2'≡¯3↑colName
 353                 ⍝ Return the .Net DateTime object as numeric 3↑⎕TS
 354                   :If 'System.DateTime'≡colDataType
 355                       test←array[;index]≠DBNull.Value
 356                       array[{⍵/⍳⍴⍵}~test;index]←⊂1900 1 1  ⍝ Default value if DBNull is returned
 357                       array[{⍵/⍳⍴⍵}test;index]←(array[{⍵/⍳⍴⍵}test;index]).(Year Month Day)
 358                   :Else
 359                       ⎕←'DStoApl Error: Don''t know what to do with: ',(dt.TableName),'.',colName
 360                   :End
 361      
 362               :ElseIf '_d3'≡¯3↑colName
 363                 ⍝ Return the .Net DateTime object as a character representation of the date
 364                   :If 'System.DateTime'≡colDataType
 365                       test←array[;index]≠DBNull.Value
 366                       array[{⍵/⍳⍴⍵}~test;index]←⊂'1/1/1900'  ⍝ Default value if DBNull is returned
 367                       dateCvt←{⎕USING←'System.Globalization' ⋄ ⍵.ToString((,'G')CultureInfo.InvariantCulture)}
 368                       array[{⍵/⍳⍴⍵}test;index]←dateCvt¨array[{⍵/⍳⍴⍵}test;index]
 369                   :Else
 370                       ⎕←'DStoApl Error: Don''t know what to do with: ',(dt.TableName),'.',colName
 371                   :End
 372      
 373               :ElseIf '_b'≡¯2↑colName
 374                   :If 'System.Boolean'≡colDataType
 375                     ⍝ If equal to DBNull the Boolean is Inderminate and it is changed to ¯1
 376                       test←array[;index]=DBNull.Value
 377                       array[{⍵/⍳⍴⍵}test;index]←¯1
 378                   :End
 379               :End
 380      
 381           :EndFor
 382      
 383           colNames{ns.⍎⍺,'←⍵'}¨↓⍉array       ⍝ Assign each columns to the colNames in the NameSpace
 384      
 385           ⍎'apl.',(dt.TableName),'←ns'       ⍝ Add the new named namespace to the nameless namespace
 386       :EndFor
 387     ∇
 388 
 389     ∇ apl←DTtoApl dt
 390     ⍝ Convert a .Net DataTable to APL
 391     ⍝ dt  = DataTable
 392     ⍝ apl = apl representation of the DataTable
 393      
 394       :If 9≠⎕NC'dt'
 395       :OrIf 'System.Data.DataTable'≢dt.GetType.ToString
 396           ⎕←'DTtoApl Error: The argument is not a DataTable Object !'
 397       :EndIf
 398      
 399       apl←2011⌶dt
 400     ∇
 401 
 402     ∇ r←dt DTtoBinFile fileName;binaryFormatter;memStream;⎕USING
 403     ⍝ Saves a Binary representation of the DataTable to a file name
 404     ⍝ fileName = fully qualified file name
 405     ⍝ dt       = DataTable
 406      
 407     ⍝ Get a Binary Formatter
 408       ⎕USING←',mscorlib.dll'
 409       binaryFormatter←⎕NEW System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
 410      
 411     ⍝ Serialize the DataTable to a MemoryStream
 412       memStream←⎕NEW System.IO.MemoryStream
 413       binaryFormatter.Serialize memStream dt
 414      
 415     ⍝ Write the MemoryStream to fileName and dispose of the MemoryStream
 416       System.IO.File.WriteAllBytes((⊂,fileName),(⊂memStream.ToArray))
 417       memStream.Close ⋄ memStream.Dispose ⋄ memStream←⎕NULL
 418      
 419      ⍝ Write to fileName and close the FileStream (alternative way to do the same thing)
 420 ⍝      fileStream←⎕NEW System.IO.FileStream(fileName System.IO.FileMode.Create)
 421 ⍝      binaryFormatter.Serialize fileStream dt
 422 ⍝      fileStream.Close
 423     ∇
 424 
 425     ∇ xmlDoc←DTtoXml dt;ms;⎕USING
 426     ⍝ Generates the Xml representation of a Data Table.
 427     ⍝ dt     = DataTable
 428     ⍝ xmlDoc = XmlDocument
 429      
 430     ⍝ Get an Empty Memory Stream:
 431       ⎕USING←'System.IO,mscorlib.dll'
 432       ms←⎕NEW MemoryStream
 433      
 434     ⍝ Set the XML Mapping as Attribute for all the columns:
 435       (⌷dt.Columns).ColumnMapping←dt.Columns[0].ColumnMapping.Attribute
 436      
 437     ⍝ Write to the Memory Stream the Xml Representation of the Data Table with the Xls Schema:
 438       ⎕USING←'System.Data,System.Data.dll' 'System.Xml,System.Xml.dll'
 439       dt.WriteXml(ms XmlWriteMode.WriteSchema) ⍝ XmlWriteMode.IgnoreSchema also available
 440      
 441     ⍝ Set the position of the memory stream at the beginning
 442       ⎕USING←'System,mscorlib.dll'
 443       ms.Position←Convert.ToInt64 0
 444      
 445     ⍝ Write the Memory Stream to an XmlDocument
 446       ⎕USING←'System.Xml,System.Xml.dll'
 447       xmlDoc←⎕NEW XmlDocument
 448       xmlDoc.Load ms
 449       xmlDoc.DocumentElement.SetAttribute('xmlns' '')
 450      
 451     ⍝ Clean-up
 452       ms.Close ⋄ ms.Dispose ⋄ ms←⎕NULL
 453     ∇
 454 
 455     ∇ ds DStoXmlFile fileName;⎕USING
 456     ⍝ Saves an Xml representation of the DataSet to a file name
 457     ⍝ fileName = fully qualified file name
 458     ⍝ ds       = DataSet
 459      
 460     ⍝ Write to a Xml file with the Schema
 461       ⎕USING←'System.Data,System.Data.dll'
 462       ds.WriteXml(fileName XmlWriteMode.WriteSchema)
 463     ∇
 464 
 465     ∇ dt DTtoXmlFile fileName;⎕USING
 466     ⍝ Saves an Xml representation of the DataTable to a file name
 467     ⍝ fileName = fully qualified file name
 468     ⍝ dt       = DataTable
 469      
 470     ⍝ Set the XML Mapping as Attribute for all the column:
 471       (⌷dt.Columns).ColumnMapping←dt.Columns[0].ColumnMapping.Attribute
 472      
 473     ⍝ Write to a Xml file with the Schema
 474       ⎕USING←'System.Data,System.Data.dll'
 475       dt.WriteXml(fileName XmlWriteMode.WriteSchema)
 476     ∇
 477 
 478     ∇ apl←dt GetColumn colNumber;colName;dv;string;⎕USING
 479      ⍝ Get the value of a single column of an existing DataTable
 480      ⍝ dt        = Data Table
 481      ⍝ colNumber = Index of the column (Origin 1)
 482      ⍝ apl       = Apl data
 483      
 484      ⍝ Get the name of the column
 485       colName←dt.Columns[colNumber-1].ColumnName
 486      
 487      ⍝ Create a .net string vector (string[]) with that name
 488       ⎕USING←'' 'System.Data,System.Data.dll'
 489       string←System.Array.CreateInstance(System.Type.GetType⊂'System.String')1
 490       string.SetValue(colName 0)
 491      
 492 ⍝    ⍝ Make a DataView and filter the method .ToTable
 493 ⍝     dv←⎕NEW DataView dt
 494 ⍝     apl←,2011⌶dv.ToTable(0 string)
 495      
 496      ⍝ ↓↓↓ To get a column without using a DataView
 497       apl←,2011⌶dt.DefaultView.ToTable(0 string) ⍝ Use (1 string) to remove the duplicates values
 498     ∇
 499 
 500     ∇ colNames←GetColNames dt
 501      ⍝ Get the names of all the columns
 502      ⍝ dt       = Data Table
 503      ⍝ colNames = Column Names of all the columns
 504      
 505       colNames←(⌷dt.Columns).ColumnName
 506     ∇
 507 
 508     ∇ apl←dt GetItem(rowNumber colNumber)
 509     ⍝ Select one Item at rowNumber and colNumber
 510     ⍝ rowNumber = Index of the row (Origin 1)
 511     ⍝ colNumber = Index of the column (Origin 1)
 512     ⍝ dt        = Data Table
 513      
 514       apl←⊃dt.Rows[rowNumber-1].Item[colNumber-1]
 515     ∇
 516 
 517     ∇ apl←dt GetRow rowNumber
 518      ⍝ Get the value of a row of an existing DataTable
 519      ⍝ rowNumber = Index of the row (Origin 1)
 520      ⍝ dt        = Data Table
 521      ⍝ apl       = Apl data
 522      
 523       apl←dt.Rows[rowNumber-1].ItemArray
 524     ∇
 525 
 526     ∇ la InsertRow apl;dt;newRow;rowNumber
 527      ⍝ Insert a new Row in a DataTable
 528      ⍝ apl       = New Data to Insert
 529      ⍝ dt        = DataTable
 530      ⍝ rowNumber = Row Number of DataTable after wich the data will
 531      ⍝           = be inserted (Origin 1)
 532      
 533      ⍝ Split the Left Argument:
 534       (dt rowNumber)←la
 535      
 536       newRow←dt.NewRow
 537       newRow.ItemArray←apl
 538       dt.Rows.InsertAt(newRow,rowNumber)
 539     ∇
 540 
 541     ∇ r←Shape dt
 542     ⍝ Equivalent to ⍴dt
 543      
 544       r←dt.(Rows Columns).Count
 545     ∇
 546 
 547     ∇ apl←dt SelectRows query;rows
 548     ⍝ Gets an array of all DataRow objects that match the filter criteria.
 549     ⍝ Contributed by Richard Proctor
 550      
 551       rows←dt.Select(⊂query)
 552       apl←rows.ItemArray
 553     ∇
 554 
 555     ∇ la SetRow apl;dt;rowNumber
 556      ⍝ Update a row of a DataTable with new values.
 557      ⍝ apl       = New Data to Replace the Existing Ones
 558      ⍝ dt        = DataTable
 559      ⍝ rowNumber = Row Number of DataTable at Origin 1
 560      
 561      ⍝ Split the Left Argument:
 562       (dt rowNumber)←la
 563      
 564      ⍝ If a Vector, set as a one row matrix:
 565       :If 1=⍴⍴apl
 566           apl←(1,⍴apl)⍴apl
 567       :EndIf
 568      
 569      ⍝ Change the Data:
 570       2010⌶dt apl ⍬(rowNumber-1)
 571     ∇
 572 
 573     ∇ ShowDS ds;dg;dt;index;tc;ti;win;⎕USING
 574     ⍝ Show a DataSet in multiple Syncfusion's Grid
 575     ⍝ Each Table(s) is shown in a separate Tab
 576     ⍝ ds = DataSet to Show
 577      
 578       ⎕USING←'System.Windows,WPF/PresentationFramework.dll'
 579       ⎕USING,←⊂'System.Windows.Controls,WPF/PresentationFramework.dll'
 580       ⎕USING,←⊂'Syncfusion.UI.Xaml.Grid,Syncfusion/4.5/Syncfusion.SfGrid.WPF.dll'
 581       :Trap 0 ⋄ Anything ⋄ :EndTrap  ⍝ To load ⎕USING into memory
 582      
 583     ⍝ Get a Window
 584       win←⎕NEW Window
 585       win.(Width Height)←640 480
 586       win.Title←'Show DataSet[ ',(ds.DataSetName),' ]'
 587      
 588     ⍝ Get a TabControl that will hold the TabItem
 589       tc←⎕NEW TabControl
 590      
 591     ⍝ Iterate to set each Tables in a TabItem
 592       :For index :In ⍳ds.Tables.Count
 593      
 594           dt←ds.Tables[index-1]
 595      
 596           ti←⎕NEW TabItem
 597           ti.Header←dt.TableName      ⍝ ← Name of the Tab
 598      
 599           dg←⎕NEW SfDataGrid
 600           dg.AutoGenerateColumns←1
 601           dg.ItemsSource←dt           ⍝ ← the DataTable is set here
 602           dg.AllowResizingColumns←1
 603           dg.(ColumnSizer←ColumnSizer.Star)
 604           dg.AllowEditing←1
 605      
 606           ti.Content←dg
 607           {}tc.Items.Add(ti)
 608      
 609       :EndFor
 610      
 611     ⍝ Set the content of the Window with the TabControl and Show
 612       win.Content←tc
 613       win.Show
 614     ∇
 615 
 616     ∇ ShowDT dt;dg;win;⎕USING
 617     ⍝ Show a DataTable in a Syncfusion's Grid
 618     ⍝ dt = DataTable to Show
 619      
 620       ⎕USING←'System.Windows,WPF/PresentationFramework.dll'
 621       ⎕USING,←⊂'System.Windows.Controls,WPF/PresentationFramework.dll'
 622       ⎕USING,←⊂'Syncfusion.UI.Xaml.Grid,Syncfusion/4.5/Syncfusion.SfGrid.WPF.dll'
 623       :Trap 0 ⋄ Anything ⋄ :EndTrap  ⍝ To load ⎕USING into memory
 624      
 625       win←⎕NEW Window
 626       win.(Width Height)←640 480
 627       win.Title←'Show DataTable [ ',(dt.TableName),' ]'
 628      
 629       dg←⎕NEW SfDataGrid
 630       dg.AutoGenerateColumns←1
 631       dg.ItemsSource←dt           ⍝ ← the DataTable is set here
 632       dg.AllowResizingColumns←1
 633       dg.(ColumnSizer←ColumnSizer.Star)
 634       dg.AllowEditing←1
 635      
 636       win.Content←dg
 637       win.Show
 638     ∇
 639 
 640     ∇ dt SortAscending colNumber;colName
 641     ⍝ Sort Ascending a DataTable based on the value of a Column
 642     ⍝ colNumber = Index of the column (Origin 1)
 643     ⍝ dt        = Data Table
 644      
 645       colName←dt.Columns[colNumber-1].ColumnName
 646       dt.DefaultView.Sort←colName,' ASC'
 647       dt←dt.DefaultView.ToTable ⍬
 648     ∇
 649 
 650     ∇ dt SortDescending colNumber;colName
 651     ⍝ Sort Descending a DataTable based on the value of a Column
 652     ⍝ colNumber = Index of the column (Origin 1)
 653     ⍝ dt        = Data Table
 654      
 655       colName←dt.Columns[colNumber-1].ColumnName
 656       dt.DefaultView.Sort←colName,' DESC'
 657       dt←dt.DefaultView.ToTable ⍬
 658     ∇
 659 
 660     ∇ ds←XmlFileToDS fileName;⎕USING
 661     ⍝ Retrieves a DataSet from an Xml representation made by DStoXmlFile
 662     ⍝ fileName = fully qualified file name
 663     ⍝ ds       = DataSet
 664      
 665       ⎕USING←'System.Data,System.Data.dll'
 666       ds←⎕NEW DataSet
 667       {}ds.ReadXml(⊂,fileName)
 668     ∇
 669 
 670     ∇ dt←XmlFileToDT fileName;⎕USING
 671     ⍝ Retrieves a DataTable from an Xml representation made by DTtoXmlFile
 672     ⍝ fileName = fully qualified file name
 673     ⍝ dt       = DataTable
 674      
 675       ⎕USING←'System.Data,System.Data.dll'
 676       dt←⎕NEW DataTable
 677       {}dt.ReadXml(⊂,fileName)
 678     ∇
 679 
 680     ∇ dt←XmlToDT xmlDoc;ds;⎕USING
 681      ⍝ Obtain a DataTable from an XmlDoc made with DTtoXml.
 682      ⍝ xmlDoc = XmlDocument
 683      ⍝ dt     = DataTable
 684      
 685       ⎕USING←'System.Data,System.Data.dll' 'System.Xml,System.Xml.dll'
 686       ds←⎕NEW DataSet
 687       {}ds.ReadXml(⎕NEW XmlNodeReader xmlDoc)
 688       dt←ds.Tables[0]
 689     ∇
 690 
 691     ∇ ns←makeNs
 692     ⍝ Build a sample nameless namespace
 693      
 694       ns←⎕NS''
 695       ns.Books←⎕NS''
 696       ns.Books.Author←'author1' 'author2' 'author3'
 697       ns.Books.Title←'title1' 'title2' 'title3'
 698       ns.Books.Price←100 200 300
 699       ns.Books.IsAvailable_b←1 0 ¯1
 700       ns.Books.Date_d1←42000 43000 44000
 701      
 702       ns.Inventory←⎕NS''
 703       ns.Inventory.Title←'title1' 'title2' 'title3'
 704       ns.Inventory.Quantity←100 200 300
 705       ns.Inventory.IsShipped_b←1 0 ¯1
 706       ns.Inventory.DatePurchased_d2←(2014 12 1)(2013 8 4)(2012 6 2)
 707       ns.Inventory.DateShipped_d3←'2014-12-5' '2013/8/9' '2012 6 5'
 708     ∇
 709 
 710 :EndNamespace