NK University

この文書は読取専用です。文書のソースを閲覧することは可能ですが、変更はできません。もし変更したい場合は管理者に連絡してください。
====== FormsとSQLiteによる簡易DBクライアント ======
[[:powershell|一つ上へ]]

<code powershell>
# 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()
</code>

===== 検索機能の実装 =====

<code powershell>
# --- 検索用関数 ---
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"
    }
})
</code>