Using SQLite from PowerShell on Windows 7x64?

Question

I'm having a difficult time trying to load System.Data.SQLite.dll from PowerShell in Windows 7 x64.

# x64
[void][System.Reflection.Assembly]::LoadFrom("C:\projects\PSScripts\lib\System.Data.SQLite.x64.DLL")
# x86
#[void][System.Reflection.Assembly]::LoadFrom("C:\projects\PSScripts\lib\System.Data.SQLite.DLL")

$conn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$conn.ConnectionString = "Data Source=C:\temp\PSData.db"
$conn.Open()
$command = $conn.CreateCommand()
$command.CommandText = "select DATETIME('NOW') as now, 'Bar' as Foo"
$adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void]$adapter.Fill($dataset)

Trying to open the connection with the x64 assembly results in:

Exception calling "Open" with "0" argument(s): "An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)"

Trying to load the x86 assembly results in:

Exception calling "LoadFrom" with "1" argument(s): "Could not load file or assembly 'file:///C:\projects\PSScripts\lib\System.Data.SQLite.DLL' or one of its dependencies. An attempt was made to load a program with an incorrect format."

Any thoughts or ideas?

Funny, I was working on a similar problem yesterday. I did get past the error you see, but still can't open a db. I'm trying to package up what I know for an answer now. — halr9000, Dec 30 '10 at 02:12

halr9000 · Accepted Answer · 2010-12-30T03:56:15.560

Is it possible that your x64 binary is corrupt? I'm able to successfully use add-type on a freshly downloaded copy of system.data.sqlite.dll using the below code, and I can instantiate all of the related objects. I'm also able to open a database without errors, and successfully execute a query. Try this technique (essentially, using Add-Type instead of LoadFrom) with your db and let me know.

Sample code for a SQLite PowerShell module:

function Add-SqliteAssembly {
    # determine bitness (32 vs. 64) of current PowerShell session
    # I'm assuming bitness = system architecture here, which won't work on IA64, but who cares
    switch ( [intptr]::Size ) {
        4   { $binarch = 'x86' }
        8   { $binarch = 'x64' }
    }
    $modPath = $MyInvocation.MyCommand.Module.ModuleBase
    $SQLiteBinName = 'System.Data.SQLite.dll'
    $SQLiteBinPath = "$modPath\$binarch\$SQLiteBinName"
    Add-Type -Path $SQLiteBinPath 
}

To use this module, save it to a file called sqlite.psm1 and place it somewhere in your module path. Then place the two System.Data.SQLite.dll which you have downloaded into sub-folders, each in the proper folder (x86 or x64). Then, in PowerShell, type:

Import-Module sqlite

And then to actually load the assembly:

Add-SqliteAssembly

Now, your initial code (minus the loadfrom stuff) should work.

I haven't tried your module approach yet, but your corrupt binary got me thinking. I believe I was fighting both a corrupt x64 assembly and also a session issue in that once an assembly was loaded/attempted to load, it didn't matter what you did the result was always the same. But if I restarted Powershell ISE/Power Gui Script Editor I could load/consume the x64 assembly without issue. So a new copy of the dll and restarting the editor between changes makes it so I can query SQLite. Thanks for your help. -- Jason — jas, Dec 30 '10 at 18:34

score 3 · Answer 2 · answered Mar 23 '12 at 06:03

You need to make sure that the System.Data.SQLite.dll is correct for both bit (32 or 64) and .Net version. For Win 7 x64 the default installed .Net is 3.5. Powershell will use .Net 3.5 even if you have installed the 4.0 (or higher) clients. You can use .NET 4.0 in Powershell if you jump through other hoops (discussed elsewhere).

Download ADO SQLite package from:
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Look for Precompiled Binaries for 64 bit Windows (.NET Framework 3.5 sp1). Select the appropriate zip (with or without the VC++ runtime). Don't let the 2008 (or 2010) in the file name fool you. It's a reference to the VC++ version used to compile. The project is current. The version as of this writing is 1.0.79.0 dated Jan 28, 2012. Unfortunately the help file does not come with this package. The useful SQLite.Net.chm file is in the separate source package in the docs\ folder.

Once the zip is extracted (no install or registry hacks required), point your powershell script to the dll using:

Add-Type -Path "<drive><path>\System.Data.SQLite.dll"

Example:
Add-Type -Path "C:\sql\sqliteFx35x64\System.Data.SQLite.dll"

The rest of your test code should work

Roman Kuzmin · Answer 3 · 2010-12-29T10:52:45.667

I would try to create the YourApp.exe.config file in the application home directory (powershell.exe.config in the powershell.exe's directory if it is PowerShell, mind that the location is different for x86 and x64):

<?xml version="1.0"?>
<configuration>
 <runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
   <probing privatePath="C:\projects\PSScripts\lib"/>
  </assemblyBinding>
 </runtime>
</configuration>

If it does not help then double check that all the SQLite DLLs are installed properly. See the manual (I do not know much about SQLite ADO .NET provider myself).

Using SQLite from PowerShell on Windows 7x64?

3 Answers3

Linked