VB.net - SQLite query response turning empty after first interaction-CodePudding

so I'm using SQLite in a VB.net project with a populated database. I'm using the Microsoft.Data.Sqlite.Core and System.Data.SQLite NuGet package libraries. So the problem presents when I'm trying to get the result of a query. At first the SQLiteDataReader gets the response and all the elements of the desired table. I know this cause in the debugger I have a breakpoint after the setting the object and when I check the parameters of the SQLiteDataReader object the Result View shows all the elements of my table, but as soon as I remove the mouse from the object and check it again the Result View turns out empty, without even resuming with the next line of code. Does anyone know if its a known bug or something cause Ive used this exact method of querying a table in another project and it works.

The code:

Public Function RunQuery(com As String)
   If CheckConnection() Then
      command.CommandText = com
      Dim response As SQLiteDataReader
      response = command.ExecuteReader
      Dim len As Integer = response.StepCount
      Dim col As Integer = response.FieldCount
      Dim resp(len, col) As Object

      For i = 0 To col - 1
         Using response
             response.Read()
             For j = 0 To len - 1
                 resp(i, j) = response.GetValue(j)
             Next
         End Using
      Next

Debugger with populated result view Debugger with empty result view

edit: added the for loop to show that its not only on the debugger that the result view is empty. When using response.Read() it throws an exception "System.InvalidOperationException: 'No current row'"

CodePudding user response：

As I have told you in the comment, a DataReader derived class is a forward only retrieval object. This means that when you reach the end of the records returned by the query, that object is not capable to restart from the beginning.

So if you force the debugger to enumerate the view the debugger reaches the end of the results and a second attempt to show the content of the reader fails.

For the other part of your problem, it is caused by a misunderstanding on how to work on the reader. You should loop over the Read result not using a StepCount property. This is not standard and other data providers don't support it. Probably is present in SQLiteProvider because it is relatively easy to count the number of records while other providers don't attempt do calculate them for performance reason.

However there are other ways to read from the reader. One of them is to fill a DataTable object with its Load method that conveniently take a DataReader

 Dim data As DataTable = New DataTable()
 Using response
     data.Load(response)
 End Using
' Now you have a datatable filled with your data. 
' No need to have a specific array

A DataTable is like an array where you have Rows and Columns instead of indexes to iterate over.