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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

老大白菜

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值