【VB.NET】 WindowsフォームアプリケーションでSQLiteを使ってみた

WindowsフォームアプリケーションでSQLiteデータベースに対してCRUD操作を行う単純なアプリケーションを作成してみました。
そのときのログを残します。

開発環境

• Windows 10 Pro (64bit)
• Microsoft Visual Studio Community 2019
  言語：VB.NET
  ターゲットフレームワーク：.NET Framework 4.7.2

使用ツール

今回はSQLiteデータベースをGUIで管理できるDB Browser for SQLiteというツールを使用して、データベースファイルの作成やプログラムからデータベースを操作した結果を参照します。

データベースファイルの作成

DB Browser for SQLiteを使用してデータベースファイルを作成します。

(1) DB Browser for SQLite を起動して [新しいデータベース] をクリック。

(2) データベースファイルの保存先とファイル名を決めて、[保存] をクリック。

(3) テーブルの定義を編集ダイアログが表示されたら [キャンセル] をクリック。
→テーブルの作成はこれから作成するプログラムから行います。

(4) 空のデータベースファイルが作成されます。

パッケージのインストール

Windowsフォームアプリケーション（.NET Framework）の新しいプロジェクトを作成した後、[プロジェクト] メニューの [Nugetパッケージの管理] を選択します。

検索ボックスに sqlite と入力して検索し、検索結果から System.Data.SQLite.Core を選択してインストールボタンをクリックします。

補足

今回インストールした System.Data.SQLite.Core の他にも色々なパッケージが公開されていて、どれを選択したら良いのか迷いました。
下記に自分なりに調べたことをまとめてみましたが、正直よく分かっていないので間違えていたらすみません。

パッケージ	開発元	メモ
System.Data.SQLite	SQLite開発チーム	Entity Frameworkを使用するとき選択
System.Data.SQLite.Core	SQLite開発チーム	ADO.NETプロバイダのみの使用で良いとき選択
Microsoft.Data.Sqlite	Microsoft	Entitity Framework や .NET Core を使用するとき選択
Microsoft.Data.Sqlite.Core	Microsoft	Microsoft.Data.Sqlite のネイティブのSQLiteライブラリのコピーが含まれていない版

Microsoftが開発したパッケージの方がパフォーマンスが良さそうという記事も見かけました。

私はEntity Framework を使用したことがないので、いつか試して記事にまとめられたらと思います。

フォームのデザイン

フォームにデータベース操作を行うトリガーとなるボタンを配置します。

テキスト	コントロール名	動作
Create Table	Button1	データベースにテーブルを作成する。
Insert	Button2	テーブルにレコードを挿入する。
Select	Button3	テーブルのレコードを検索して結果をコンソール出力する。
Update	Button4	テーブルのレコードを更新する。
Delete	Button5	テーブルのレコードを削除する。

ソースコード

フォーム全体のソースコードです。

Insertを行うところではトランザクションやバインド変数を使用してみました。

Imports System.Data.SQLite

Public Class Form1
    ''' <summary>
    ''' Create Tableボタンクリックイベントハンドラ
    ''' </summary>
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' コネクション作成
        Using con = New SQLiteConnection(GetConnectionString())
            con.Open()
            Using cmd = con.CreateCommand()
                ' テーブル作成SQL
                cmd.CommandText = "CREATE TABLE users (" &
                                  "id INTEGER PRIMARY KEY," &
                                  "name TEXT NOT NULL," &
                                  "age INTEGER," &
                                  "email TEXT NOT NULL UNIQUE" &
                                  ")"
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

    ''' <summary>
    ''' Insertボタンクリックイベントハンドラ
    ''' </summary>
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ' コネクション作成
        Using con = New SQLiteConnection(GetConnectionString())
            con.Open()

            ' トランザクション開始
            Using tx = con.BeginTransaction()
                Using cmd = con.CreateCommand()
                    ' 挿入するデータの作成 
                    Dim users = {New With {.id = 1, .name = "一郎", .age = 40, .email = "abc@example.com"},
                                 New With {.id = 2, .name = "二郎", .age = 32, .email = "123@example.com"},
                                 New With {.id = 3, .name = "三郎", .age = 28, .email = "test@example.com"}
                                }

                    ' レコード挿入SQL
                    cmd.CommandText = "INSERT INTO users (id, name, age, email)" &
                                      " VALUES (@id, @name, @age, @email)"

                    ' パラメータ作成
                    cmd.Parameters.Add("id", DbType.Int32)
                    cmd.Parameters.Add("name", DbType.String)
                    cmd.Parameters.Add("age", DbType.Int32)
                    cmd.Parameters.Add("email", DbType.String)

                    For Each user In users
                        cmd.Parameters("id").Value = user.id
                        cmd.Parameters("name").Value = user.name
                        cmd.Parameters("age").Value = user.age
                        cmd.Parameters("email").Value = user.email
                        cmd.ExecuteNonQuery()
                    Next

                    ' コミット
                    tx.Commit()
                End Using
            End Using
        End Using
    End Sub

    ''' <summary>
    ''' Selectボタンクリックイベントハンドラ
    ''' </summary>
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        ' コネクション作成
        Using con = New SQLiteConnection(GetConnectionString())
            con.Open()
            Using cmd = con.CreateCommand()
                ' テーブル検索SQL - 全レコードを取得する。（id列を昇順で並べて）
                cmd.CommandText = "SELECT * FROM users ORDER BY id"
                Using dr = cmd.ExecuteReader()
                    While (dr.Read())
                        Console.WriteLine($"id:{dr("id")}, name:{dr("name")}, age:{dr("age")}, email:{dr("email")}")
                    End While
                End Using
            End Using
        End Using
    End Sub

    ''' <summary>
    ''' Updateボタンクリックイベントハンドラ
    ''' </summary>
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        ' コネクション作成
        Using con = New SQLiteConnection(GetConnectionString())
            con.Open()
            Using cmd = con.CreateCommand()
                ' レコード更新SQL - ageが30以上のレコードに対して、nameの前に文字列 ex- を付加する。
                cmd.CommandText = "UPDATE users SET name = 'ex-' || name" &
                                  " WHERE age >= 30"
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

    ''' <summary>
    ''' Deleteボタンクリックイベントハンドラ
    ''' </summary>
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        ' コネクション作成
        Using con = New SQLiteConnection(GetConnectionString())
            con.Open()
            Using cmd = con.CreateCommand()
                ' レコード削除SQL - idが 2 のレコードを削除する。
                cmd.CommandText = "DELETE FROM users WHERE id = 2"
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

    ''' <summary>
    ''' 接続文字列を取得します。
    ''' </summary>
    Private Function GetConnectionString() As String        ' 
        Return New SQLiteConnectionStringBuilder() With {.DataSource = "[DBファイルパス]\test.sqlite3"}.ToString()
    End Function
End Class

実行結果

フォーム上のボタン(Create Table ～ Delete)を上から順番にクリックしていった結果です。

テーブルの作成（Create Table ）

usersテーブルが作成されました。

当たり前ですがレコードはまだありません。

レコードの挿入（Insert ）

3件のレコードが正しく挿入されました。

レコードの検索（Select）

検索結果が表示されました。

レコードの更新（Update）

レコードが正しく更新されました。
→ ageが30以上のレコードに対して、nameの前に文字列 ex- を付加

レコードの削除（Delete）

レコードが正しく削除されました。
→ idが 2 のレコードを削除