使用嵌入式C#进行SQLite查询

感谢您分享your solution，这是一种从PowerShell使用SQLite的轻量级方式，无需安装模块或库。
此外，它还可以在ARM 64机器上工作，而NuGet软件包（如Microsoft.Data.Sqlite和System.Data.SQLite）目前不可以。
我冒昧地以下列方式修改了您的代码：

实现了代码跨版本（同时运行在Windows PowerShell和PowerShell（Core）7+）和跨平台;注意：在Windows和macOS上测试-不确定Linux。
使API对PowerShell更加友好（返回 data 而不是通过结果代码传递，并需要by-ref参数;使用例外）
Unicode支持（SQLite在内部使用UTF-8，Marshal.PtrToStringAnsi()会误解）。
改进的数据类型支持：
为查询结果返回的DataTable示例现在使用.NET类型的列，这些列对应于SQLite数据类型（如果仅使用$dataTableInstance.Columns.Add(<string>)，则会得到 string 列）。
INTEGER SQLite类型Map到System.Int64（[long]）上，以确保不会发生溢出。
- 警告 *：假设所有列值都是 * 相同 * 类型; SQLite技术上允许给定的列值为 * 任意 * 类型-参见the docs。
  通过Add-Type定义静态helper类[SqliteHelper]，并通过C#代码进行临时编译，对本地SQLite库进行P/Invoke调用;会导致每个会话的一次性编译性能损失：

# 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;

  // Map data type IDs to .NET types.
  private static readonly Dictionary<int, Type> _colTypeToType = new Dictionary<int, Type> 
  { 
    { SQLITE_INTEGER, typeof(Int64) },
    { SQLITE_FLOAT, typeof(double) },
    { SQLITE_TEXT, typeof(string) },
    { SQLITE_BLOB, typeof(byte[]) },
  };

  // 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);
      int[] columnTypes = new int[colCount];

      // Get the first row so that column names and types can be determined.
      result = step(stmt);
      if (result == SQLITE_ROW) {
        for (int c = 0; c < colCount; c++) {
          columnTypes[c] = column_type(stmt, c);
          dt.Columns.Add(Marshal.PtrToStringUni(column_name(stmt, c)), _colTypeToType[columnTypes[c]]);
        }
      } 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++) {
          switch (columnTypes[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}", columnTypes[i]));
          }
        }
        dt.Rows.Add(rowData);
      } while (step(stmt) == SQLITE_ROW);

      result = finalize(stmt);
      if (result != SQLITE_OK) throw new SqliteException(result);

      return dt;
  }
}
"@

示例用法（假设您事先运行过上面的Add-Type命令）：

# 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)

您可以通过利用PowerShell中的System.Data命名空间并嵌入与SQLite数据库交互的C#代码来实现这一目标。下面是一种使用winsqlite3.dll库实现SQLite功能的方法：

cls
Remove-Variable * -ea 0
$errorActionPreference = 'stop'

$entry   = '[DllImport("winsqlite3.dll", EntryPoint="sqlite3'
add-type -TypeDefinition @"
using System;
using System.Data;
using System.Collections.Generic;
using System.Runtime.InteropServices;

public static class sqlite {
    ${entry}_open")]          public  static extern IntPtr open(String filename, ref IntPtr db);
    ${entry}_prepare_v2")]    private static extern IntPtr prepare(IntPtr db, String query, int len, ref IntPtr stmt, IntPtr dummy);
    ${entry}_step")]          private static extern int    step(IntPtr stmt);
    ${entry}_column_count")]  private static extern int    column_count( IntPtr stmt);
    ${entry}_column_name")]   private static extern IntPtr column_name(  IntPtr stmt, int col);
    ${entry}_column_type")]   private static extern int    column_type(  IntPtr stmt, int col);
    ${entry}_column_double")] private static extern Double column_double(IntPtr stmt, int col);
    ${entry}_column_int")]    private static extern int    column_int(   IntPtr stmt, int col);
    ${entry}_column_int64")]  private static extern Int64  column_int64( IntPtr stmt, int col);
    ${entry}_column_text")]   private static extern IntPtr column_text(  IntPtr stmt, int col);
    ${entry}_column_blob")]   private static extern IntPtr column_blob(  IntPtr stmt, int col);
    ${entry}_column_bytes")]  private static extern int    column_bytes( IntPtr stmt, int col);
    ${entry}_finalize")]      private static extern IntPtr finalize(IntPtr stmt);
    ${entry}_close")]         public  static extern IntPtr close(IntPtr db);

    public static DataTable dbquery(IntPtr db, string query) {
        IntPtr stmt = IntPtr.Zero;
        DataTable dt = new DataTable();
        IntPtr result = prepare(db, query, -1, ref stmt, IntPtr.Zero);
        if (stmt == IntPtr.Zero) {return dt;}

        int colEnd = column_count(stmt);
        string[] columnNames = new string[colEnd];
        int[] columnTypes = new int[colEnd];

        // Execute the first step to retrieve column types
        if (step(stmt) == 100) {
            for (int c = 0; c < colEnd; c++) {
                IntPtr ptr = column_name(stmt, c);
                columnNames[c] = Marshal.PtrToStringAnsi(ptr);
                dt.Columns.Add(columnNames[c]);
                columnTypes[c] = column_type(stmt, c);
            }
        } else {
            finalize(stmt);
            return dt;
        }

        do {
            object[] rowData = new object[colEnd];
            for (int i = 0; i < colEnd; i++) {
                switch (columnTypes[i]) {
                    case 1:
                        rowData[i] = column_int(stmt, i);
                        break;
                    case 2:
                        rowData[i] = column_double(stmt, i);
                        break;
                    case 3:
                        IntPtr ptr = column_text(stmt, i);
                        rowData[i] = Marshal.PtrToStringAnsi(ptr);
                        break;
                    case 4:
                        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;
                    default:
                        rowData[i] = DBNull.Value;
                        break;
                }
            }
            dt.Rows.Add(rowData);
        } while (step(stmt) == 100);

        finalize(stmt);
        return dt;
    }
}
"@ -ReferencedAssemblies System.Data, System.Xml

# Sample query
$query = @"
SELECT * FROM MyTable
"@

# Connect to the database file or use ':memory:' for in-memory DB
$db = [IntPtr]::Zero
$filename = "path/to/your/database.sqlite3"
$result = [sqlite]::open($filename, [ref]$db)

if ($result -eq 0) {
    # Query the database
    $dataTable = [sqlite]::dbquery($db, $query)

    # Display the result
    $dataTable
}
else {
    Write-Host "Database connection failed."
}

# Close the database connection
[sqlite]::close($db)

下面是代码的功能分解：
嵌入的C#代码定义了一个静态类sqlite，其中包含DLL导入，用于您需要的SQLite函数。它还包括一个dbquery方法，用于执行SQL查询并将结果作为DataTable检索。
PowerShell脚本通过使用数据库文件名调用[sqlite]：：open连接到SQLite数据库。如果连接成功，它将检索表示数据库连接（db）的IntPtr。
该脚本通过调用[sqlite]：：dbquery并传递数据库连接（db）和查询字符串来执行示例查询。结果存储在DataTable对象中。
该脚本显示生成的DataTable对象，其中包含从数据库检索到的数据。您可以根据需要修改此部件以格式化或进一步处理数据。
最后，脚本使用[sqlite]：：close关闭数据库连接。

使用嵌入式C#进行SQLite查询

2条答案

相关问题

热门标签

最新问答