Form と sqlite による検索処理の実装

一つ上へ

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
 
# SQLite アセンブリの読み込み
Add-Type -Path ".\System.Data.SQLite.dll"
 
# === フォーム作成 ===
$form = New-Object System.Windows.Forms.Form
$form.Text = "SQLite 検索ツール"
$form.Size = New-Object System.Drawing.Size(500,400)
 
# 入力ボックス
$textBox = New-Object System.Windows.Forms.TextBox
$textBox.Location = New-Object System.Drawing.Point(20,20)
$textBox.Size = New-Object System.Drawing.Size(300,20)
$form.Controls.Add($textBox)
 
# 検索ボタン
$button = New-Object System.Windows.Forms.Button
$button.Text = "検索"
$button.Location = New-Object System.Drawing.Point(340,20)
$form.Controls.Add($button)
 
# 結果表示用（ListBox）
$listBox = New-Object System.Windows.Forms.ListBox
$listBox.Location = New-Object System.Drawing.Point(20,60)
$listBox.Size = New-Object System.Drawing.Size(440,280)
$form.Controls.Add($listBox)
 
# === SQLite DB 初期化 ===
$dbPath = ".\test.db"
if (-not (Test-Path $dbPath)) {
    [System.Data.SQLite.SQLiteConnection]::CreateFile($dbPath)
}
 
$connectionString = "Data Source=$dbPath;Version=3;"
$connection = New-Object System.Data.SQLite.SQLiteConnection $connectionString
$connection.Open()
 
# テーブル作成（存在しない場合）
$cmd = $connection.CreateCommand()
$cmd.CommandText = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)"
$cmd.ExecuteNonQuery()
 
# サンプルデータを投入（存在しない場合のみ）
$cmd.CommandText = "INSERT INTO users (name) VALUES ('Alice'),('Bob'),('Charlie')"
try { $cmd.ExecuteNonQuery() } catch {}
 
# === ボタンクリック処理 ===
$button.Add_Click({
    $listBox.Items.Clear()
    $keyword = $textBox.Text
 
    $query = "SELECT id, name FROM users WHERE name LIKE @kw"
    $cmd = $connection.CreateCommand()
    $cmd.CommandText = $query
    $param = $cmd.CreateParameter()
    $param.ParameterName = "@kw"
    $param.Value = "%$keyword%"
    $cmd.Parameters.Add($param) | Out-Null
 
    $reader = $cmd.ExecuteReader()
    while ($reader.Read()) {
        $listBox.Items.Add("ID=$($reader['id']), Name=$($reader['name'])")
    }
    $reader.Close()
})
 
# === フォーム表示 ===
$form.Add_FormClosed({
    $connection.Close()
})
$form.ShowDialog()