Author Topic: Need CLR translation  (Read 5579 times)

stanl

  • Pundit
  • *****
  • Posts: 936
Need CLR translation
« on: January 05, 2014, 08:26:21 am »
How would I translate (probably 'appbase' or something Tony previously wrote, but I'm still a little confused)

[System.Reflection.Assembly]::LoadFrom("C:\Powershell\System.Data.SQLite.dll")

in WB CLR code, so that I might continue with a script like

Code: Winbatch
ObjectClrOption("use", "System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
cn = ObjectClrNew("System.Data.SQLite.SQLiteConnection")
cn.ConnectionString = "Data Source=E:\RA\SQLITE\SMS.SQLITE"
cn.Open()

cm = ObjectClrNew("System.Data.SQLite.SQLiteCommand")
cSQL = "SELECT SMS, Carrier FROM Carriers"
cm.Connection = cn
cm.CommandText = cSQL
dr = cm.ExecuteReader()

 

td

  • Tech Support
  • *****
  • Posts: 3017
    • WinBatch
Re: Need CLR translation
« Reply #1 on: January 05, 2014, 10:09:13 am »
Two simple rules to remember. The 'appbase' option sets the directory that the CLR will use to look for 'non-GAC' assemblies.  The CLR does not use path environment variable searches like traditional Windows applications and DLLs often do. If you want to use 'non-GAC' assembles, you must either place those assemblies in the directory containing the WIL interpreter DLL or you must use the 'appbase' option. So you can do the following
Code: Winbatch
ObjectClrOption("AppBase", "C:\Powershell")
 

The second rule is that for what I hope are obvious reasons WinBath does not automagically load assemblies into the AppDomain of your script. The only exception is the 'mscorlib' assembly which the CLR automatically loads when it is loaded into a process.   You use the 'use' option to load assemblies into your WinBatch script AppDomain so you need the following before you can make use of your assembly's classes.
Code: Winbatch
ObjectClrOption("use","System.Data.SQLite")
 

Together these statements are equivalent to your Powershell  "[System.Reflection.Assembly]::LoadFrom("C:\Powershell\System.Data.SQLite.dll")" line.

The SQLite assembly works well in WinBatch.  In fact, we use it as part of the CLR subsystem regression test.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

  • Pundit
  • *****
  • Posts: 936
Re: Need CLR translation
« Reply #2 on: January 05, 2014, 11:35:29 am »
Thanks Tony. Now just need to read the data. Code is below and I attached the SQLite db (just have to change the appbase and db location to test). I get 'unknown name' error on While dr.read()

Code: Winbatch
ObjectClrOption("AppBase", "C:\Powershell")
ObjectClrOption("use","System.Data.SQLite")
cn = ObjectClrNew("System.Data.SQLite.SQLiteConnection")
cn.ConnectionString = "Data Source=E:\RA\SQLITE\SMS.SQLITE"
cn.Open()

cm = ObjectClrNew("System.Data.SQLite.SQLiteCommand")
cSQL = "SELECT SMS, Carrier FROM Carriers"
cm.Connection = cn
cm.CommandText = cSQL
dr = ObjectClrNew("System.Data.SQLite.SQLiteDataReader",cm.ExecuteReader())

;While dr.read()
;   data=data:dr.getstring(0):@TAB
;   data=data:dr.getstring(1):@CRLF
;EndWhile

dr=0
cn.Close()

Exit
 

stanl

  • Pundit
  • *****
  • Posts: 936
Re: Need CLR translation
« Reply #3 on: January 05, 2014, 02:07:23 pm »
as a P.S.

there was
data="" ;missing in earlier post
;While dr.read()
;   data=data:dr.getstring(0):@TAB
;   data=data:dr.getstring(1):@CRLF
;EndWhile

and I tried the script with both the 1.07 and 1.09 (.NET 3.5) versions of the .dll

All the C# and VB code I've seen use the .read() method after creating the reader.

....IFICantBYTE

  • Full Member
  • ***
  • Posts: 120
Re: Need CLR translation
« Reply #4 on: January 05, 2014, 05:07:35 pm »
Hi Stan,
I may be wrong, (I have only limited experience with CLR stuff), but it could possibly be CaSe sensitive issues....
Try changing your dr.read() to dr.Read() and the dr.getstring(x) to dr.GetString(x) and see if that error goes away.
 
Regards,
....IFICantBYTE

Nothing sucks more than that moment during an argument when you realize you're wrong. :)

stanl

  • Pundit
  • *****
  • Posts: 936
Re: Need CLR translation
« Reply #5 on: January 06, 2014, 02:44:17 am »
Hi Stan,
I may be wrong, (I have only limited experience with CLR stuff), but it could possibly be CaSe sensitive issues....
Try changing your dr.read() to dr.Read() and the dr.getstring(x) to dr.GetString(x) and see if that error goes away.

Excellent suggestion, but the error only changes from Unknown Name to a general COM/OLE error - extended error has Exception: Non-Static Method requires a Target.
 
I tried closing the command object prior to the read() based on issues another user had on StackOverflow but the error persists.

stanl

  • Pundit
  • *****
  • Posts: 936
Re: Need CLR translation
« Reply #6 on: January 06, 2014, 06:23:09 am »
Now for Plan-B, combined both the SQLite dll and .NET System to create a DataSet and DbAdapter. Still having issues getting at the data.

Code: Winbatch
ObjectClrOption("AppBase", "C:\Powershell\bin")
ObjectClrOption("use","System.Data.SQLite")
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
ds = ObjectClrNew("System.Data.DataSet")
dt = ObjectClrNew("System.Data.DataTable")
db = ObjectClrNew("System.Data.SQLite.SQLiteDataAdapter","SELECT SMS, Carrier FROM Carriers","Data Source=C:\RA\SQLITE\SMS.SQLITE")

db.Fill(ds)

Message("",ObjectTypeGet(db))
                         
;dt = ds.Tables[0]  ; AAARRRGGGHHH

db.Dispose()
Exit

 

td

  • Tech Support
  • *****
  • Posts: 3017
    • WinBatch
Re: Need CLR translation
« Reply #7 on: January 06, 2014, 07:15:47 am »
There are many ways to accomplish the task and MSFT's surprisingly good FCL documentation is the a good place to start an exploration of those possibilities, since the SQLite classes are mostly derived from the FCL data classes. Being generally lazy on a Monday morning, I simply plugged your query and db into a existing script and got the expected result.
Code: Winbatch
strFile = DirScript():"SMS.SQLITE"
lRecords = ""
ObjectClrOption("appbase", "C:\Reinstalls\SQLite\sqlite-netFx20-binary-bundle-Win32-2005-1.0.84.0")
ObjectClrOption("use", "System.Data.SQLite")

objSqlLiteCon = objectClrNew("System.Data.SQLite.SQLiteConnection", "Data Source=%strFile%;Pooling=true")
ObjectClrOption("use", "System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
objDataState = ObjectClrNew( "System.Data.ConnectionState")
objSqlLiteCon.Open()
if objSqlLiteCon.State == objDataState.Open  

   ; Create a query.
   objSqlLiteCmd = objectClrNew("System.Data.SQLite.SQLiteCommand")
   objSqlLiteCmd.Connection  = objSqlLiteCon
   objSqlLiteCmd.CommandText = "SELECT SMS, Carrier FROM Carriers"
   objReader = objSqlLiteCmd.ExecuteReader()
   
   ; Create a data list.
   while objReader.Read
      lRecords = lRecords:objReader.GetValue(0):@tab:objReader.GetValue(1):@CRLF
   endwhile
   
   ; Cleanup
   objReader.Close()
   objSqlLiteCon.Close()
   objReader     = 0
   objSqlLiteCmd = 0
endif
objDataState  = 0
objSqlLiteCon = 0

Message("Result", lRecords)
 
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

  • Pundit
  • *****
  • Posts: 936
Re: Need CLR translation
« Reply #8 on: January 06, 2014, 08:10:12 am »
Got it. Just for my own records, creating a System.Data.SQLite.SQLiteDataReader object is not required;  .read or .read() will both work; in this example GetValue/GetString are equivalent.

There is also a devart SQLite build which has a FetchSize property. Have you played with that build?

Thanks: enjoy your lazy day.... it was 61 degrees when I drove to work at 7am, by 7pm it will be in the 20's then slide to 10 overnight.

JTaylor

  • Pundit
  • *****
  • Posts: 1014
    • Data & Stuff Inc.
Re: Need CLR translation
« Reply #9 on: January 06, 2014, 08:14:59 am »
Good stuff.   Thanks for the post.


-10F here this morning.

Jim

stanl

  • Pundit
  • *****
  • Posts: 936
Re: Need CLR translation
« Reply #10 on: January 06, 2014, 10:00:28 am »
It was -37 when I called my brother (Watertown, NY - new home of the 10th Mountain)

FYI:  I got around to pulling up some old code Deana helped me with when I first posted about MSAccess and the CLR - adjusted my dbadapter code by inserting her code to output to reportview:

Code: Winbatch
isdata=1
ObjectClrOption("AppBase", "C:\Powershell\bin")
ObjectClrOption("use","System.Data.SQLite")
cn = ObjectClrNew("System.Data.SQLite.SQLiteConnection")
cn.ConnectionString = "Data Source=C:\RA\SQLITE\SMS.SQLITE"

ObjectClrOption("use", "System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oState = ObjectClrNew( "System.Data.ConnectionState")
cn.Open()
If cn.State == oState.Open  

   cm = ObjectClrNew("System.Data.SQLite.SQLiteCommand")
   cSQL = "SELECT SMS, Carrier FROM Carriers"
   cm.Connection = cn
   cm.CommandText = cSQL
   db = ObjectClrNew("System.Data.SQLite.SQLiteDataAdapter")
   dt = ObjectClrNew( 'System.Data.DataTable')  

   db.SelectCommand = cm
   rowcount = db.Fill(dt)
   colcount = dt.Columns.Count

   If rowcount==0
      isData=0
      goto end
   Endif

   arrData = ArrDimension(rowcount+1,colcount)

   oCols = dt.Columns
   x=0
   ForEach col in oCols
      arrData[0,x]=col.ColumnName
      x=x+1
   Next

   ;Read Data into an Array
   For _row = 1 To dt.Rows.Count-1
      objRow = dt.Rows.Item(_row)
      For _col = 0 To dt.Columns.Count-1
        objColumn = dt.Columns.Item(_col)
        arrData[_row,_col] = objRow.Item(objColumn)
      Next
   Next

   goto showit        


Endif

:end
cn.Close()
Exit



:showit
; Display in Reportview
MyDialogFormat=`WWWDLGED,6.2`

MyDialogCaption=`%cSQL%`
MyDialogX=090
MyDialogY=090
MyDialogWidth=374
MyDialogHeight=147
MyDialogNumControls=003
MyDialogProcedure=`DEFAULT`
MyDialogFont=`DEFAULT`
MyDialogTextColor=`DEFAULT`
MyDialogBackground=`DEFAULT,DEFAULT`
MyDialogConfig=0

MyDialog001=`101,127,036,012,PUSHBUTTON,"PushButton_OK",DEFAULT,"OK",1,10,32,DEFAULT,DEFAULT,DEFAULT`
MyDialog002=`237,127,036,012,PUSHBUTTON,"PushButton_Cancel",DEFAULT,"Cancel",0,20,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
MyDialog003=`001,005,366,110,REPORTVIEW,"ReportView_1",arrData,DEFAULT,DEFAULT,30,7340032,DEFAULT,DEFAULT,"192|192|192"`

ButtonPushed=Dialog("MyDialog")

Goto end