4
PowerShell 操作 SQLite 資料庫
source link: https://blog.darkthread.net/blog/ps-sqlite-example/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
PowerShell 操作 SQLite 資料庫
 | 2024-01-27 09:35 AM |  | 0 |  | 1,456 |
PowerShell 是某些情境跑程式的首選，能省下掏粉自清的麻煩。
我手邊有 PowerShell 存取 SQL/Oracle 的範例，再補上 SQLite。
首先是決定用什麼程式庫，共有 System.Data.SQLite 及 Microsoft.Data.Sqlite 兩種選擇，前者由 SQLite 官方維護，後者是微軟 Entity Framework 小組配合 .NET Core (.NET 6+) 需求發展的輕量級版本。若要在 PowerShell 使用，我偏好 System.Data.SQLite，需部署的檔案數量較少。(Microsoft.Data.Sqlite 依賴 SQLitePCLRaw.bundle-*，需要的 dll 檔案較多，參考：.NET 6 SQLite 原生程式庫版本問題)
前置作業是取得 System.Data.SQLite 所需 dll 檔案。無腦做法是開個 .NET Console 專案用 NuGet 安裝 System.Data.SQLite，編譯後由 bin 目錄取檔，將以下檔案複製到 .ps 所在目錄：
- System.Data.SQLite.dll
- x64\SQLite.Interop.dll
- x86\SQLite.Interop.dll (實測這個可以不用)
.ps 程式範例如下，就是標準 ADO.NET 操作，應沒有任何難度：
$ErrorActionPreference = 'STOP'
$dbPath = '.\test.sqlite'
$cs = "Data Source=$dbPath";
$needInit = !(Test-Path $dbPath)
Add-Type -Path .\System.Data.SQLite.dll
$cn = [System.Data.SQLite.SQLiteConnection]::new($cs)
$cn.Open()
$cmd = $cn.CreateCommand();
if ($needInit) {
$cmd.CommandText = @"
CREATE TABLE Player (
Id VARCHAR(16),
Name VARCHAR(32),
CONSTRAINT Player_PK PRIMARY KEY (Id)
)
"@
$cmd.ExecuteNonQuery() | Out-Null
}
$cmd.CommandText = 'DELETE FROM Player'
$cmd.ExecuteNonQuery() | Out-Null
$cmd.CommandText = 'INSERT INTO Player VALUES (@Id, @Name)'
$cmd.Parameters.Add([System.Data.SQLite.SQLiteParameter]::new('Id', 'A1234')) | Out-Null
$p = $cmd.Parameters.Add('Name', [System.Data.DbType]::AnsiString)
$p.Value = 'Jeffrey'
$rowAffected = $cmd.ExecuteNonQuery()
Write-Host "$rowAffected rows inserted"
$cmd.CommandText = "SELECT * FROM Player"
$cmd.Parameters.Clear()
$dr = $cmd.ExecuteReader()
while ($dr.Read()) {
Write-Host $dr["Id"] $dr["Name"]
}
$cn.Dispose()
- Posted in
- PowerShell
and has 0 comments
Comments
Be the first to post a comment
Post a comment
Comment Name Captcha
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK