$PSVersionTable<\/code> and check the CLRVersion, it should look like below:<\/p>\n\n\n\n![\"PowerShell](\"https:\/\/ziviz.us\/WP\/wp-content\/uploads\/2025\/05\/Screenshot-2025-05-14-205124.png\")
<\/figure>\n<\/div>\n\n\nPer above, I need the SQLite download for .Net 4.0.<\/p>\n\n\n\n
If you download the wrong architecture, you will likely run into this error:<\/p>\n\n\n\n![\"PowerShell](\"https:\/\/ziviz.us\/WP\/wp-content\/uploads\/2025\/05\/Screenshot-2025-05-14-210514-1024x173.png\")
An attempt was made to load a program with an incorrect format.<\/figcaption><\/figure>\n\n\n\nIf you download SQLite for a version of .Net you don’t have and can’t run, you will likely get this one:<\/p>\n\n\n
\n![\"PowerShell](\"https:\/\/ziviz.us\/WP\/wp-content\/uploads\/2025\/05\/Screenshot-2025-05-14-210901-1024x98.png\")
Operation is not supported. (Exception from HRESULT: 0x80131515)<\/figcaption><\/figure>\n<\/div>\n\n\nOnce you have your version figured out, download the matching SQLite binaries from https:\/\/system.data.sqlite.org\/<\/a><\/p>\n\n\n\nAs of today, for me, that is the sqlite-netFx40-binary-bundle-x64-2010-1.0.119.0.zip<\/code><\/p>\n\n\n\n![\"Image](\"https:\/\/ziviz.us\/WP\/wp-content\/uploads\/2025\/05\/Screenshot-2025-05-14-205626.png\")
<\/figure>\n<\/div>\n\n\nNext, and very important, you must unblock the zip file. You can do this by right-clicking the zip file, and selecting “Unblock” and then clicking “Apply”.<\/p>\n\n\n
\n![\"Showing](\"https:\/\/ziviz.us\/WP\/wp-content\/uploads\/2025\/05\/Screenshot-2025-05-14-205740.png\")
<\/figure>\n<\/div>\n\n\nIf you do not do this, when you unzip the files, all the decompressed files will also be blocked. If you try and then hook into the SQLite dll from PowerShell you will get the following error:<\/p>\n\n\n
\n![\"PowerShell](\"https:\/\/ziviz.us\/WP\/wp-content\/uploads\/2025\/05\/Screenshot-2025-05-14-210100-1024x101.png\")
Operation is not supported. (Exception from HRESULT: 0x80131515)<\/figcaption><\/figure>\n<\/div>\n\n\nStep 2: Adding and Using SQLite<\/h2>\n\n\n\n
Once you have the version figured out, files unblocked. Actually using SQLite is really easy.<\/p>\n\n\n\n
First you must import the dll with the Add-Type<\/code> command. You can just point it at the path to the DLL file. This is the line that will fail if you chose the wrong DLL or forgot to unblock the files.<\/p>\n\n\n\nAdd-Type -Path \"C:\\sqlite-netFx40-binary-bundle-x64-2010-1.0.119.0\\System.Data.SQLite.dll\"<\/code><\/pre>\n\n\n\nThen you can create a connection string defining your database’s file path and connect to it.<\/p>\n\n\n\n
$connectionString = \"Data Source=C:\\sqlite-netFx40-binary-bundle-x64-2010-1.0.119.0\\MyTest.db;Version=3;\"\n\n$connection = New-Object System.Data.SQLite.SQLiteConnection($connectionString)\n$connection.Open() | Out-Null<\/code><\/pre>\n\n\n\nIf you get this error<\/p>\n\n\n\n
\nException calling “Open” with “0” argument(s): “unable to open database file”<\/p>\n<\/blockquote>\n\n\n\n
Then you likely do not have permissions to the file. SQLite will attempt to create a new db file if one does not exist. You may want to check if the DB has been initiated before using in further code.<\/p>\n\n\n\n
From here, you should be good to go, but here are some rapid-fire scenarios to get you started.<\/p>\n\n\n\n
Create a table<\/strong><\/p>\n\n\n\n$Query = \"CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, creationtime DATETIME DEFAULT CURRENT_TIMESTAMP);\"\n\n$command = New-Object System.Data.SQLite.SQLiteCommand($Query, $connection)\n$command.ExecuteNonQuery() | Out-Null\n$command.Dispose() | Out-Null<\/code><\/pre>\n\n\n\nRemember to dispose when you are done.<\/p>\n\n\n\n
Also, lets make an index<\/p>\n\n\n\n
$Query = \"CREATE INDEX name_idx ON mytable (name);\"\n$command = New-Object System.Data.SQLite.SQLiteCommand($Query, $connection)\n$command.ExecuteNonQuery() | Out-Null\n$command.Dispose() | Out-Null<\/code><\/pre>\n\n\n\nInsert into a table<\/strong><\/p>\n\n\n\n$Query = \"INSERT INTO mytable (name) VALUES (@name);\"\n$Name = \"John.Doe\"\n\n$command = New-Object System.Data.SQLite.SQLiteCommand($Query, $connection)\n$command.Parameters.AddWithValue(\"@name\", $Name) | Out-Null\n$command.ExecuteNonQuery() | Out-Null\n$command.Dispose() | Out-Null<\/code><\/pre>\n\n\n\nNote the use of Parameters. This helps protect against SQL injection attacks. You should use them whenever you are adding variable data into your query. <\/p>\n\n\n\n
Query the table<\/strong><\/p>\n\n\n\n$Query = \"SELECT * FROM mytable WHERE name = @name;\"\n$Name = \"John.Doe\"\n\n$command = New-Object System.Data.SQLite.SQLiteCommand($Query, $connection)\n$command.Parameters.AddWithValue(\"@name\", $Name) | Out-Null\n$reader = $command.ExecuteReader()\n\nwhile ($reader.Read()) {\n Write-Host \"\"\n for ($i = 0; $i -lt $reader.FieldCount; $i++) {\n Write-Host \"$($reader.GetName($I)) = $($reader.GetValue($I))\"\n }\n}\n$reader.Close() | Out-Null\n$reader.Dispose() | Out-Null\n$command.Dispose() | Out-Null<\/code><\/pre>\n\n\n\nThese commands have been silent so far, but this time we are getting data out of the database. This code should return:<\/p>\n\n\n\n
\nid = 1
name = John.Doe
creationtime = <somedate><\/p>\n<\/blockquote>\n\n\n\n
Note that we are disposing the reader as well as the command this time. Both are disposable, so it’s good practice to dispose them when done using them.<\/p>\n\n\n\n
We are also using parameters again, since the name we are putting into our query is variable.<\/p>\n\n\n\n
Finally, at the end of your script<\/strong><\/p>\n\n\n\nSurprise, you should dispose your connection to the database file itself<\/p>\n\n\n\n
$connection.Close()\n$connection.Dispose()<\/code><\/pre>\n\n\n\nHopefully this is useful to someone. If you are looking for a more complete example, I made a simple generic caching module using this info. Check it out on Github<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"I wanted to use SQLite with PowerShell, but without needing to rely on another module. Just SQLite… and PowerShell. So here is how to do that… Step 1: Download and Prepare SQLite SQLite is available for the .Net framework, which PowerShell rides on. Great! But you need to make sure that the SQLite binaries you … Continue reading “PowerShell and SQLite”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-220","post","type-post","status-publish","format-standard","hentry","category-powershell"],"_links":{"self":[{"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/posts\/220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/comments?post=220"}],"version-history":[{"count":3,"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/posts\/220\/revisions"}],"predecessor-version":[{"id":230,"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/posts\/220\/revisions\/230"}],"wp:attachment":[{"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/media?parent=220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/categories?post=220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ziviz.net\/WP\/wp-json\/wp\/v2\/tags?post=220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}