C# In-Memory Compiling and Sqlite

[JTaylor]

Emboldened by my success with HTML Parsing (which is working extremely well in production) I am attempting a similar thing with SQLite.   Not having much success out of the gate though.  The following C# code compiles fine in VS but tells me that Data does not exist in the namespace Microsoft.   I have tried numerous things with System.Data.Sqlite as well but end up at the same place.    Suggestions?

Jim

Code (winbatch) Select Expand



GoSub Load_Routines

  oSQL = Load_SQL()
  myConn = oSQL.doParsing()
  message("myConn",myConn)

Exit

:LOAD_ROUTINES

#DefineSubRoutine Load_SQL()

  ObjectClrOption ( 'useany', 'System')
  objCSharp = ObjectClrNew('Microsoft.CSharp.CSharpCodeProvider')
  objParams = ObjectClrNew('System.CodeDom.Compiler.CompilerParameters')
  objParams.GenerateInMemory = ObjectType( "VT_BOOL", 1 ) ;TRUE
  objParams.ReferencedAssemblies.Add("System.dll")
  objParams.ReferencedAssemblies.Add("System.Windows.Forms.dll")

;Make sure the cSharp code includes both namepspace and class that can be later used by ObjectCLRNew
  cSrc = $"namespace HSQL {
    using System;
    using System.Text;
    using System.Security.Cryptography;
    using System.Collections.Generic;
    using System.Windows.Forms;
    using System.Threading;
    using System.Reflection;
    using Microsoft.Data.Sqlite;
//or
//  using System.Data.Sqlite;

    public class My_SQL {

  private static void doParsing()
   {
        SqliteConnection myConn;
        SqliteCommand myCmd;

        string cs = @"URI=file::C::\gbat\DOM_SqlITE\test.db";
        var myConn = new SqliteConnection(cs);
        myConn.Open();
        myCmd = new SqliteCommand();
        return myConn;
    }

     }   // END OF CLASS
    }    // END OF NAMESPACE

$"

  objResult = objCSharp.CompileAssemblyFromSource(objParams,cSrc)

  ;Compiler Output
  If objResult.Output.Count > 0 Then
    strOutput = ''
    For x = 0 to objResult.Output.Count-1
       If strOutput == "" Then
         strOutput = objResult.Output.Item(x)
       Else
         strOutput = strOutput:@LF:objResult.Output.Item(x)
       EndIf
    Next
    Pause('Compiler Output',strOutput)
  Endif
 
  ; Compiler Errors
  If objResult.Errors.Count > 0 Then
    strErrors = ''
    ForEach ce In objResult.Errors
      ;Pause("Error", ce.ToString())
      If strErrors == "" Then
        strErrors = ce.ToString()
      Else
        strErrors = strErrors:@LF:ce.ToString()
      EndIf
    Next
    Pause('Compiler Errors',strErrors)
    Exit
  EndIf

  browser = ObjectClrNew( 'HSQL.My_SQL' )
  return browser

#EndSubRoutine


Return




[td]

Don't believe that the "Microsoft.Data.Sqlite" is part of the DotNet Framework. The DotNet Framework is part of the Windows OS and is therefore supported by WinBatch CLR hosting. "Microsoft.Data.Sqlite" is part of DotNet Core which is not part of the OS and must be downloaded and installed deliberately and is not supported by WinBatch. Generally, DotNet Core is installed with MSFT's Visual Studio on development machines. Often when you install a DotNet Core application on your system you get many, many DLLs in the install directory. This is because they don't want to force users to have to install the correct version of DotNet Core on their systems just to use the application they have been working on so diligently.

https://docs.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite?view=msdata-sqlite-6.0.0

You can find Sqlite assemblies that use the DotNet Framework instead of DotNet Core on the Internet or just use COM-based ADO.

[JTaylor]

That is what I figured and why I had also tried using System.Data.Sqlite, which I think should work?  I will keep looking.  Thanks.

Jim

[td]

Your script shows "Microsoft.Data.Sqlite" and not "System.Data.Sqlite". "System.Data.Sqlite" was originally developed by Robert Simpson and I think is still available for download. I have a copy on my system but haven't used it in a while.  I assume it still works with WinBatch and the FCL.

[JTaylor]

