Using Windows 10 Winsqlite3.dll

[Joe Caverly]

I wanted to try using the Windows 10 built-in winsqlite3.dll,
so I created the SQLite command in my CsScript.dll plugin.

The first step was to add the Win32 API declares for the winsqlite3.dll using P/Invoke;

        // --- SQLite (winsqlite3.dll) P/Invoke ---

        private const string SQLITE_DLL = "winsqlite3.dll";

        [DllImport(SQLITE_DLL)]
        private static extern int sqlite3_open(string filename, out IntPtr db);

        [DllImport(SQLITE_DLL)]
        private static extern int sqlite3_close(IntPtr db);

        [DllImport(SQLITE_DLL)]
        private static extern int sqlite3_prepare_v2(
            IntPtr db,
            string sql,
            int numBytes,
            out IntPtr stmt,
            IntPtr pzTail);

        [DllImport(SQLITE_DLL)]
        private static extern int sqlite3_step(IntPtr stmt);

        [DllImport(SQLITE_DLL)]
        private static extern int sqlite3_finalize(IntPtr stmt);

        [DllImport(SQLITE_DLL)]
        private static extern IntPtr sqlite3_errmsg(IntPtr db);

        [DllImport(SQLITE_DLL)]
        private static extern IntPtr sqlite3_column_text(IntPtr stmt, int col);

        [DllImport(SQLITE_DLL)]
        private static extern int sqlite3_column_count(IntPtr stmt);

        private const int SQLITE_ROW = 100;
        private const int SQLITE_DONE = 101;

Next, the C# Code for the plugin;

        /// <summary>
        /// Command: SQLITE "SQL STATEMENT"
        /// Runs SQL against an in-memory SQLite DB using winsqlite3.dll.
        /// Example:
        ///   SQLITE SELECT 6.59 * 0.454 AS result
        ///   SQLITE u:\jlc.db SELECT * FROM transactions
        /// </summary>
        public int SQLITE(StringBuilder args)
        {
            string input = args.ToString().Trim();
            if (string.IsNullOrEmpty(input))
            {
                Console.Error.WriteLine("SQLITE error: SQL statement required");
                return 1;
            }

            // Split into tokens
            string[] parts = input.Split(new[] { ' ' }, 2, StringSplitOptions.RemoveEmptyEntries);
            if (parts.Length == 0)
            {
                Console.Error.WriteLine("SQLITE error: SQL statement required");
                return 1;
            }

            string dbPath;
            string sql;

            // If first token ends with .db, treat it as a filename
            if (parts[0].EndsWith(".db", StringComparison.OrdinalIgnoreCase))
            {
                dbPath = parts[0];

                if (!Path.IsPathRooted(dbPath))
                    dbPath = Path.Combine(Environment.CurrentDirectory, dbPath);

                if (!File.Exists(dbPath))
                {
                    Console.Error.WriteLine($"SQLITE error: database not found: {dbPath}");
                    return 1;
                }

                if (parts.Length < 2)
                {
                    Console.Error.WriteLine("SQLITE error: SQL statement required after database name");
                    return 1;
                }

                sql = parts[1].Trim();
            }
            else
            {
                // No DB specified → use in-memory
                dbPath = ":memory:";
                sql = input;
            }

            // Strip surrounding quotes
            if ((sql.StartsWith("\"") && sql.EndsWith("\"")) ||
                (sql.StartsWith("'") && sql.EndsWith("'")))
            {
                sql = sql.Substring(1, sql.Length - 2);
            }

            IntPtr db;
            int rc = sqlite3_open(dbPath, out db);
            if (rc != 0)
            {
                Console.Error.WriteLine($"SQLITE error: cannot open database {dbPath}");
                return 1;
            }

            IntPtr stmt;
            rc = sqlite3_prepare_v2(db, sql, -1, out stmt, IntPtr.Zero);
            if (rc != 0)
            {
                string err = Marshal.PtrToStringAnsi(sqlite3_errmsg(db));
                Console.Error.WriteLine($"SQLITE error: {err}");
                sqlite3_close(db);
                return 1;
            }

            int colCount = sqlite3_column_count(stmt);

            while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
            {
                for (int i = 0; i < colCount; i++)
                {
                    IntPtr txt = sqlite3_column_text(stmt, i);
                    string val = Marshal.PtrToStringAnsi(txt);
                    Console.Write(val);
                    if (i < colCount - 1) Console.Write(" | ");
                }
                Console.WriteLine();
            }

            sqlite3_finalize(stmt);
            sqlite3_close(db);
            return 0;
        }

This was just a proof-of-concept,
just to see if I could actually do this,
so the SQLite command does not do much.

R:\>SQLite SELECT 6.59 * 0.454 AS result
2.99186

R:\>SQLite u:\jlc.db SELECT * FROM transactions
2025-08-16 | ZMI
2025-11-18 | PLZ.UN
 |
 |
 |
 |
 |

R:\>

CsScript.zip plugin is attached if you want to test.

Ref: List Of SQLite Functions

Joe