{"id":842,"date":"2025-10-30T15:28:01","date_gmt":"2025-10-30T14:28:01","guid":{"rendered":"https:\/\/www.silverice.it\/IpercubeTechWP1\/?page_id=842"},"modified":"2025-10-30T15:28:01","modified_gmt":"2025-10-30T14:28:01","slug":"readsqltablechunk","status":"publish","type":"page","link":"https:\/\/www.silverice.it\/IpercubeTechWP1\/ipercube-utilities\/sql-functions\/readsqltablechunk\/","title":{"rendered":"Read Sql Table Chunk"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Reads a chunk of rows from a SQL Server table using the OFFSET\/FETCH NEXT pattern for paging.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This function uses a static variable&nbsp;<strong>offset<\/strong>&nbsp;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&nbsp;<strong>ResetOffset<\/strong>&nbsp;set to&nbsp;<strong>True<\/strong>&nbsp;before starting a new reading cycle.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:75%\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column page-snippet is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:100%\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-Geist-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-Geist-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">VB<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>    Public Function ReadSqlTableChunk(MyConnection As SqlConnection, \n                                      TableName As String, \n                                      FieldList As String, \n                                      TableKey As String, \n                                      batchSize As Integer, \n                                      ResetOffset As Boolean) As (Table As DataTable, hasMoreRows As Boolean, ErrMessage As String)\n\n        Dim dtTable As New DataTable\n        Dim hasMoreRows As Boolean = True\n        Dim ErrMessage As String = String.Empty\n\n        Static offset As Integer = 0\n        If ResetOffset Then offset = 0\n        If batchSize = 0 Then batchSize = 10000\n\n        If String.IsNullOrEmpty(FieldList) Then FieldList = \"*\"\n\n        Try\n            While hasMoreRows\n                Dim query As String = $\"SELECT {FieldList} FROM {TableName} ORDER BY {TableKey} OFFSET {offset} ROWS FETCH NEXT {batchSize} ROWS ONLY\"\n\n                Using command As New SqlCommand(query, MyConnection)\n                    Using reader As SqlDataReader = command.ExecuteReader()\n                        If reader.HasRows Then\n                            dtTable.Load(reader)\n                        Else\n                            hasMoreRows = False\n                        End If\n                    End Using\n                End Using\n\n                ' Prepare the offset for the next call\n                offset += batchSize\n                Exit While\n            End While\n\n        Catch exSql As SqlException\n            ErrMessage = exSql.Message\n        Catch ex As Exception\n            ErrMessage = ex.Message\n        End Try\n\n        Return (dtTable, hasMoreRows, ErrMessage)\n    End Function<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">Public Function <\/span><span style=\"color: #DCDCAA\">ReadSqlTableChunk<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">MyConnection<\/span><span style=\"color: #D4D4D4\"> As <\/span><span style=\"color: #4EC9B0\">SqlConnection<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                      TableName As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                      FieldList As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                      TableKey As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                      batchSize As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                      ResetOffset As <\/span><span style=\"color: #4EC9B0\">Boolean<\/span><span style=\"color: #D4D4D4\">) As (<\/span><span style=\"color: #9CDCFE\">Table<\/span><span style=\"color: #D4D4D4\"> As <\/span><span style=\"color: #4EC9B0\">DataTable<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> hasMoreRows<\/span><span style=\"color: #D4D4D4\"> As <\/span><span style=\"color: #4EC9B0\">Boolean<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> ErrMessage<\/span><span style=\"color: #D4D4D4\"> As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> dtTable <\/span><span style=\"color: #D4D4D4\">As<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #D4D4D4\">DataTable<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> hasMoreRows <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">Boolean<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #569CD6\"> True<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> ErrMessage <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #DCDCAA\">String<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #569CD6\">Empty<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Static offset As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> ResetOffset <\/span><span style=\"color: #C586C0\">Then<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">offset<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">batchSize<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Then<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">batchSize<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #B5CEA8\">10000<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">String<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">IsNullOrEmpty<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">FieldList<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #C586C0\">Then<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #9CDCFE\">FieldList<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #CE9178\">&quot;*&quot;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Try<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">While<\/span><span style=\"color: #D4D4D4\"> hasMoreRows<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> query <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> = $<\/span><span style=\"color: #CE9178\">&quot;SELECT {FieldList} FROM {TableName} ORDER BY {TableKey} OFFSET {offset} ROWS FETCH NEXT {batchSize} ROWS ONLY&quot;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                Using command As<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #DCDCAA\">SqlCommand<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">query<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> MyConnection<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    Using reader As <\/span><span style=\"color: #4EC9B0\">SqlDataReader<\/span><span style=\"color: #D4D4D4\"> = command.<\/span><span style=\"color: #DCDCAA\">ExecuteReader<\/span><span style=\"color: #D4D4D4\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> reader.HasRows <\/span><span style=\"color: #C586C0\">Then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            dtTable.<\/span><span style=\"color: #DCDCAA\">Load<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">reader<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #C586C0\">Else<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                            <\/span><span style=\"color: #9CDCFE\">hasMoreRows<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #569CD6\"> False<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                        <\/span><span style=\"color: #C586C0\">End If<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Using<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Using<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #6A9955\">&#39; Prepare the offset for the next call<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #9CDCFE\">offset<\/span><span style=\"color: #D4D4D4\"> +=<\/span><span style=\"color: #9CDCFE\"> batchSize<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                Exit <\/span><span style=\"color: #C586C0\">While<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">While<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Catch exSql As <\/span><span style=\"color: #4EC9B0\">SqlException<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #9CDCFE\">ErrMessage<\/span><span style=\"color: #D4D4D4\"> = exSql.Message<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Catch ex As <\/span><span style=\"color: #4EC9B0\">Exception<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #9CDCFE\">ErrMessage<\/span><span style=\"color: #D4D4D4\"> = ex.Message<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Try<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #9CDCFE\">dtTable<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> hasMoreRows<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> ErrMessage<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">End Function<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Reads a chunk of rows from a SQL Server table using the OFFSET\/FETCH NEXT pattern for paging. This function uses a static variable&nbsp;offset&nbsp;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&nbsp;ResetOffset&nbsp;set to&nbsp;True&nbsp;before starting a new reading [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":810,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-842","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/842","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/comments?post=842"}],"version-history":[{"count":2,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/842\/revisions"}],"predecessor-version":[{"id":844,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/842\/revisions\/844"}],"up":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/810"}],"wp:attachment":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/media?parent=842"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}