Need CLR translation

[stanl]

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) Select Expand


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]

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) Select Expand


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) Select Expand


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.

[stanl]

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) Select Expand


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]

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]

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.

[stanl]

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]

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) Select Expand


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]

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) Select Expand


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)


[stanl]

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]

Good stuff.   Thanks for the post.


-10F here this morning.

Jim

[stanl]

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) Select Expand


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