Skip to content

Instantly share code, notes, and snippets.

@lunark
Created May 28, 2021 13:03
Show Gist options
  • Save lunark/5c2546ebfbd7a877efc16f547354dede to your computer and use it in GitHub Desktop.
Save lunark/5c2546ebfbd7a877efc16f547354dede to your computer and use it in GitHub Desktop.
how_to_sqlite.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
@lunark
Copy link
Author

lunark commented Jun 3, 2021

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment