how_to_sqlite.vb

gistfile1.vb

Option Explicit On
Option Strict On

Public Class clsSQLite

    Public Enum ErrArgs
        None = 0
        NotFoundDBFile = 1
        AlreadyExistsDBFile = 2
    End Enum

    Public Shared Sub DeleteDBFile(ByVal DBFilename As String)
        'コネクションプール切断には、下記命令のほか、ガベージコレクトしてメモリ接続を切ることが必要
        System.Data.SQLite.SQLiteConnection.ClearAllPools()
        GC.Collect()
        GC.WaitForPendingFinalizers()

        'ファイル
        If System.IO.File.Exists(DBFilename) Then
            System.IO.File.Delete(DBFilename)
        End If
    End Sub


#Region "ビジネスロジック（SQL実行・結果取得）"
    ''' <summary>
    ''' SQL実行（INSERT/UPDATE/DELETE）
    ''' </summary>
    ''' <param name="DBFilename"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function Vacuum(ByVal DBFilename As String) As Boolean
        Try
            'DisposeしてもSQLITEのファイルハンドルは外れないので、
            'コネクションプールの削除とｶﾞﾍﾞｰｼﾞｺﾚｸﾄ
            System.Data.SQLite.SQLiteConnection.ClearAllPools()
            GC.Collect()
            GC.WaitForPendingFinalizers()

            Using conn As New System.Data.SQLite.SQLiteConnection("Data Source=" & DBFilename & ";Version=3;New=False;Compress=True;SyncMode=Full;JournalMode=Wal;")
                conn.Open()
                Using cmd As System.Data.SQLite.SQLiteCommand = conn.CreateCommand()
                    cmd.CommandText = "vacuum;"
                    cmd.ExecuteNonQuery()
                End Using
            End Using
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    ''' <summary>
    ''' SQL実行（Select）
    ''' </summary>
    ''' <param name="DBFilename"></param>
    ''' <param name="p_SQL"></param>
    ''' <param name="p_DataSet"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function SelectSQL(ByVal DBFilename As String, ByVal p_SQL As String, ByRef p_DataSet As System.Data.DataTable) As Boolean
        Using conn As New System.Data.SQLite.SQLiteConnection("Data Source=" & DBFilename & ";Version=3;New=False;Compress=True;SyncMode=Full;JournalMode=Wal;")
            Dim da As New System.Data.SQLite.SQLiteDataAdapter(p_SQL, conn)
            da.Fill(p_DataSet)
        End Using
        Return True
    End Function

    ''' <summary>
    ''' SQL実行（INSERT/UPDATE/DELETE）
    ''' </summary>
    ''' <param name="DBFilename"></param>
    ''' <param name="p_SQL"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecSQL(ByVal DBFilename As String, ByVal p_SQL As String, Optional ByVal pNew As Boolean = False) As Boolean
        Try
            Using conn As New System.Data.SQLite.SQLiteConnection("Data Source=" & DBFilename & ";Version=3;New=" & If(pNew, "True", "False") & ";Compress=True;SyncMode=Full;JournalMode=Wal;")
                conn.Open()
                Using cmd As System.Data.SQLite.SQLiteCommand = conn.CreateCommand()
                    cmd.CommandText = p_SQL
                    cmd.ExecuteNonQuery()
                End Using
            End Using
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function


    ''' <summary>
    ''' SQL実行（INSERT/UPDATE/DELETE）
    ''' </summary>
    ''' <param name="DBFilename"></param>
    ''' <param name="p_SQL"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecSQL_FOR_transaction(ByVal DBFilename As String, ByVal p_SQL As String, Optional ByVal pNew As Boolean = False) As Boolean
        Try
            Using conn As New System.Data.SQLite.SQLiteConnection("Data Source=" & DBFilename & ";Version=3;New=" & If(pNew, "True", "False") & ";Compress=True;SyncMode=Full;JournalMode=Wal;")
                conn.Open()
                Using cmd As System.Data.SQLite.SQLiteCommand = conn.CreateCommand()

                    Dim p_SQLSplit As String() = Split(p_SQL, vbCrLf)

                    Try
                        'トランザクションの開始
                        cmd.Transaction = conn.BeginTransaction()
                        For Each Ar In p_SQLSplit
                            If Ar = "" Then Exit For
                            cmd.CommandText = Ar
                            cmd.ExecuteNonQuery()
                        Next

                    Catch ex As System.Data.SQLite.SQLiteException
                        cmd.Transaction.Rollback()
                        Throw ex
                    End Try
                    cmd.Transaction.Commit()
                    'コミット
                End Using
            End Using
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

#End Region

    'isolation_levelの設定により、で、COMMITしないと使えないトランザクションとか作れる
    'https://yatt.hatenablog.jp/entry/20120304/1330832666
    '排他ロック：exclusive

End Class

Usage:

テーブルの作成なんか

    Public Shared Function CreateTable() As Boolean
        Dim sql = <s><![CDATA[
        CREATE TABLE DEPT (
        	'DEPTNO'	TEXT UNIQUE,
        	'DNAME'	TEXT,
        	'LOC'	TEXT
        );
        ]]></s>.Value()
        'テーブルの作成
        Return clsSQLite.ExecSQL(”D:\test.db”, sql, True)
    End Function

これの使い方がわかればだいたいほかも似たような感じです。