Question on In-Memory DataTable

[spl]

Playing around with CLR DataTables to replace older code using fabricated recordsets. Easy enough to create, but am puzzled on how to enumerate. NOTE: this is in-memory, not using the adaptor which does work if building from a query

Code Select Expand

ObjectClrOption("useany","System.Data")
dt = ObjectClrNew("System.Data.DataTable")

dt.Columns.Add("Name")
dt.Columns.Add("Age")
dt.Columns.Add("Location")

dt.Rows.Add("Brian", "23", "UK")
dt.Rows.Add("Sam", "32", "Canada")
dt.Rows.Add("Eric", "25", "USA")

rs = dt.Rows
Message("Rows",rs.Count)
ForEach row in rs
   Display(2,"Row Data","??????") ; what will work
                                  ; tried row[0], row[0][0], row.Name etc...
                                  ; what will work instead of "??????"
Next
dt=0
Exit


[JTaylor]

row.Item("Name")

[spl]

row.Item("Name")

Glad someone was listening. Yeah, often forget how WB uses Item. syntax.

[spl]

Thanks Jim: This is more generic

Code Select Expand

ObjectClrOption("useany","System.Data")
dt = ObjectClrNew("System.Data.DataTable")

dt.Columns.Add("Name")
dt.Columns.Add("Age")
dt.Columns.Add("Location")

dt.Rows.Add("Brian", "23", "UK")
dt.Rows.Add("Sam", "32", "Canada")
dt.Rows.Add("Eric", "25", "USA")

rs = dt.Rows
cs = dt.Columns
rc =1
ForEach row in rs
   data = ""
   ForEach col in cs
      data = data:row.Item(col):" "
   Next
   Display(2,"Row:":rc,data)
   rc+=1
Next
dt=0
Exit


[spl]

Now I want to step this thread up a notch - instantiate column types and other properties. Below I set out PS<>WB for the datatable with a couple of extra columns. Goal would be to 'type' the columns in WB similar to PS. I checked out ObjectClrType(), but is seemed to require a pre-defined value. Was wondering if there was a way to map  WB vars to CLR System types?? Another route would be to breakdown each column, but would like to just keep the 2nd parameter for type interpreted for WB code. Hope this makes sense.

Code Select Expand

;Powershell datatable code with data types
$tabName = "Test"
$table = New-Object System.Data.DataTable "$tabName"

[void]$table.Columns.Add("Name",[string])
[void]$table.Columns.Add("Salary",[Int])
[void]$table.Columns.Add("DOB",[DateTime])
[void]$table.Columns.Add("Location",[string])
[void]$table.Columns.Add("Salaried",[bool])

[void]$table.Rows.Add("Brian", "55000","04/17/1999" ,"UK",1)
[void]$table.Rows.Add("Sam", "107000","11/02/1984", "Canada",1)
[void]$table.Rows.Add("Eric", "25000","03/05/2007", "USA",0)

;WB [but how to create data type as second parameter????
ObjectClrOption("useany","System.Data")
dt = ObjectClrNew("System.Data.DataTable")

dt.Columns.Add("Name",(data type))
dt.Columns.Add("Salary",(data type))
dt.Columns.Add("DOB",(data type))
dt.Columns.Add("Location",(data type))
dt.Columns.Add("Salaried",(data type))

dt.Rows.Add("Brian", "55000", "04/17/1999", "UK",1)
dt.Rows.Add("Sam", "107000", "11/02/1984", "Canada",1)
dt.Rows.Add("Eric", "25000", "03/05/2007", "USA",0)



[spl]

Thank God for archives. Seemed I had already figured it out in 2019 (with discussions on parsing VB .Net data into SQLite). Anyway, this works for me and interesting how DateTime and Boolean is returned.

Code Select Expand

;WB 2024B Simple typed .Net DataTable
;Stan Littlefield 10/01/2024
;===============================================================================================================
gosub udfs
IntControl(73,1,0,0,0)
ObjectClrOption("useany","System.Data")
dt = ObjectClrNew("System.Data.DataTable")
oType = ObjectClrNew('System.Type')
ostr = oType.GetType("System.String")
oint = oType.GetType("System.Int32")
odate = oType.GetType("System.DateTime")
obool = oType.GetType("System.Boolean")

oColumn = ObjectClrNew("System.Data.DataColumn","Name", ostr)    
dt.Columns.Add(oColumn)

oColumn = ObjectClrNew("System.Data.DataColumn","Salary", oint)    
dt.Columns.Add(oColumn)

oColumn = ObjectClrNew("System.Data.DataColumn","DOB", odate)    
dt.Columns.Add(oColumn)

oColumn = ObjectClrNew("System.Data.DataColumn","Location", ostr)    
dt.Columns.Add(oColumn)

oColumn = ObjectClrNew("System.Data.DataColumn","Hourly", obool)    
dt.Columns.Add(oColumn)

dt.Rows.Add("Brian", "55000", "04/17/1999","UK",0)
dt.Rows.Add("Sam", "107000","11/09/2006", "Canada",0)
dt.Rows.Add("Eric", "25000","03/05/2007", "USA",1)

rs = dt.Rows
cs = dt.Columns
rc =1
ForEach row in rs
   data = ""
   ForEach col in cs
      data = data:row.Item(col):@TAB
   Next
   Display(2,"Row:":rc,data)
   rc+=1
Next
dt=0
Exit

:WBERRORHANDLER
dt=0
geterror()
Message("Error Encountered",errmsg)
Exit
;===============================================================================================================

:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine

Return
;===============================================================================================================


[spl]

OK. So another chapter, if anyone cares. Creating a Datatable in-memory with typed columns can present issues with null values. The script attached below allows you to play around, again if anyone cares. Reading up with StackOverflow and others, it seems that a System.Datetime type should not allow null values, but I have already determined that placing $null in a column for a row insert would work fine as all columns in a new DataTable supposedly default to null. Anyway, running with WB and coding to explicitly create columns to accept null, I found that with WB code it makes sense to create a column as System.Object rather than System.DateTime in order to avoid error. NOTE: this is for in-memory, as when importing from an external source there are functions/coding procedures you can implement for correct importing. Code below can be played with

Code Select Expand

;WB 2024B Simple typed .Net DataTable
;Stan Littlefield 10/02/2024
;Updated to show issues with Null date
;===============================================================================================================
gosub udfs
IntControl(73,1,0,0,0)
ObjectClrOption("useany","System.Data")
dt = ObjectClrNew("System.Data.DataTable")

bTrue = ObjectType( "BOOL", -1 )
bFalse = ObjectType( "BOOL", 0 )
oType = ObjectClrNew('System.Type')
ostr = oType.GetType("System.String")
oint = oType.GetType("System.Int32")
;Comment/Uncomment to display error
odate = oType.GetType("System.Datetime")
;Comment/Uncomment to run clean
;odate = oType.GetType("System.Object")
obool = oType.GetType("System.Boolean")

oColumn = ObjectClrNew("System.Data.DataColumn","Name", ostr)
oColumn.AllowDBNull = bTrue     
dt.Columns.Add(oColumn)

oColumn = ObjectClrNew("System.Data.DataColumn","Salary", oint) 
oColumn.AllowDBNull = bTrue     
dt.Columns.Add(oColumn)

oColumn = ObjectClrNew("System.Data.DataColumn","DOB", odate)   
oColumn.AllowDBNull = bTrue 
dt.Columns.Add(oColumn)

oColumn = ObjectClrNew("System.Data.DataColumn","Location", ostr)
oColumn.AllowDBNull = bTrue     
dt.Columns.Add(oColumn)

oColumn = ObjectClrNew("System.Data.DataColumn","Hourly", obool)     
dt.Columns.Add(oColumn)

dt.Rows.Add("Brian", "55000", "04/17/1999","UK",@FALSE)
dt.Rows.Add("Sam", "107000","2009-03-15 09:11:00", "Canada",0)
;uncomment to display datetime regardless of type caste
;dt.Rows.Add("Eric","25000","03/05/2007", "USA",@TRUE)
;uncomment to display error if Datetime type 
dt.Rows.Add("Eric","25000","" , "USA",@TRUE)

rs = dt.Rows
cs = dt.Columns
rc =1
ForEach row in rs
   data = ""
   ForEach col in cs
      data = data:row.Item(col):@TAB
   Next
   Display(2,"Row:":rc,data)
   rc+=1
Next
dt=0
Exit

:WBERRORHANDLER
dt=0
geterror()
Message("Error Encountered",errmsg)
Exit
;===============================================================================================================

:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine

Return
;===============================================================================================================