Read Sql Table Chunk
Reads a chunk of rows from a SQL Server table using the OFFSET/FETCH NEXT pattern for paging.
This function uses a static variable offset to maintain paging state across multiple calls. It is suitable for sequential chunked reading scenarios such as data exports or UI pagination. Make sure to call it with ResetOffset set to True before starting a new reading cycle.
VB
Public Function ReadSqlTableChunk(MyConnection As SqlConnection,
TableName As String,
FieldList As String,
TableKey As String,
batchSize As Integer,
ResetOffset As Boolean) As (Table As DataTable, hasMoreRows As Boolean, ErrMessage As String)
Dim dtTable As New DataTable
Dim hasMoreRows As Boolean = True
Dim ErrMessage As String = String.Empty
Static offset As Integer = 0
If ResetOffset Then offset = 0
If batchSize = 0 Then batchSize = 10000
If String.IsNullOrEmpty(FieldList) Then FieldList = "*"
Try
While hasMoreRows
Dim query As String = $"SELECT {FieldList} FROM {TableName} ORDER BY {TableKey} OFFSET {offset} ROWS FETCH NEXT {batchSize} ROWS ONLY"
Using command As New SqlCommand(query, MyConnection)
Using reader As SqlDataReader = command.ExecuteReader()
If reader.HasRows Then
dtTable.Load(reader)
Else
hasMoreRows = False
End If
End Using
End Using
' Prepare the offset for the next call
offset += batchSize
Exit While
End While
Catch exSql As SqlException
ErrMessage = exSql.Message
Catch ex As Exception
ErrMessage = ex.Message
End Try
Return (dtTable, hasMoreRows, ErrMessage)
End Function