vb.net环境下的sqlitehelper分页解决

SQLITEHELPER

Imports System.Data.SQLite

Public Class SQLiteHelper
    Private connection As SQLiteConnection

    Public Sub New(dbPath As String)
        connection = New SQLiteConnection($"Data Source={dbPath};Version=3;")
        connection.Open()
    End Sub

    Public Sub Close()
        connection.Close()
    End Sub

    Public Sub ExecuteNonQuery(sql As String)
        Using command As New SQLiteCommand(sql, connection)
            command.ExecuteNonQuery()
        End Using
    End Sub

    Public Function ExecuteScalar(sql As String) As Object
        Using command As New SQLiteCommand(sql, connection)
            Return command.ExecuteScalar()
        End Using
    End Function

    Public Function ExecuteReader(sql As String) As SQLiteDataReader
        Using command As New SQLiteCommand(sql, connection)
            Return command.ExecuteReader()
        End Using
    End Function

    Public Function ExecuteDataTable(sql As String) As DataTable
        Using command As New SQLiteCommand(sql, connection)
            Using adapter As New SQLiteDataAdapter(command)
                Dim dataTable As New DataTable()
                adapter.Fill(dataTable)
                Return dataTable
            End Using
        End Using
    End Function
End Class

查询分页调用

Public Class PaginationWithSearchExample
    Private Const PageSize As Integer = 10 ' 每页显示的条目数
    Private currentPage As Integer = 1 ' 当前页码
    Private dbPath As String = "your_database_path_here" ' 替换为你的实际数据库路径

    Private Sub LoadData(searchKeyword As String)
        Using dbHelper As New SQLiteHelper(dbPath)
            ' 构建查询条件
            Dim condition As String = If(String.IsNullOrWhiteSpace(searchKeyword), "", $"WHERE YourColumnName LIKE '%{searchKeyword}%'")

            ' 计算要跳过的记录数
            Dim offset As Integer = (currentPage - 1) * PageSize

            ' 构建带查询条件的分页查询的 SQL 语句
            Dim sql As String = $"SELECT * FROM YourTableName {condition} LIMIT {PageSize} OFFSET {offset}"

            ' 使用 SQLiteHelper 执行查询
            Dim dataTable As DataTable = dbHelper.ExecuteDataTable(sql)

            ' 在这里处理查询结果，例如将结果显示在界面上
            ' ...

            ' 如果需要，你还可以获取满足查询条件的总记录数以便计算总页数
            ' 例如：Dim totalRecords As Integer = GetTotalRecordsCount(dbHelper, condition)

            ' 更新界面显示当前页码和总页数
            ' UpdateUI(currentPage, totalRecords)
        End Using
    End Sub

    ' 示例：获取满足查询条件的总记录数的方法
     Private Function GetTotalRecordsCount(dbHelper As SQLiteHelper, condition As String) As Integer
         Dim sql As String = $"SELECT COUNT(*) FROM YourTableName {condition}"
         Return Convert.ToInt32(dbHelper.ExecuteScalar(sql))
     End Function

    ' 示例：更新界面显示的方法
     Private Sub UpdateUI(currentPage As Integer, totalRecords As Integer)
         
    End Sub

    ' 示例：处理翻页按钮点击事件
    Private Sub NextPageButton_Click(sender As Object, e As EventArgs) Handles NextPageButton.Click
         currentPage += 1
         LoadData(SearchTextBox.Text)
    End Sub

     Private Sub PreviousPageButton_Click(sender As Object, e As EventArgs) Handles PreviousPageButton.Click
         If currentPage > 1 Then
             currentPage -= 1
             LoadData(SearchTextBox.Text)
         End If
     End Sub

    ' 示例：处理搜索按钮点击事件
     Private Sub SearchButton_Click(sender As Object, e As EventArgs) Handles SearchButton.Click
         currentPage = 1 ' 重新从第一页开始显示结果
         LoadData(SearchTextBox.Text)
    End Sub
End Class