vb.net 的sqlite 通用类及使用说明

通用类

Imports System.Data.SQLite

Public Class SqliteDatabaseHelper
    Private ConnectionString As String

    Public Sub New(databasePath As String)
        ConnectionString = $"Data Source={databasePath}"
    End Sub

    Public Function ExecuteQueryNoPage(query As String, parameters As Dictionary(Of String, Object)) As DataTable
        Dim dataTable As New DataTable()

        Try
            Using connection As New SQLiteConnection(ConnectionString)
                connection.Open()

                ' Add parameters to the query
                Using command As New SQLiteCommand(query, connection)
                    For Each parameter In parameters
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value)
                    Next

                    Using reader As SQLiteDataReader = command.ExecuteReader()
                        dataTable.Load(reader)
                    End Using
                End Using
            End Using
        Catch ex As Exception
            ' Handle exceptions as needed
            Console.WriteLine($"Error executing query: {ex.Message}")
        End Try

        Return dataTable
    End Function

    Public Function ExecuteQuery(query As String, parameters As Dictionary(Of String, Object), pageSize As Integer, pageNumber As Integer) As DataTable
        Dim dataTable As New DataTable()

        Try
            Using connection As New SQLiteConnection(ConnectionString)
                connection.Open()

                ' Add parameters to the query
                Using command As New SQLiteCommand(query, connection)
                    For Each parameter In parameters
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value)
                    Next

                    ' Calculate offset and limit for pagination
                    Dim offset As Integer = (pageNumber - 1) * pageSize
                    command.CommandText = $"{query} LIMIT {pageSize} OFFSET {offset}"

                    Using reader As SQLiteDataReader = command.ExecuteReader()
                        dataTable.Load(reader)
                    End Using
                End Using
            End Using
        Catch ex As Exception
            ' Handle exceptions as needed
            Console.WriteLine($"Error executing query: {ex.Message}")
        End Try

        Return dataTable
    End Function

    Public Function ExecuteScalar(query As String, parameters As Dictionary(Of String, Object)) As Object
        Try
            Using connection As New SQLiteConnection(ConnectionString)
                connection.Open()

                Using command As New SQLiteCommand(query, connection)
                    For Each parameter In parameters
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value)
                    Next

                    Return command.ExecuteScalar()
                End Using
            End Using
        Catch ex As Exception
            ' Handle exceptions as needed
            Console.WriteLine($"Error executing query: {ex.Message}")
            Return Nothing
        End Try
    End Function

    Public Function ExecuteNonQuery(query As String, parameters As Dictionary(Of String, Object)) As Integer
        Try
            Using connection As New SQLiteConnection(ConnectionString)
                connection.Open()

                Using command As New SQLiteCommand(query, connection)
                    For Each parameter In parameters
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value)
                    Next

                    Return command.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            ' Handle exceptions as needed
            Console.WriteLine($"Error executing query: {ex.Message}")
            Return -1
        End Try
    End Function

    ' Additional methods for CRUD operations

    Public Function InsertData(tableName As String, values As Dictionary(Of String, Object)) As Integer
        Dim columns As String = String.Join(", ", values.Keys)
        Dim parameters As String = String.Join(", ", values.Keys.Select(Function(key) $"@{key}"))
        Dim query As String = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})"

        Return ExecuteNonQuery(query, values)
    End Function

    Public Function UpdateData(tableName As String, setValues As Dictionary(Of String, Object), condition As String, parameters As Dictionary(Of String, Object)) As Integer
        Dim setClause As String = String.Join(", ", setValues.Select(Function(pair) $"{pair.Key} = @{pair.Key}"))
        Dim query As String = $"UPDATE {tableName} SET {setClause} WHERE {condition}"

        Return ExecuteNonQuery(query, parameters)
    End Function

    Public Function DeleteData(tableName As String, condition As String, parameters As Dictionary(Of String, Object)) As Integer
        Dim query As String = $"DELETE FROM {tableName} WHERE {condition}"

        Return ExecuteNonQuery(query, parameters)
    End Function

    Public Function GetFieldValue(tableName As String, fieldName As String, condition As String, parameters As Dictionary(Of String, Object)) As Object
        Dim query As String = $"SELECT {fieldName} FROM {tableName} WHERE {condition}"

        Return ExecuteScalar(query, parameters)
    End Function
End Class

使用说明

Dim connectionString As String = "Data Source =" + Environment.CurrentDirectory + "/web.db"
Dim dbHelper As New SqliteDatabaseHelper(connectionString)

'无分页查询
Dim query As String = "SELECT ID, Name，age FROM user  WHERE name LIKE %@name%"
Dim parameters As New Dictionary(Of String, Object) From {{"@name", "w"}}
Dim resultDataTable As DataTable = dbHelper.ExecuteQueryNoPage(query, parameters)
'分页
Dim query As String = "SELECT  ID, Name，age FROM user WHERE name LIKE  %@name%"
Dim parameters As New Dictionary(Of String, Object) From{{"@name", "w"}}
Dim pageSize As Integer = 10
Dim pageNumber As Integer = 1
Dim resultDataTable As DataTable = dbHelper.ExecuteQuery(query, parameters, pageSize, pageNumber)

' 插入记录
Dim insertValues As New Dictionary(Of String, Object) From {{"name", "wellfuture"}, {"age", 22}}
Dim insertResult As Integer = dbHelper.InsertData("user", insertValues)

' 更新记录
Dim updateValues As New Dictionary(Of String, Object) From {{"name", "wanghaibin"}}
Dim updateCondition As String = "ID = @id"
Dim updateParameters As New Dictionary(Of String, Object) From {{"@id", 1}}
Dim updateResult As Integer = dbHelper.UpdateData("user", updateValues, updateCondition, updateParameters)

' 删除
Dim deleteCondition As String = "ID = @id"
Dim deleteParameters As New Dictionary(Of String, Object) From {{"@id", 1}}
Dim deleteResult As Integer