差分

このページの2つのバージョン間の差分を表示します。

--- powershell:form_sqlite_db_client [2025/09/24 01:18] – 作成 mikoto
+++ powershell:form_sqlite_db_client [2025/10/02 07:38] (現在) – mikoto
@@ 行 3: / 行 3: @@
 <code powershell>
+# Form用のモジュールを読み込み
 Add-Type -AssemblyName System.Windows.Forms
 Add-Type -AssemblyName System.Drawing
 # sqlite3.dll を読み込む
-Add-Type -Path ".\System.Data.SQLite.dll"
+Add-Type -Path "path\to\dll\System.Data.SQLite.dll"
-# フォーム作成
+# --- クエリ実行関数 ---
-$form = New-Object System.Windows.Forms.Form
+function showTableList {
-$form.Text = "Easy DB Client"
+    param(
-$form.Size = New-Object System.Drawing.Size(1000,600)
+        [System.Data.SQLite.SQLiteConnection]$Connection
+    )
-# DBのファイルパス(無ければ新規作成)
+    $cmd = $Connection.CreateCommand()
-$dbFile = ".\test.db"
+    $cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
-$connectionString = "Data Source=$dbFile;Version=3;"
-if (-not (Test-Path $dbFile)) {
+    try {
-    [System.Data.SQLite.SQLiteConnection]::CreateFile($dbFile)
+        $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($connectionString)
+$conn = New-Object System.Data.SQLite.SQLiteConnection($connStr)
 $conn.Open()
-# コマンドオブジェクト
-$cmd = $conn.CreateCommand()
-# --- テーブルリスト表示 ---
+# フォーム作成
-$cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
+$form = New-Object System.Windows.Forms.Form
+$form.Text = "簡易DBクライアント"
+$form.Size = New-Object System.Drawing.Size(1200,800)
-$labelTable = New-Object System.Windows.Forms.Label
+# テーブル一覧表示領域ラベル
-$labelTable.Text = "テーブルリスト"
+$tbListLabel = New-Object System.Windows.Forms.Label
-$labelTable.Location = New-Object System.Drawing.Point(20,20)
+$tbListLabel.Text = "テーブルリスト"
-$labelTable.Size = New-Object System.Drawing.Size(100,15)
+$tbListLabel.Location = New-Object System.Drawing.Point(20,20)
+$tbListLabel.Size = New-Object System.Drawing.Size(100,15)
-$listBoxTable = New-Object System.Windows.Forms.ListBox
+# テーブル一覧表示領域
-$listBoxTable.Size = New-Object System.Drawing.Size(300,200)
+$tbListArea = New-Object System.Windows.Forms.ListBox
-$listBoxTable.Location = New-Object System.Drawing.Point(20,40)
+#$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 # テーブル一覧を取得
-$reader = $cmd.ExecuteReader()
+foreach ($t in $tbList) {
-while ($reader.Read()) {
+    $tbListArea.Items.Add($t.name)
-    $tableName = $reader["name"]
-    $listBoxTable.Items.Add($tableName)
 }
-$reader.Close()
-# レコード表示ボタン
-$btnShow = New-Object System.Windows.Forms.Button
-$btnShow.Text = "表示"
-$btnShow.Location = New-Object System.Drawing.Point(20,500)
-# 保存ボタン
-$btnSave = New-Object System.Windows.Forms.Button
-$btnSave.Text = "保存"
-$btnSave.Location = New-Object System.Drawing.Point(100,500)
 # DataGridView
 $dataGrid = New-Object System.Windows.Forms.DataGridView
-$dataGrid.Location = New-Object System.Drawing.Point(350,50)
+$dataGrid.Location = New-Object System.Drawing.Point(350,40)
-$dataGrid.Size = New-Object System.Drawing.Size(600,400)
+$dataGrid.Height = $form.ClientSize.Height -$tbListArea.Top - 50
+$dataGrid.Width = $form.ClientSize.Width - $tbListArea.Right - 50
 $dataGrid.AutoSizeColumnsMode = "Fill"
-$dataGrid.AllowUserToAddRows = $true
+#$dataGrid.AllowUserToAddRows = $true
-$dataGrid.AllowUserToDeleteRows = $true
+#$dataGrid.AllowUserToDeleteRows = $true
-$dataGrid.ReadOnly = $false
+$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
-# 変数をフォームスコープで共有
+# レコード表示ボタン
-$dataTable = $null
+$btnShow = New-Object System.Windows.Forms.Button
-$adapter = $null
+$btnShow.Text = "表示"
-$table = $null
+$btnShow.Location = New-Object System.Drawing.Point(350, 10)
 # レコード表示ボタンイベント
 $btnShow.Add_Click({
-    if ($listBoxTable.SelectedItem -ne $null) {
+    Write-Host "Clicked"
-        $table = [string]$listBoxTable.SelectedItem
+    Write-Host $tbListArea.SelectedItem
+    if ($tbListArea.SelectedItem -ne $null) {
+        $table = [string]$tbListArea.SelectedItem
+        $cmd = $conn.CreateCommand()
         $cmd.CommandText = "SELECT * FROM [$table];"
@@ 行 91: / 行 130: @@
 })
-# 保存ボタンクリックイベント
+# コントロールを追加
-$btnSave.Add_Click({
+$form.Controls.Add($tbListLabel)
-    if ($script:adapter -ne $null -and $script:dataTable -ne $null) {
+$form.Controls.Add($tbListArea)
-        try {
-            # DataGridView の編集中セルを確定
-            if ($dataGrid.IsCurrentCellInEditMode) {$dataGrid.EndEdit() }
-            $dataGrid.CurrentCell = $null # フォーカスを外して編集を確定
-            # DataTableの変更を確認
-            $changes = $script:dataTable.GetChanges()
-            if ($changes -ne $null) {
-                $script:adapter.Update($script:dataTable)
-                Write-Host "UpdateCommand:" $adapter.UpdateCommand.CommandText
-                Write-Host "InsertCommand:" $adapter.InsertCommand.CommandText
-                Write-Host "DeleteCommand:" $adapter.DeleteCommand.CommandText
-                [System.Windows.Forms.MessageBox]::Show("変更を保存しました","保存完了")
-            } else {
-                [System.Windows.Forms.MessageBox]::Show("保存対象の変更がありません","情報")
-            }
-        }
-        catch {
-            [System.Windows.Forms.MessageBox]::Show("保存中にエラーが発生しました。`n$_","エラー")
-        }
-    } else {
-        [System.Windows.Forms.MessageBox]::Show("保存できるデータがありません。先に表示ボタンでテーブルを読み込んでください。","情報")
-    }
-})
-# コントロール
-$form.Controls.Add($labelTable)
-$form.Controls.Add($listBoxTable)
-$form.Controls.Add($btnShow)
 $form.Controls.Add($dataGrid)
-$form.Controls.Add($btnSave)
+$form.Controls.Add($btnShow)
 # --- フォームを閉じたら DB も閉じる ---
@@ 行 131: / 行 141: @@
 # フォームを表示
 [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>