:Namespace netDataTable ⍝ Methods related to .Net DataTable and DataSet. (⎕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) ∇ ∇ ds←{dataSetName}AplToDS nss;array;colName;colNames;colType;dt;index;IsChar;IsNum;ns;nsName;parseDate;ptr;test;⎕USING ⍝ Function to convert namespaces within a namespace to a .Net DataSet ⍝ ⍝ nss = namespaces within a nameless namespace ⍝ ds = .Net DataSet that contains a collection of DataTable(s) ⍝ ⍝ The properties name of the namespace can finish with _? to apply a two way converter: ⍝ _b : Boolean converter 1, 0 ¯1 is True, False, Indeterminate ⍝ _d1 : OADate ⍝ _d2 : 3↑⎕TS or 6↑⎕TS ⍝ _d3 : Character representation of a date ⍝ ⍝ Typical usage: ⍝ 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 ⍝ Boolean converter ⍝ ns.Books.Date_d1←42000 43000 44000 ⍝ OADate converter ⍝ ns.Books.Date_d2←(2014 12 1)(2013 8 4)(2012 6 2) ⍝ 3↑⎕TS converter ⍝ ns.Books.Date_d3←'2014-12-5' '2013/8/9' '2012 6 5' ⍝ Characters of date converter ⍝ ⍝ ds←AplToDS ns ⍝ ShowDT ds.Tables[⊂'Books'] ⍝ Show and modify the DataTable ⍝ ns←DStoApl ds ⍝ Convert back to Apl the DataSet ⍝ ⍝ or if you are binding use the following statement: ⍝ ItemsSource="{Binding Tables[Books]}" ⍝ and set the DataContext to the DataSet created with AplToDS :If 9≠⎕NC'nss' :OrIf '[Namespace]'≢¯11↑⍕nss ⎕←'AplToDS Error: Argument must be a nameless Namespace' →0 :EndIf IsChar←{' '=↑1↑0⍴⍵} IsNum←{0=↑1↑0⍴⍵} :If 0=⎕NC'dataSetName' dataSetName←'DataSet' :ElseIf ~IsChar dataSetName ⎕←'AplToDS Error: dataSetName must be Characters' →0 :End ⎕USING←'System' 'System.Data,System.Data.dll' 'Dyalog' 'System.Windows.Controls,WPF/PresentationFramework.dll' ds←⎕NEW DataSet(⊂dataSetName) ⍝ Default value in case of error ⍝ TryParse will return 1 if successful with the parsed value in 'ptr' ptr←⎕NEW ByRef(⎕NEW DateTime ⎕TS) ⍝ Pointer to receive the conversion of 'parseData' if successfull parseDate←{(DateTime.TryParse(⍵ ptr)):ptr.Value ⍝ Parse characters to a .Net DateTime Object ⎕NULL} ⍝ Default value if characters are not a valid date :Trap 0 ⍝ Convert each simple namespaces into a DataTable and Add it to the DataSet: :For nsName :In nss.⎕NL-9 ns←nss.⍎nsName ⍝ Current namespace of variables colNames←(ns.⎕NL-2) ⍝ Get the variable names (colNames) of the namespace array←⍉⊃ns.⍎¨colNames ⍝ Get all the values of the colNames as a matrix ⍝ Convert the namespace into a DataTable dt←⎕NEW DataTable(⊂nsName) ⍝ Determine the Type of each column colType←'' :For index :In ⍳2⌷⍴array colName←index⊃colNames :If '_b'≡¯2↑colName ⍝ 1 and 0 to Boolean. ¯1 is Inderminate state (System.DBNull) :If ∧/IsNum¨array[;index] colType,←Boolean test←array[;index]=¯1 ⍝ ¯1 = Indeterminate state array[{⍵/⍳⍴⍵}test;index]←DBNull.Value ⍝ Change the ¯1 for DBNull.Value array[{⍵/⍳⍴⍵}~test;index]←1⌊0⌈array[{⍵/⍳⍴⍵}~test;index] ⍝ Coerce to 1 or 0 all the other values :Else colType,←String array[;index]←⍕¨array[;index] ⎕←'AplToDS Error: Don''t know what to do with: ',nsName,'.',colName :End :ElseIf '_d1'≡¯3↑colName ⍝ Numeric OADates to DateTime :If ∧/IsNum¨array[;index] colType,←DateTime array[;index]←DateTime.FromOADate¨array[;index] :Else colType,←String array[;index]←⍕¨array[;index] ⎕←'AplToDS Error: Don''t know what to do with: ',nsName,'.',colName :End :ElseIf '_d2'≡¯3↑colName ⍝ 3↑⎕TS or 6↑⎕TS to DateTime colType,←DateTime array[;index]←parseDate¨⍕¨array[;index] ⍝ parseDate cannot bug :ElseIf '_d3'≡¯3↑colName ⍝ Character representation of a date colType,←DateTime array[;index]←parseDate¨⍕¨array[;index] ⍝ parseDate cannot bug :ElseIf ∧/IsNum¨array[;index] ⍝ Only numbers with no converters colType,←Double :ElseIf ∧/IsChar¨array[;index] ⍝ Only characters with no converters colType,←String :Else ⎕←'AplToDS Error: Don''t know what to do with: ',nsName,'.',colName colType,←String array[;index]←⍕¨array[;index] :EndIf :EndFor ⍝ Set the Column's Types and Names of the DataTable: {}colNames{dt.Columns.Add ⍺ ⍵}¨colType ⍝ Fill the DataTable 2010⌶dt array ⍝ Add the DataTable to the DataSet ds.Tables.Add(dt) :EndFor :EndTrap ∇ ∇ dt←{colNames}AplToDT aplArray;colType;index;IsChar;IsNum;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. ⍝ colNames = 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'colNames' ⍝ There is no columnNames :OrIf (⍴colNames)≠1↓⍴aplArray ⍝ Wrong Shape :OrIf 1∊0=1↑¨0⍴¨colNames ⍝ Not characters :OrIf 0∊≡¨colNames ⍝ Wrong Depth ⍝ 'colNames' is not supplied or invalid: ⍝ Generate the Column Names as 'C1' 'C2', etc.: colNames←'C',¨⍕¨⍳1↓⍴aplArray :End tableName←'Data' ⍝ Default TableName IsChar←{' '=↑1↑0⍴⍵} IsNum←{0=↑1↑0⍴⍵} ⍝ Default value in case of error: ⎕USING←'System' 'System.Data,System.Data.dll' dt←⎕NEW DataTable(⊂tableName) :Trap 99 ⍝ Determine the Type of each column: colType←'' :For index :In ⍳2⌷⍴aplArray :If ∧/IsChar¨aplArray[;index] ⍝ Default Type for characters colType,←String :ElseIf ∧/IsNum¨aplArray[;index] ⍝ Default Type for numbers colType,←Double :Else ⍝ Mixed Type in the same column ⎕←'Don''t know what to do with column no: ',⍕index aplArray[;index]←⍕¨aplArray[;index] colType,←String :EndIf :EndFor ⍝ Set the Column's Types and Names of the DataTable: {}colNames{dt.Columns.Add ⍺ ⍵}¨colType ⍝ Fill the DataTable: 2010⌶dt aplArray :EndTrap ∇ ∇ dt←BinFileToDT fileName;binaryFormatter;fileStream;⎕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←DStoApl ds;array;colDataType;colDataTypes;colName;colNames;dateCvt;dt;index;ns;test;⎕USING ⍝ Function to convert a .Net DataSet made with AplToDS to an APL namespace ⍝ ⍝ ds = .Net DataSet ⍝ apl = Namespaces within a nameless namespace :If 9≠⎕NC'ds' :OrIf 'System.Data.DataSet'≢⍕ds ⎕←'DStoApl Error: Argument must be a DataSet' →0 :EndIf ⎕USING←'System' apl←⎕NS'' ⍝ Default value in case of error ⍝ Convert each DataTable to a named namespace :For dt :In ⌷ds.Tables ns←⎕NS'' ⍝ Empty nameless namespace colNames←(⌷dt.Columns).ColumnName ⍝ Column names of the DataTable colDataTypes←(⌷dt.Columns).DataType ⍝ Type of the column array←2011⌶dt ⍝ Convert the DataTable to an APL array :For index :In ⍳2⌷⍴array colName←index⊃colNames colDataType←⍕index⊃colDataTypes :If '_d1'≡¯3↑colName ⍝ Return the .Net DateTime object as numeric OADates :If 'System.DateTime'≡colDataType test←array[;index]≠DBNull.Value array[{⍵/⍳⍴⍵}~test;index]←0 ⍝ Default value if DBNull is returned array[{⍵/⍳⍴⍵}test;index]←(array[{⍵/⍳⍴⍵}test;index]).ToOADate :Else ⎕←'DStoApl Error: Don''t know what to do with: ',(dt.TableName),'.',colName :End :ElseIf '_d2'≡¯3↑colName ⍝ Return the .Net DateTime object as numeric 3↑⎕TS :If 'System.DateTime'≡colDataType test←array[;index]≠DBNull.Value array[{⍵/⍳⍴⍵}~test;index]←⊂1900 1 1 ⍝ Default value if DBNull is returned array[{⍵/⍳⍴⍵}test;index]←(array[{⍵/⍳⍴⍵}test;index]).(Year Month Day) :Else ⎕←'DStoApl Error: Don''t know what to do with: ',(dt.TableName),'.',colName :End :ElseIf '_d3'≡¯3↑colName ⍝ Return the .Net DateTime object as a character representation of the date :If 'System.DateTime'≡colDataType test←array[;index]≠DBNull.Value array[{⍵/⍳⍴⍵}~test;index]←⊂'1/1/1900' ⍝ Default value if DBNull is returned dateCvt←{⎕USING←'System.Globalization' ⋄ ⍵.ToString((,'G')CultureInfo.InvariantCulture)} array[{⍵/⍳⍴⍵}test;index]←dateCvt¨array[{⍵/⍳⍴⍵}test;index] :Else ⎕←'DStoApl Error: Don''t know what to do with: ',(dt.TableName),'.',colName :End :ElseIf '_b'≡¯2↑colName :If 'System.Boolean'≡colDataType ⍝ If equal to DBNull the Boolean is Inderminate and it is changed to ¯1 test←array[;index]=DBNull.Value array[{⍵/⍳⍴⍵}test;index]←¯1 :End :End :EndFor colNames{ns.⍎⍺,'←⍵'}¨↓⍉array ⍝ Assign each columns to the colNames in the NameSpace ⍎'apl.',(dt.TableName),'←ns' ⍝ Add the new named namespace to the nameless namespace :EndFor ∇ ∇ 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;⎕USING;memStream ⍝ 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 ⍝ Serialize the DataTable to a MemoryStream memStream←⎕NEW System.IO.MemoryStream binaryFormatter.Serialize memStream dt ⍝ Write the MemoryStream to fileName and dispose of the MemoryStream System.IO.File.WriteAllBytes((⊂,fileName),(⊂memStream.ToArray)) memStream.Close ⋄ memStream.Dispose ⋄ memStream←⎕NULL ⍝ Write to fileName and close the FileStream (alternative way to do the same thing) ⍝ 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 ∇ ∇ ds DStoXmlFile fileName;⎕USING ⍝ Saves an Xml representation of the DataSet to a file name ⍝ fileName = fully qualified file name ⍝ ds = DataSet ⍝ Write to a Xml file with the Schema ⎕USING←'System.Data,System.Data.dll' ds.WriteXml(fileName XmlWriteMode.WriteSchema) ∇ ∇ 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;dv;string;⎕USING ⍝ 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←GetColNames 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;newRow;rowNumber ⍝ 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) ∇ ∇ ShowDS ds;dg;dt;index;tc;ti;win;⎕USING ⍝ Show a DataSet in multiple Syncfusion's Grid ⍝ Each Table(s) is shown in a separate Tab ⍝ ds = DataSet to Show ⎕USING←'System.Windows,WPF/PresentationFramework.dll' ⎕USING,←⊂'System.Windows.Controls,WPF/PresentationFramework.dll' ⎕USING,←⊂'Syncfusion.UI.Xaml.Grid,Syncfusion/4.5/Syncfusion.SfGrid.WPF.dll' :Trap 0 ⋄ Anything ⋄ :EndTrap ⍝ To load ⎕USING into memory ⍝ Get a Window win←⎕NEW Window win.(Width Height)←640 480 win.Title←'Show DataSet [ ',(ds.DataSetName),' ]' ⍝ Get a TabControl that will hold the TabItem tc←⎕NEW TabControl ⍝ Iterate to set each Tables in a TabItem :For index :In ⍳ds.Tables.Count dt←ds.Tables[index-1] ti←⎕NEW TabItem ti.Header←dt.TableName ⍝ ← Name of the Tab dg←⎕NEW SfDataGrid dg.AutoGenerateColumns←1 dg.ItemsSource←dt ⍝ ← the DataTable is set here dg.AllowResizingColumns←1 dg.(ColumnSizer←ColumnSizer.Star) dg.AllowEditing←1 ti.Content←dg {}tc.Items.Add(ti) :EndFor ⍝ Set the content of the Window with the TabControl and Show win.Content←tc win.Show ∇ ∇ ShowDT dt;dg;win;⎕USING ⍝ Show a DataTable in a Syncfusion's Grid ⍝ dt = DataTable to Show ⎕USING←'System.Windows,WPF/PresentationFramework.dll' ⎕USING,←⊂'System.Windows.Controls,WPF/PresentationFramework.dll' ⎕USING,←⊂'Syncfusion.UI.Xaml.Grid,Syncfusion/4.5/Syncfusion.SfGrid.WPF.dll' :Trap 0 ⋄ Anything ⋄ :EndTrap ⍝ To load ⎕USING into memory win←⎕NEW Window win.(Width Height)←640 480 win.Title←'Show DataTable [ ',(dt.TableName),' ]' dg←⎕NEW SfDataGrid dg.AutoGenerateColumns←1 dg.ItemsSource←dt ⍝ ← the DataTable is set here dg.AllowResizingColumns←1 dg.(ColumnSizer←ColumnSizer.Star) dg.AllowEditing←1 win.Content←dg win.Show ∇ ∇ ds←XmlFileToDS fileName;⎕USING ⍝ Retrieves a DataSet from an Xml representation made by DStoXmlFile ⍝ fileName = fully qualified file name ⍝ ds = DataSet ⎕USING←'System.Data,System.Data.dll' ds←⎕NEW DataSet {}ds.ReadXml(⊂,fileName) ∇ ∇ 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] ∇ ∇ ns←makeNs ⍝ Build a sample 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 ns.Books.Date_d1←42000 43000 44000 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) ns.Inventory.DateShipped_d3←'2014-12-5' '2013/8/9' '2012 6 5' ∇ :EndNamespace