Lessons Learned

There are a lot of Sqlite packages available on nuget, if you're not careful you might include unnecessary dlls for EntityFramework. For plain Sqlite access via SqliteConnection use the System.Data.SQlite.Core package. Once the package is included you might get an error like this:

      Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E) at System.Data.SQLite.UnsafeNativeMethods.sqlite3_config_none(SQLiteConfigOpsEnum op)

If you do, one cause could be a strange thing where Sqlite is relying on the entry assembly to dynamically load that dll at runtime. So I also added a reference (via nuget) to the same System.Data.SQLite.Core package into the entry project (with Main or entry-point) even though it didn't make use of Sqlite in that assembly and magically it worked after that!

You might get casts errors if you directly cast to an int:

int myVal = (int)dataReader[0];

System.InvalidCastException: Specified cast is not valid
    

Instead use conversion apis to avoid failed explicit casts, ie:

sqliteDataReader.Read();
int val = System.Convert.ToInt32(sqliteDataReader.GetValue(0)).

cmd.CommandText = "INSERT INTO foo (name) values(@someParam);";
cmd.Parameters.AddWithValue("@someParam", myVal)).
  

The code block above worked whereas the question mark approach "INSERT INTO foo (name) values('?')" and Parameters.Add(myVal) did not work for me. I was getting the following error:

Unable to cast object of type 'System.String' to type 'System.Data.SQLite.SQLiteParameter'

Quotes around values won't work

var clause = "WHERE name in ('@param')"

instead leave the quotes out and the prepared statement's parameter will be placed correct:

var clause = "WHERE name in (@param)"

When I added a char parameter it actually saved it as the ascii code (in the case of 'M' it was 77):

cmd.Parameters.AddWithValue("@param1", 'M');

So instead I had to convert it to a string

cmd.Parameters.AddWithValue("@param1", 'M'.ToString())