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
Dim sql As String = $"SELECT * FROM YourTableName {condition} LIMIT {PageSize} OFFSET {offset}"
Dim dataTable As DataTable = dbHelper.ExecuteDataTable(sql)
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