1 :Namespace netDataTable
   2 
   3 ⍝ Methods related to a .Net DataTable.
   4 
   5     (⎕IO ⎕ML ⎕WX)←1 3 3
   6 
   7     ∇ dt AddRow apl;newRow
   8      ⍝ Add a Row At the End of An Existing DataTable
   9      ⍝ dt   = DataTable
  10      ⍝ apl  = Data to Add as Vector or Matrix of proper dimension.
  11      
  12      ⍝ If a Vector, set as a one row matrix:
  13       :If 1=⍴⍴apl
  14           apl←(1,⍴apl)⍴apl
  15       :EndIf
  16      
  17      ⍝ Add the data:
  18       2010⌶dt apl
  19      
  20      ⍝ Equivalent way to add only one row:
  21 ⍝      newRow←dt.NewRow
  22 ⍝      newRow.ItemArray←,apl
  23 ⍝      dt.Rows.Add(newRow)
  24 ⍝      or
  25 ⍝      dt.Rows.Add(⊂apl)
  26      
  27     ∇
  28 
  29     ∇ dt←{columnNames}AplToDT aplArray;columnType;index;tableName;⎕USING
  30      ⍝ Create a DataTable from an Apl Array.
  31      ⍝ EACH COLUMN MUST BE OF THE SAME TYPE.
  32      ⍝ For dates, convert ⎕TS to an OADate before making the DataTable.
  33      ⍝ apl         = An APL array of Numbers and Characters.
  34      ⍝ columnNames = Column names for the DataTable. Works best with 2 characters per name.
  35      ⍝ dt          = Resulting DataTable
  36      
  37      ⍝ If a Vector, set as a one row matrix:
  38       :If 1=⍴⍴aplArray
  39           aplArray←(1,⍴aplArray)⍴aplArray
  40       :EndIf
  41      
  42      ⍝ Check if aplArray is a 2 dimensional array:
  43       :If 2≠⍴⍴aplArray
  44           ⎕←'AplToDT Error: Argument must be of rank equal or smaller than 2'
  45           →0
  46       :End
  47      
  48      ⍝ Check if columnNames is properly formed:
  49       :If 0=⎕NC'columnNames'            ⍝ There is no columnNames
  50       :OrIf (⍴columnNames)≠1↓⍴aplArray  ⍝ Wrong Shape
  51       :OrIf 1∊0=1↑¨0⍴¨columnNames       ⍝ Not characters
  52       :OrIf 0∊≡¨columnNames             ⍝ Wrong Depth
  53      
  54         ⍝ 'columnNames' is not supplied or invalid:
  55         ⍝ Generate the Column Names as 'C1' 'C2', etc.:
  56           columnNames←'C',¨⍕¨⍳1↓⍴aplArray
  57       :End
  58      
  59       tableName←'Data'  ⍝ Default TableName
  60      
  61     ⍝ Default value in case of error:
  62       ⎕USING←'System' 'System.Data,System.Data.dll'
  63       dt←⎕NEW DataTable(⊂tableName)
  64      
  65       :Trap 0
  66          ⍝ Determine the Type of each column:
  67           columnType←''
  68           :For index :In ⍳1↓⍴aplArray
  69               :Select ⎕DR aplArray[;index]
  70               :CaseList 80 160 320 326
  71                   columnType,←String
  72               :Else
  73                   columnType,←Double
  74               :EndSelect
  75           :EndFor
  76      
  77          ⍝ Set the Column's Types and Names of the DataTable:
  78           {}columnNames{dt.Columns.Add ⍺ ⍵}¨columnType
  79      
  80          ⍝ Fill the DataTable:
  81           2010⌶dt aplArray
  82      
  83       :EndTrap
  84     ∇
  85 
  86     ∇ dt←BinFileToDT fileName;fileStream;binaryFormatter;⎕USING
  87      ⍝ Retrieves a DataTable from a Binary representation made by DTtoBinFile
  88      ⍝ fileName = fully qualified file name
  89      ⍝ dt       = DataTable
  90      
  91      ⍝ Read the fileName
  92       ⎕USING←',mscorlib.dll'
  93       fileStream←⎕NEW System.IO.FileStream(fileName System.IO.FileMode.Open)
  94      
  95      ⍝ Get a BinaryFormatter and Deserialize the file stream
  96       binaryFormatter←⎕NEW System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
  97       dt←binaryFormatter.Deserialize fileStream
  98       fileStream.Close
  99     ∇
 100 
 101     ∇ apl←DTtoApl dt
 102     ⍝ Convert a .Net DataTable to APL
 103     ⍝ dt  = DataTable
 104     ⍝ apl = apl representation of the DataTable
 105      
 106       :If 9≠⎕NC'dt'
 107       :OrIf 'System.Data.DataTable'≢dt.GetType.ToString
 108           ⎕←'DTtoApl Error: The argument is not a DataTable Object !'
 109       :EndIf
 110      
 111       apl←2011⌶dt
 112     ∇
 113 
 114     ∇ r←dt DTtoBinFile fileName;binaryFormatter;fileStream;⎕USING
 115     ⍝ Saves a Binary representation of the DataTable to a file name
 116     ⍝ fileName = fully qualified file name
 117     ⍝ dt       = DataTable
 118      
 119     ⍝ Get a Binary Formatter
 120       ⎕USING←',mscorlib.dll'
 121       binaryFormatter←⎕NEW System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
 122      
 123     ⍝ Write to fileName and close the FileStream
 124       fileStream←⎕NEW System.IO.FileStream(fileName System.IO.FileMode.Create)
 125       binaryFormatter.Serialize fileStream dt
 126       fileStream.Close
 127     ∇
 128 
 129     ∇ xmlDoc←DTtoXml dt;ms;⎕USING
 130     ⍝ Generates the Xml representation of a Data Table.
 131     ⍝ dt     = DataTable
 132     ⍝ xmlDoc = XmlDocument
 133      
 134     ⍝ Get an Empty Memory Stream:
 135       ⎕USING←'System.IO,mscorlib.dll'
 136       ms←⎕NEW MemoryStream
 137      
 138     ⍝ Set the XML Mapping as Attribute for all the columns:
 139       (⌷dt.Columns).ColumnMapping←dt.Columns[0].ColumnMapping.Attribute
 140      
 141     ⍝ Write to the Memory Stream the Xml Representation of the Data Table with the Xls Schema:
 142       ⎕USING←'System.Data,System.Data.dll' 'System.Xml,System.Xml.dll'
 143       dt.WriteXml(ms XmlWriteMode.WriteSchema) ⍝ XmlWriteMode.IgnoreSchema also available
 144      
 145     ⍝ Set the position of the memory stream at the beginning
 146       ⎕USING←'System,mscorlib.dll'
 147       ms.Position←Convert.ToInt64 0
 148      
 149     ⍝ Write the Memory Stream to an XmlDocument
 150       ⎕USING←'System.Xml,System.Xml.dll'
 151       xmlDoc←⎕NEW XmlDocument
 152       xmlDoc.Load ms
 153       xmlDoc.DocumentElement.SetAttribute('xmlns' '')
 154      
 155     ⍝ Clean-up
 156       ms.Close ⋄ ms.Dispose ⋄ ms←⎕NULL
 157     ∇
 158 
 159     ∇ dt DTtoXmlFile fileName;⎕USING
 160     ⍝ Saves an Xml representation of the DataTable to a file name
 161     ⍝ fileName = fully qualified file name
 162     ⍝ dt       = DataTable
 163      
 164     ⍝ Set the XML Mapping as Attribute for all the column:
 165       (⌷dt.Columns).ColumnMapping←dt.Columns[0].ColumnMapping.Attribute
 166      
 167     ⍝ Write to a Xml file with the Schema
 168       ⎕USING←'System.Data,System.Data.dll'
 169       dt.WriteXml(fileName XmlWriteMode.WriteSchema)
 170     ∇
 171 
 172     ∇ apl←dt GetCol colNumber;colName;⎕USING;string;dv
 173      ⍝ Get the value of a single column of an existing DataTable
 174      ⍝ dt        = Data Table
 175      ⍝ colNumber = Index of the column (Origin 1)
 176      ⍝ apl       = Apl data
 177      
 178      ⍝ Get the name of the column
 179       colName←dt.Columns[colNumber-1].ColumnName
 180      
 181      ⍝ Create a .net string vector (string[]) with that name
 182       ⎕USING←'' 'System.Data,System.Data.dll'
 183       string←System.Array.CreateInstance(System.Type.GetType⊂'System.String')1
 184       string.SetValue(colName 0)
 185      
 186      ⍝ Make a DataView and filter the method .ToTable
 187       dv←⎕NEW DataView dt
 188       apl←,2011⌶dv.ToTable(0 string)
 189     ∇
 190 
 191     ∇ colNames←GetColumnNames dt
 192      ⍝ Get the names of all the columns
 193      ⍝ dt       = Data Table
 194      ⍝ colNames = Column Names of all the columns
 195      
 196       colNames←(⌷dt.Columns).ColumnName
 197     ∇
 198 
 199     ∇ apl←dt GetRow rowNumber
 200      ⍝ Get the value of a row of an existing DataTable
 201      ⍝ rowNumber = Index of the row (Origin 1)
 202      ⍝ dt        = Data Table
 203      ⍝ apl       = Apl data
 204      
 205       apl←dt.Rows[rowNumber-1].ItemArray
 206     ∇
 207 
 208     ∇ la InsertRow apl;dt;rowNumber;newRow
 209      ⍝ Insert a new Row in a DataTable
 210      ⍝ apl       = New Data to Insert
 211      ⍝ dt        = DataTable
 212      ⍝ rowNumber = Row Number of DataTable after wich the data will
 213      ⍝           = be inserted (Origin 1)
 214      
 215      ⍝ Split the Left Argument:
 216       (dt rowNumber)←la
 217      
 218       newRow←dt.NewRow
 219       newRow.ItemArray←apl
 220       dt.Rows.InsertAt(newRow,rowNumber)
 221     ∇
 222 
 223     ∇ la SetRow apl;dt;rowNumber
 224      ⍝ Update a row of a DataTable with new values.
 225      ⍝ apl       = New Data to Replace the Existing Ones
 226      ⍝ dt        = DataTable
 227      ⍝ rowNumber = Row Number of DataTable at Origin 1
 228      
 229      ⍝ Split the Left Argument:
 230       (dt rowNumber)←la
 231      
 232      ⍝ If a Vector, set as a one row matrix:
 233       :If 1=⍴⍴apl
 234           apl←(1,⍴apl)⍴apl
 235       :EndIf
 236      
 237      ⍝ Change the Data:
 238       2010⌶dt apl ⍬(rowNumber-1)
 239     ∇
 240 
 241     ∇ ShowDT dt;sfDir;win;gdc;gdcs
 242     ⍝ Show a DataTable as a Syncfusion's Grid
 243     ⍝ dt = DataTable to Show
 244      
 245     ⍝ Location ofthe Syncfusion directory
 246       sfDir←'Syncfusion/4.5/'
 247      
 248       ⎕USING←'System.Windows,WPF/PresentationFramework.dll'
 249       win←⎕NEW Window
 250       win.HorizontalContentAlignment←win.HorizontalContentAlignment.Stretch
 251       win.Title←'Show DataTable'
 252      
 253       ⎕USING←'Syncfusion.Windows.Controls.Grid,',sfDir,'Syncfusion.Grid.WPF.dll'
 254       gdc←⎕NEW GridDataControl
 255       gdc.AutoPopulateColumns←1
 256       gdc.ItemsSource←dt ⍝ ← the DataTable is set here
 257       gdc.VisualStyle←gdc.VisualStyle.Metro
 258       gdc.ColumnSizer←gdc.ColumnSizer.Star
 259       gdc.ShowAddNewRow←0
 260       gdc.ShowColumnOptions←0
 261       gdc.ShowGroupDropArea←1
 262       gdc.ShowHoveringBackground←1
 263       gdc.HeaderColumns←1
 264       gdc.AllowEdit←0
 265      
 266       win.Content←gdc
 267       win.Show
 268     ∇
 269 
 270     ∇ dt←XmlFileToDT fileName;⎕USING
 271     ⍝ Retrieves a DataTable from an Xml representation made by DTtoXmlFile
 272     ⍝ fileName = fully qualified file name
 273     ⍝ dt       = DataTable
 274      
 275       ⎕USING←'System.Data,System.Data.dll'
 276       dt←⎕NEW DataTable
 277       {}dt.ReadXml(⊂,fileName)
 278     ∇
 279 
 280     ∇ dt←XmlToDT xmlDoc;ds;⎕USING
 281      ⍝ Obtain a DataTable from an XmlDoc made with DTtoXml.
 282      ⍝ xmlDoc = XmlDocument
 283      ⍝ dt     = DataTable
 284      
 285       ⎕USING←'System.Data,System.Data.dll' 'System.Xml,System.Xml.dll'
 286       ds←⎕NEW DataSet
 287       {}ds.ReadXml(⎕NEW XmlNodeReader xmlDoc)
 288       dt←ds.Tables[0]
 289     ∇
 290 
 291 :EndNamespace