:Namespace netDataTable ⍝ Methods related to a .Net DataTable. (⎕IO ⎕ML ⎕WX)←1 3 3 ∇ dt AddRow apl;newRow ⍝ Add a Row At the End of An Existing DataTable ⍝ dt = DataTable ⍝ apl = Data to Add as Vector or Matrix of proper dimension. ⍝ If a Vector, set as a one row matrix: :If 1=⍴⍴apl apl←(1,⍴apl)⍴apl :EndIf ⍝ Add the data: 2010⌶dt apl ⍝ Equivalent way to add only one row: ⍝ newRow←dt.NewRow ⍝ newRow.ItemArray←,apl ⍝ dt.Rows.Add(newRow) ⍝ or ⍝ dt.Rows.Add(⊂apl) ∇ ∇ dt←{columnNames}AplToDT aplArray;columnType;index;tableName;⎕USING ⍝ Create a DataTable from an Apl Array. ⍝ EACH COLUMN MUST BE OF THE SAME TYPE. ⍝ For dates, convert ⎕TS to an OADate before making the DataTable. ⍝ apl = An APL array of Numbers and Characters. ⍝ columnNames = Column names for the DataTable. Works best with 2 characters per name. ⍝ dt = Resulting DataTable ⍝ If a Vector, set as a one row matrix: :If 1=⍴⍴aplArray aplArray←(1,⍴aplArray)⍴aplArray :EndIf ⍝ Check if aplArray is a 2 dimensional array: :If 2≠⍴⍴aplArray ⎕←'AplToDT Error: Argument must be of rank equal or smaller than 2' →0 :End ⍝ Check if columnNames is properly formed: :If 0=⎕NC'columnNames' ⍝ There is no columnNames :OrIf (⍴columnNames)≠1↓⍴aplArray ⍝ Wrong Shape :OrIf 1∊0=1↑¨0⍴¨columnNames ⍝ Not characters :OrIf 0∊≡¨columnNames ⍝ Wrong Depth ⍝ 'columnNames' is not supplied or invalid: ⍝ Generate the Column Names as 'C1' 'C2', etc.: columnNames←'C',¨⍕¨⍳1↓⍴aplArray :End tableName←'Data' ⍝ Default TableName ⍝ Default value in case of error: ⎕USING←'System' 'System.Data,System.Data.dll' dt←⎕NEW DataTable(⊂tableName) :Trap 0 ⍝ Determine the Type of each column: columnType←'' :For index :In ⍳1↓⍴aplArray :Select ⎕DR aplArray[;index] :CaseList 80 160 320 326 columnType,←String :Else columnType,←Double :EndSelect :EndFor ⍝ Set the Column's Types and Names of the DataTable: {}columnNames{dt.Columns.Add ⍺ ⍵}¨columnType ⍝ Fill the DataTable: 2010⌶dt aplArray :EndTrap ∇ ∇ dt←BinFileToDT fileName;fileStream;binaryFormatter;⎕USING ⍝ Retrieves a DataTable from a Binary representation made by DTtoBinFile ⍝ fileName = fully qualified file name ⍝ dt = DataTable ⍝ Read the fileName ⎕USING←',mscorlib.dll' fileStream←⎕NEW System.IO.FileStream(fileName System.IO.FileMode.Open) ⍝ Get a BinaryFormatter and Deserialize the file stream binaryFormatter←⎕NEW System.Runtime.Serialization.Formatters.Binary.BinaryFormatter dt←binaryFormatter.Deserialize fileStream fileStream.Close ∇ ∇ apl←DTtoApl dt ⍝ Convert a .Net DataTable to APL ⍝ dt = DataTable ⍝ apl = apl representation of the DataTable :If 9≠⎕NC'dt' :OrIf 'System.Data.DataTable'≢dt.GetType.ToString ⎕←'DTtoApl Error: The argument is not a DataTable Object !' :EndIf apl←2011⌶dt ∇ ∇ r←dt DTtoBinFile fileName;binaryFormatter;fileStream;⎕USING ⍝ Saves a Binary representation of the DataTable to a file name ⍝ fileName = fully qualified file name ⍝ dt = DataTable ⍝ Get a Binary Formatter ⎕USING←',mscorlib.dll' binaryFormatter←⎕NEW System.Runtime.Serialization.Formatters.Binary.BinaryFormatter ⍝ Write to fileName and close the FileStream fileStream←⎕NEW System.IO.FileStream(fileName System.IO.FileMode.Create) binaryFormatter.Serialize fileStream dt fileStream.Close ∇ ∇ xmlDoc←DTtoXml dt;ms;⎕USING ⍝ Generates the Xml representation of a Data Table. ⍝ dt = DataTable ⍝ xmlDoc = XmlDocument ⍝ Get an Empty Memory Stream: ⎕USING←'System.IO,mscorlib.dll' ms←⎕NEW MemoryStream ⍝ Set the XML Mapping as Attribute for all the columns: (⌷dt.Columns).ColumnMapping←dt.Columns[0].ColumnMapping.Attribute ⍝ Write to the Memory Stream the Xml Representation of the Data Table with the Xls Schema: ⎕USING←'System.Data,System.Data.dll' 'System.Xml,System.Xml.dll' dt.WriteXml(ms XmlWriteMode.WriteSchema) ⍝ XmlWriteMode.IgnoreSchema also available ⍝ Set the position of the memory stream at the beginning ⎕USING←'System,mscorlib.dll' ms.Position←Convert.ToInt64 0 ⍝ Write the Memory Stream to an XmlDocument ⎕USING←'System.Xml,System.Xml.dll' xmlDoc←⎕NEW XmlDocument xmlDoc.Load ms xmlDoc.DocumentElement.SetAttribute('xmlns' '') ⍝ Clean-up ms.Close ⋄ ms.Dispose ⋄ ms←⎕NULL ∇ ∇ dt DTtoXmlFile fileName;⎕USING ⍝ Saves an Xml representation of the DataTable to a file name ⍝ fileName = fully qualified file name ⍝ dt = DataTable ⍝ Set the XML Mapping as Attribute for all the column: (⌷dt.Columns).ColumnMapping←dt.Columns[0].ColumnMapping.Attribute ⍝ Write to a Xml file with the Schema ⎕USING←'System.Data,System.Data.dll' dt.WriteXml(fileName XmlWriteMode.WriteSchema) ∇ ∇ apl←dt GetCol colNumber;colName;⎕USING;string;dv ⍝ Get the value of a single column of an existing DataTable ⍝ dt = Data Table ⍝ colNumber = Index of the column (Origin 1) ⍝ apl = Apl data ⍝ Get the name of the column colName←dt.Columns[colNumber-1].ColumnName ⍝ Create a .net string vector (string[]) with that name ⎕USING←'' 'System.Data,System.Data.dll' string←System.Array.CreateInstance(System.Type.GetType⊂'System.String')1 string.SetValue(colName 0) ⍝ Make a DataView and filter the method .ToTable dv←⎕NEW DataView dt apl←,2011⌶dv.ToTable(0 string) ∇ ∇ colNames←GetColumnNames dt ⍝ Get the names of all the columns ⍝ dt = Data Table ⍝ colNames = Column Names of all the columns colNames←(⌷dt.Columns).ColumnName ∇ ∇ apl←dt GetRow rowNumber ⍝ Get the value of a row of an existing DataTable ⍝ rowNumber = Index of the row (Origin 1) ⍝ dt = Data Table ⍝ apl = Apl data apl←dt.Rows[rowNumber-1].ItemArray ∇ ∇ la InsertRow apl;dt;rowNumber;newRow ⍝ Insert a new Row in a DataTable ⍝ apl = New Data to Insert ⍝ dt = DataTable ⍝ rowNumber = Row Number of DataTable after wich the data will ⍝ = be inserted (Origin 1) ⍝ Split the Left Argument: (dt rowNumber)←la newRow←dt.NewRow newRow.ItemArray←apl dt.Rows.InsertAt(newRow,rowNumber) ∇ ∇ la SetRow apl;dt;rowNumber ⍝ Update a row of a DataTable with new values. ⍝ apl = New Data to Replace the Existing Ones ⍝ dt = DataTable ⍝ rowNumber = Row Number of DataTable at Origin 1 ⍝ Split the Left Argument: (dt rowNumber)←la ⍝ If a Vector, set as a one row matrix: :If 1=⍴⍴apl apl←(1,⍴apl)⍴apl :EndIf ⍝ Change the Data: 2010⌶dt apl ⍬(rowNumber-1) ∇ ∇ ShowDT dt;sfDir;win;gdc;gdcs ⍝ Show a DataTable as a Syncfusion's Grid ⍝ dt = DataTable to Show ⍝ Location ofthe Syncfusion directory sfDir←'Syncfusion/4.5/' ⎕USING←'System.Windows,WPF/PresentationFramework.dll' win←⎕NEW Window win.HorizontalContentAlignment←win.HorizontalContentAlignment.Stretch win.Title←'Show DataTable' ⎕USING←'Syncfusion.Windows.Controls.Grid,',sfDir,'Syncfusion.Grid.WPF.dll' gdc←⎕NEW GridDataControl gdc.AutoPopulateColumns←1 gdc.ItemsSource←dt ⍝ ← the DataTable is set here gdc.VisualStyle←gdc.VisualStyle.Metro gdc.ColumnSizer←gdc.ColumnSizer.Star gdc.ShowAddNewRow←0 gdc.ShowColumnOptions←0 gdc.ShowGroupDropArea←1 gdc.ShowHoveringBackground←1 gdc.HeaderColumns←1 gdc.AllowEdit←0 win.Content←gdc win.Show ∇ ∇ dt←XmlFileToDT fileName;⎕USING ⍝ Retrieves a DataTable from an Xml representation made by DTtoXmlFile ⍝ fileName = fully qualified file name ⍝ dt = DataTable ⎕USING←'System.Data,System.Data.dll' dt←⎕NEW DataTable {}dt.ReadXml(⊂,fileName) ∇ ∇ dt←XmlToDT xmlDoc;ds;⎕USING ⍝ Obtain a DataTable from an XmlDoc made with DTtoXml. ⍝ xmlDoc = XmlDocument ⍝ dt = DataTable ⎕USING←'System.Data,System.Data.dll' 'System.Xml,System.Xml.dll' ds←⎕NEW DataSet {}ds.ReadXml(⎕NEW XmlNodeReader xmlDoc) dt←ds.Tables[0] ∇ :EndNamespace