Yes.  I had tried both.  The other was commented out in my code.   I have the dll you reference or at least the one from the SQLite site.   I just can't quite put the pieces together to make it work.   I finally got System.Data.SQLite to work in VS by changing the target .NET value to 4 but that hasn't helped in the WB side due to my ignorance in how to get things pointed in the right direction, I am sure.    "using" doesn't work.  I can successfully load the assembly but unsure what to do from there to gain access to the classes and methods from that point and my hours of reading and trying various things haven't gotten me vary far.

Jim

[td]

This is an old script but it still works and doesn't require any C#. The file system paths are correct for my systems. Obviously, they need to be corrected for the system running the script.

Code (winbatch) Select Expand

strFile = "D:\Reinstalls\SQLite\sqlite-netFx20-binary-bundle-Win32-2005-1.0.84.0\test.db"
if FileExist(strFile) then FileDelete(strFile)

ObjectClrOption("appbase", "D:\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("useany", "System.Data")
objDataState = ObjectClrNew( "System.Data.ConnectionState")
objSqlLiteCon.Open()
if objSqlLiteCon.State == objDataState.Open 

   ; Create a table.
   objSqlLiteCmd = objectClrNew("System.Data.SQLite.SQLiteCommand")
   objSqlLiteCmd.Connection  = objSqlLiteCon
   objSqlLiteCmd.CommandText = "CREATE TABLE employee_data (emp_id INTEGER, emp_name TEXT);"
   objSqlLiteCmd.ExecuteNonQuery()
   objSqlLiteCmd.CommandText = "INSERT into employee_data (emp_id, emp_name) VALUES (001,""Bob Abergast"");"
   objSqlLiteCmd.ExecuteNonQuery()
   objSqlLiteCmd.CommandText = "INSERT into employee_data (emp_id, emp_name) VALUES (002,""Marquess Doe"");"
   objSqlLiteCmd.ExecuteNonQuery()
   objSqlLiteCmd.CommandText = "Select * from employee_data;"
   objReader = objSqlLiteCmd.ExecuteReader()
   
   ; Check the newly created table.
   while objReader.Read
      Message(objReader.GetValue(0), objReader.GetValue(1))
   endwhile
   
   ; Cleanup
   objReader.Close()
   objSqlLiteCon.Close()
   objReader     = 0
   objSqlLiteCmd = 0
endif
objDataState  = 0
objSqlLiteCon = 0


[td]

Out of curiosity, I modified the above script to use "System.Data.SQLite" for the 4.6 version of the .Net Framework and it still works.

[JTaylor]

Thank you!!!    I will see what I can do with this.   

Jim

[JTaylor]

Don't know if anyone else will experience this problem but I had to put SQLite.Interop.dll in the same directory as my script.   appbase didn't seem to apply to this file.

Jim

[td]

I just pointed the AppBase to the directory where the entire zip is extracted to. The extraction directory has manifests and configuration files as well as DLLs.  This approach works fine without the need to perform any additional file copying.

[JTaylor]

Not sure what is different but, assuming I understand, I had to move the file out of the directory where the other files from the zip resided and put it in the same directory as my script.   Obviously, may just be me and only mentioned it in case someone else got the can't find message.  Thanks again.  That was a huge help.   I have fought with that before off and on over the years and always gave up as it wasn't a pressing issue.

jim

[td]

I realize this is not a big issue since it is easily worked around. But the CLR will only search for assemblies in the GAC and the AppBase specified folder. WinBatch knows nothing about the "SQLite.Interop.dll" DLL and will not try to load it on its own. I do not have SQLite assemblies in the GAC on my system. It is my understanding that the  "System.Data.SQLite" "managed" assembly searches for and loads the "SQLite.Interop.dll" "native" DLL. This DLL wraps the "native" implementation of SQLite and makes the functionality available to the "managed" assembly.

The only causes that I can think of that would cause the assembly to not find the "native" DLL are that either your antimalware software is interfering with the search or the name of your AppBase folder is somehow preventing the assembly from finding the correct platform version. I checked and both of these causes have been reported on various discussion forums.  I have no idea if either cause applies to your situation and of course, there are all the causes that I didn't think of to waste some otherwise free time considering as well.

[JTaylor]

Appreciate it.   I saw numerous post on this issue as well.   Again, it is working fine and I just wanted to let others know what worked for me in case they encounter the same problem.  Thanks again.  So far it is working VERY well for my project. 

Jim