6 Jan 2021

Use System.Data.SQLite from iLogic rule

If you need to work with an SQLite database, one way is to use System.Data.SQLite
It's not part of the .NET framework install, but available through NuGethttps://www.nuget.org/packages/System.Data.SQLite 
(now there is also a light-weight library providing similar functionality: Microsoft.Data.Sqlite and an article on comparing the two)

This library is also not available on the Design Automation servers, so you would need to provide it yourself.

If you want to use it from an iLogic rule, then apart from referencing it (using "AddReference"), you also have to make sure that it can be located.

I found two ways to do it:

1) Update iLogicAuto.FileOptions.AddinDirectory

As I mentioned in the Use external iLogic Rules blog post, you can modify the file resolution paths used by iLogic. In this case, we could do it like this:

public void RunWithArguments(Document doc, NameValueMap map)
    // Let's say that the first paameter in the command line provides the path to the 
    // app bundle and that's where the relevant files for System.Data.SQLite are stored:
    // System.Data.SQLite.dll & SQLite.Interop.dll
    string bundlePath = map.Item["_1"] as string;
    string contents = System.IO.Path.Combine(bundlePath, @"DaSamplePlugin.bundle\Contents");

    ApplicationAddIn addIn = inventorApplication.ApplicationAddIns.ItemById["{3bdd8d79-2179-4b11-8a5a-257b1c0263ac}"];
    if (!addIn.Activated)
    dynamic iLogicAuto = addIn.Automation;
    iLogicAuto.FileOptions.AddinDirectory = contents;

    // open document, run iLogic rule, etc.

2) Get the app bundle to load those dll's

If we are using any functionality inside our app bundle from System.Data.SQLite then the relevant dll's will get loaded into the Inventor Core Console process and will be available by the time the iLogic rule is run

public void RunWithArguments(Document doc, NameValueMap map)
    // Just use anything from the System.Data.SQLite library
    System.Data.SQLite.CollationEncodingEnum temp = System.Data.SQLite.CollationEncodingEnum.UTF8;

    // open document, run iLogic rule, etc.

The iLogic rule in our document:

AddReference "System.Data.dll"
AddReference "System.Data.SQLite.dll"
AddReference "System.Xml" 
Imports System.Data.SQLite
Imports System.Data

    ' Let's say mydb.db is in the same folder as the Inventor document
    Dim dbPath = System.IO.Path.Combine(ThisDoc.Path, "mydb.db")
    Dim dbConnectionString As String = "Data Source=" + dbPath

    Dim sqliteCon As SQLiteConnection = New SQLiteConnection(dbConnectionString)
    Dim dataAdapter As SQLiteDataAdapter = New SQLiteDataAdapter("SELECT name FROM sqlite_master", sqliteCon)
    Dim ds As DataSet = New DataSet()
    Dim dataRowCol As DataRowCollection = ds.Tables(0).Rows

    Trace.WriteLine("List of tables in the database")
    For Each dr In dataRowCol
Catch ex As Exception
End Try 


Related Article