NK University

この文書は読取専用です。文書のソースを閲覧することは可能ですが、変更はできません。もし変更したい場合は管理者に連絡してください。
====== SQLiteによる簡易DBビューワー ======

<code powershell>
# Form用のモジュールを読み込み
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

# sqlite3.dll を読み込む
Add-Type -Path "C:\dll\System.Data.SQLite.dll"

# --- クエリ実行関数 ---
function showAllRecords {
    param(
        [System.Data.SQLite.SQLiteConnection]$connection
    )

    $dataTable = New-Object System.Data.DataTable

    $cmd = $connection.CreateCommand()
    $cmd.CommandText = "SELECT shop, product_name, url FROM haru_master LIMIT 100;"
    
    $reader = $cmd.ExecuteReader()
# スキーマ作成
    for ($i=0; $i -lt $reader.FieldCount; $i++) {
        $dataTable.Columns.Add($reader.GetName($i))
    }

# データ追加
    while ($reader.Read()) {
        $row = $dataTable.NewRow()
        for ($i=0; $i -lt $reader.FieldCount; $i++) {
            $row[$i] = $reader.GetValue($i)
        }
        $dataTable.Rows.Add($row)
    }

    $reader.Close()

#    $adapter = New-Object System.Data.SQLite.SQLiteDataAdapter($cmd)

#    $adapter.Fill($dataTable) | Out-Null

    return $dataTable
}

# --- テーブルデータを表示 ---
function showData {
    param (
        [System.Data.SQLite.SQLiteConnection]$Connection
    )

    $cmd = $Connection.CreateCommand()
    $cmd.CommandText = "SELECT shop, product_name, url FROM "

    try {
        $results = New-Object System.Collections.Generic.IList[object]
        return $null
    }
    catch {
        return $null
    }
}

# --- 検索用関数 ---
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
}

# 初期化処理
# DBのファイルパス
$dbFile = "C:\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.MinimumSize = New-Object System.Drawing.Size(900,600)
$form.Size = New-Object System.Drawing.Size(1200,800)

# 検索フィールドラベル
$searchLabel = New-Object System.Windows.Forms.Label
$searchLabel.Text = "キーワード"
$searchLabel.Location = New-Object System.Drawing.Point(20,20)
$searchLabel.Size = New-Object System.Drawing.Size(100,15)

# 検索テキストボックス
$searchTextBox = New-Object System.Windows.Forms.TextBox
$searchTextBox.Location = New-Object System.Drawing.Point(120,15)
$searchTextBox.Width = $form.ClientSize.Width - 250
$searchTextBox.Anchor = [System.Windows.Forms.AnchorStyles]::Top `
    -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(20,40)

# レコード表示ボタンイベント
$btnShow.Add_Click({
#    $dataTable = showAllRecords -connection $conn

    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "SELECT * FROM haru_master;"
    $dataTable = New-Object System.Data.DataTable

    $adapter = New-Object System.Data.SQLite.SQLiteDataAdapter $cmd
    $adapter.Fill($dataTable)

    Write-Host $dataTable.Rows.Count

    $dataGrid.DataSource = $dataTable
    $dataGrid.Refresh()

})

# 検索ボタン
$btnSearch = New-Object System.Windows.Forms.Button
$btnSearch.Text = "検索"
$btnSearch.Location = New-Object System.Drawing.Point(1080,15)
$btnSearch.Anchor = [System.Windows.Forms.AnchorStyles]::Top `
    -bor [System.Windows.Forms.AnchorStyles]::Right

# 検索ボタンクリックイベント
$btnSearch.Add_Click({
    if ([string]$tbListArea.SelectedItem -ne $null) {
        $table_name = ([string]$tbListArea.SelectedItem).Trim()
        $keyword = [string]$searchTextBox.Text
        $dataTable = searchRecords -connection $conn -table_name $table_name -keyword $keyword
        $dataGrid.DataSource = $dataTable
        $dataTable | Format-Table -AutoSize
    } else {
        Write-Host "Dame"
    }
})

# DataGridView
$dataGrid = New-Object System.Windows.Forms.DataGridView
$dataGrid.Location = New-Object System.Drawing.Point(20,80)
$dataGrid.Height = $form.ClientSize.Height - 90
$dataGrid.Width = $form.ClientSize.Width - 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


# コントロールを追加
$form.Controls.Add($searchLabel)
$form.Controls.Add($searchTextBox)
$form.Controls.Add($dataGrid)
$form.Controls.Add($btnShow)
$form.Controls.Add($btnSearch)

# --- フォームを閉じたら DB も閉じる ---
$form.Add_FormClosed({ $conn.Close() })

# フォームを表示
[void]$form.ShowDialog()

</code>