How can I query an SQLite database using embedded C# code within PowerShell?
Thank you for sharing your solution, which is a lightweight way of using SQLite from PowerShell without needing to install modules or libraries.
Additionally, it also works on ARM64 machines, whereas NuGet packages such as Microsoft.Data.Sqlite and System.Data.SQLite currently don't.
I've taken the liberty of adapting your code in the following ways:
Made the code cross-edition (runs both in Windows PowerShell and PowerShell (Core) 7+) and cross-platform; note: tested on Windows and macOS - not sure about Linux.
Made the API more PowerShell-friendly (returning data rather than passing through result codes and requiring by-ref parameters; use of exceptions)
Unicode support (SQLite uses UTF-8 internally, which Marshal.PtrToStringAnsi() misinterprets).
Improved data-type support:
DataTable instance that is returned for query results now uses object columns so as to support all .NET types that correspond to the SQLite data types (if you use just $dataTableInstance.Columns.Add(<string>), you'll get string columns).INTEGER SQLite type is mapped onto System.Int64 ([long]) to ensure that no overflow occurs.Definition of static helper class [SqliteHelper] via Add-Type and ad hoc-compiled C# code that makes P/Invoke calls to the native SQLite library; incurs a one-time compilation performance penalty per session:
# Compile helper type [SqliteHelper], which is a thin layer on top of the C/C++
# SQLite API.
# Gratefully adapted from https://stackoverflow.com/a/76488520/45375
# Determine the platform-appropriate name of the native SQLite library.
# Note: Tested on Windows and macOS.
$libName = ('sqlite3', 'winsqlite3.dll')[$env:OS -eq 'Windows_NT']
# Compile the code.
# NOTE:
# * Re -ReferencedAssemblies:
# * System.Data.Common, System.Collections are needed for PS Core, System.Xml is needed for WinPS.
# * For the sake of WinPS compatibility:
# * The code below uses (a) legacy properyt-definition syntax and (b) legacy dictionary initializer syntax.
# * The *16() variants of the SQLite functions are used so that .NET (UTF-16LE) strings can be used / converted via
# Marshal.PtrToStringUni().
# Behind the scenes, SQLite still translates to and from UTF-8, but not having to deal with that on the .NET
# side makes things easier, given that only .NET (Core) suports Marshal.PtrToStringUTF8()
Add-Type -ReferencedAssemblies System.Collections, System.Data, System.Data.Common, System.Xml -TypeDefinition @"
using System;
using System.Data;
using System.Collections.Generic;
using System.Runtime.InteropServices;
public static class SqliteHelper {
[DllImport("$libName", CharSet=CharSet.Unicode, EntryPoint="sqlite3_open16")] private static extern int open(string filename, out IntPtr db);
[DllImport("$libName", EntryPoint="sqlite3_extended_result_codes")] private static extern int result_codes(IntPtr db, int onOrOff);
[DllImport("$libName", EntryPoint="sqlite3_close_v2")] private static extern int close(IntPtr db);
[DllImport("$libName", CharSet=CharSet.Unicode, EntryPoint="sqlite3_prepare16")] private static extern int prepare(IntPtr db, string query, int len, out IntPtr stmt, IntPtr dummy);
[DllImport("$libName", EntryPoint="sqlite3_step")] private static extern int step(IntPtr stmt);
[DllImport("$libName", EntryPoint="sqlite3_column_count")] private static extern int column_count( IntPtr stmt);
[DllImport("$libName", EntryPoint="sqlite3_column_name16")] private static extern IntPtr column_name( IntPtr stmt, int col);
[DllImport("$libName", EntryPoint="sqlite3_column_type")] private static extern int column_type( IntPtr stmt, int col);
[DllImport("$libName", EntryPoint="sqlite3_column_double")] private static extern Double column_double(IntPtr stmt, int col);
[DllImport("$libName", EntryPoint="sqlite3_column_int")] private static extern int column_int( IntPtr stmt, int col);
[DllImport("$libName", EntryPoint="sqlite3_column_int64")] private static extern Int64 column_int64( IntPtr stmt, int col);
[DllImport("$libName", EntryPoint="sqlite3_column_text16")] private static extern IntPtr column_text( IntPtr stmt, int col);
[DllImport("$libName", EntryPoint="sqlite3_column_blob")] private static extern IntPtr column_blob( IntPtr stmt, int col);
[DllImport("$libName", EntryPoint="sqlite3_column_bytes")] private static extern int column_bytes( IntPtr stmt, int col);
[DllImport("$libName", EntryPoint="sqlite3_finalize")] private static extern int finalize(IntPtr stmt);
// Important result codes.
private const int SQLITE_OK = 0;
private const int SQLITE_ROW = 100; // step() indicates that at least 1 more row exists.
private const int SQLITE_DONE = 101; // step() indicates that therea are no (more) rows.
// Data type IDs
private const int SQLITE_INTEGER = 1;
private const int SQLITE_FLOAT = 2;
private const int SQLITE_TEXT = 3;
private const int SQLITE_BLOB = 4;
private const int SQLITE_NULL = 5;
// A helper exception to report SQLite result codes that are errors.
public class SqliteException : Exception {
private int _nativeErrorCode;
public int NativeErrorCode { get { return _nativeErrorCode; } set { _nativeErrorCode = value; } }
public SqliteException(int code) : this(String.Format("SQLite API call failed with result code {0}.", code), code) {}
public SqliteException(string message, int code) : base(message) { NativeErrorCode = code; }
}
public static IntPtr Open(string filename) {
IntPtr db;
int result = open(filename, out db);
if (result != SQLITE_OK) throw new SqliteException(result);
result = result_codes(db, 1); // report extended result codes by default.
if (result != SQLITE_OK) throw new SqliteException(result);
return db;
}
public static void Close(IntPtr db) {
int result = close(db);
if (result != SQLITE_OK) throw new SqliteException(result);
}
public static DataTable Execute(IntPtr db, string query) {
IntPtr stmt;
DataTable dt = new DataTable();
int result = prepare(db, query, -1, out stmt, IntPtr.Zero);
if (result != SQLITE_OK) throw new SqliteException(result);
int colCount = column_count(stmt);
// Get the first row so that column namescan be determined.
result = step(stmt);
if (result == SQLITE_ROW) {
// Add corresponding columns to the data-table object.
// NOTE: Since any column value can be NULL, we cannot infer fixed data
// types for the columns and therefore *must* use typeof(object).
for (int c = 0; c < colCount; c++) {
dt.Columns.Add(Marshal.PtrToStringUni(column_name(stmt, c)), typeof(object));
}
} else if (result == SQLITE_DONE) { // Either a query without results or a non-query statement.
result = finalize(stmt);
if (result != 0) throw new SqliteException(result);
// For simplicity, return an empty DataTable instance either way.
// In a PowerShell pipeline, its .Rows collection is automatically enumerated,
// meaning that *no* objects are sent through the pipeline.
// If a non-query's output isn't captured (which shouldn't be necessary), PowerShell's automatic enumeration
// in the pipeline ensures that *no* output stream pollution occurs.
return dt;
} else {
throw new SqliteException(result);
}
// Fetch all rows and populate a DataTable instance with them.
object[] rowData = new object[colCount];
do {
for (int i = 0; i < colCount; i++) {
// Note: The column types must be determined for each and every row,
// given that NULL values may be present.
switch (column_type(stmt, i)) {
case SQLITE_INTEGER: // covers all integer types up to System.Int64
rowData[i] = column_int64(stmt, i);
break;
case SQLITE_FLOAT:
rowData[i] = column_double(stmt, i);
break;
case SQLITE_TEXT:
rowData[i] = Marshal.PtrToStringUni(column_text(stmt, i));
break;
case SQLITE_BLOB:
IntPtr ptr = column_blob(stmt, i);
int len = column_bytes(stmt, i);
byte[] arr = new byte[len];
Marshal.Copy(ptr, arr, 0, len);
rowData[i] = arr;
break;
case SQLITE_NULL:
rowData[i] = DBNull.Value;
break;
default:
throw new Exception(String.Format("DESIGN ERROR: Unexpected column-type ID: {0}", column_type(stmt, i)));
}
}
dt.Rows.Add(rowData);
} while (step(stmt) == SQLITE_ROW);
result = finalize(stmt);
if (result != SQLITE_OK) throw new SqliteException(result);
return dt;
}
}
"@
Sample usage (assumes that you've run the Add-Type command above beforehand):
# Abort on (uncaught) errors.
$ErrorActionPreference = 'Stop'
# Open an in-memory database (':memory:')
# To open - or create on demand - a database *file*,
# pass its *full path* of the database file.
# To convert a *relative* path to a full one:
# * If the file exists, use (Convert-Path $relativePath)
# * Otherwise, use "$PWD/$relativePath"
$db = [SqliteHelper]::Open(':memory:')
# NOTE:
# Submit ONE statement at a time with ::Execute() (";" terminator is optional).
# Create a sample table.
# NOTE: This fails, if the table already exists.
# Non-ASCII characters are included to test if character-encoding issues arise.
[SqliteHelper]::Execute($db, @'
create table sample_table (Name string, Äge int)
'@)
# Insert rows into the sample table.
# Non-ASCII characters are included to test if character-encoding issues arise.
# A 64-bit integer is included to test if overflow issues arise.
[SqliteHelper]::Execute($db, @'
insert into sample_table (Name, Äge) values ('JDoë', 42), ('JRoe', 5856288576210)
'@)
# Submit a sample query.
$query = @'
SELECT * FROM sample_table;
'@
$dataTable = [SqliteHelper]::Execute($db, $query)
# Display the results.
$dataTable
# Close the database.
[SqliteHelper]::Close($db)