FormsとSQLiteによる簡易DBクライアント

一つ上へ

# Form用のモジュールを読み込み
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
 
# sqlite3.dll を読み込む
Add-Type -Path "path\to\dll\System.Data.SQLite.dll"
 
# --- クエリ実行関数 ---
function showTableList {
    param(
        [System.Data.SQLite.SQLiteConnection]$Connection
    )
 
    $cmd = $Connection.CreateCommand()
    $cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
 
    try {
        $results = New-Object System.Collections.Generic.List[object]
 
        $reader = $cmd.ExecuteReader()
        while ($reader.Read()) {
            $row = [PSCustomObject]@{
                name = $reader["name"]
            }
            $results.Add($row) | Out-Null
        }
        $reader.Close()
        return $results
    }
    catch {
        return $null
    }
}
 
# --- テーブルデータを表示 ---
function showData {
    param (
        [System.Data.SQLite.SQLiteConnection]$Connection
    )
 
    $cmd = $Connection.CreateCommand()
    $cmd.CommandText = "SELECT col1,col2,col3 FROM tablename"
 
    try {
        $results = New-Object System.Collections.Generic.List[object]
        return $null
    }
    catch {
        return $null
    }
}
 
# 初期化処理
# DBのファイルパス(無ければ新規作成)
$dbFile = "path\to\databases\database.db"
$connStr = "Data Source=$dbFile;Version=3;"
# SQLite 接続オブジェクトを作成
$conn = New-Object System.Data.SQLite.SQLiteConnection($connStr)
$conn.Open()
 
 
# フォーム作成
$form = New-Object System.Windows.Forms.Form
$form.Text = "簡易DBクライアント"
$form.Size = New-Object System.Drawing.Size(1200,800)
 
# テーブル一覧表示領域ラベル
$tbListLabel = New-Object System.Windows.Forms.Label
$tbListLabel.Text = "テーブルリスト"
$tbListLabel.Location = New-Object System.Drawing.Point(20,20)
$tbListLabel.Size = New-Object System.Drawing.Size(100,15)
 
# テーブル一覧表示領域
$tbListArea = New-Object System.Windows.Forms.ListBox
#$tbListArea.Size = New-Object System.Drawing.Size(300,200)
$tbListArea.Location = New-Object System.Drawing.Point(20,40)
$tbListArea.Width = 300
$tbListArea.Height = $form.ClientSize.Height - $tbListArea.Top - 20 # initial height
$tbListArea.Anchor = [System.Windows.Forms.AnchorStyles]::Top `
    -bor [System.Windows.Forms.AnchorStyles]::Bottom `
    -bor [System.Windows.Forms.AnchorStyles]::Left
 
$tbList = showTableList $conn # テーブル一覧を取得
foreach ($t in $tbList) {
    $tbListArea.Items.Add($t.name)
}
 
# DataGridView
$dataGrid = New-Object System.Windows.Forms.DataGridView
$dataGrid.Location = New-Object System.Drawing.Point(350,40)
$dataGrid.Height = $form.ClientSize.Height -$tbListArea.Top - 50
$dataGrid.Width = $form.ClientSize.Width - $tbListArea.Right - 50
$dataGrid.AutoSizeColumnsMode = "Fill"
#$dataGrid.AllowUserToAddRows = $true
#$dataGrid.AllowUserToDeleteRows = $true
$dataGrid.ReadOnly = $true
$dataGrid.Anchor = [System.Windows.Forms.AnchorStyles]::Top `
    -bor [System.Windows.Forms.AnchorStyles]::Bottom `
    -bor [System.Windows.Forms.AnchorStyles]::Left `
    -bor [System.Windows.Forms.AnchorStyles]::Right
 
# レコード表示ボタン
$btnShow = New-Object System.Windows.Forms.Button
$btnShow.Text = "表示"
$btnShow.Location = New-Object System.Drawing.Point(350, 10)
 
# レコード表示ボタンイベント
$btnShow.Add_Click({
    Write-Host "Clicked"
    Write-Host $tbListArea.SelectedItem
    if ($tbListArea.SelectedItem -ne $null) {
        $table = [string]$tbListArea.SelectedItem
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = "SELECT * FROM [$table];"
 
        $script:adapter = New-Object System.Data.SQLite.SQLiteDataAdapter $cmd
 
        # 自動でINSERT/UPDATE/DELETEコマンドを生成
        $script:builder = New-Object System.Data.SQLite.SQLiteCommandBuilder $adapter
 
        $script:dataTable = New-Object System.Data.DataTable
        [void]$script:adapter.Fill($dataTable)
 
        $dataGrid.DataSource = $dataTable
    }
})
 
# コントロールを追加
$form.Controls.Add($tbListLabel)
$form.Controls.Add($tbListArea)
$form.Controls.Add($dataGrid)
$form.Controls.Add($btnShow)
 
# --- フォームを閉じたら DB も閉じる ---
$form.Add_FormClosed({ $conn.Close() })
 
# フォームを表示
[void]$form.ShowDialog()

# --- 検索用関数 ---
function searchRecords {
    param (
        [System.Data.SQLite.SQLiteConnection]$connection,
        [string]$table_name,
        [string]$keyword
    )
 
    $dataTable = New-Object System.Data.DataTable
    $cmd = $connection.CreateCommand()
    $cmd.CommandText = "SELECT * FROM haru_master WHERE product_name LIKE '%PENN%' COLLATE NOCASE LIMIT 500;"
#    $null = $cmd.Parameters.AddWithValue("@kw", "%$keyword%")
 
    $adapter = New-Object System.Data.SQLite.SQLiteDataAdapter($cmd)
    $adapter.Fill($dataTable) | Out-Null
    return $dataTable
}
 
# 検索ボタン
$btnSearch = New-Object System.Windows.Forms.Button
$btnSearch.Text = "検索"
$btnSearch.Location = New-Object System.Drawing.Point(430,10)
 
# 検索ボタンクリックイベント
$btnSearch.Add_Click({
    if ([string]$tbListArea.SelectedItem -ne $null) {
        $table_name = ([string]$tbListArea.SelectedItem).Trim()
        $keyword = [string]$textSearch.Text
#        $dataTable = searchRecords -connection $conn -table_name $table_name -keyword $keyword
        $dataGrid.DataSource = $dataTable
        $dataTable | Format-Table -AutoSize
    } else {
        Write-Host "Dame"
    }
})