ADO.NET record navigation

Question

I've done development in both VB6 and VB.NET, and I've used ADODB objects in VB6 to handle recordset navigation (i.e. the MoveFirst, MoveNext, etc. methods), and I have used ADO.NET to handle queries in a row-by-row nature (i.e For Each Row In Table.Rows ...)

But now I seem to have come to a dilemma. I am now building a program in VB.NET where I need to use the equivalent functionality of the Move commands of the old Recordset object. Does VB.NET have some sort of object that supports this functionality, or do I have to resort to using the old ADODB COM object?

Edit: Just for clarification, I want the user to be able to navigate through the query moving forwards or backwards. Looping through the rows is a simple task.

Answer 1

In .Net, there are many ways to do this. One that I like is to use a DataReader, which can return multiple recordsets. You can loop through its records using While DataReader.Read.

One of the advantages of using DataReader is that it is a forward-only, read-only object, so it's fast and light-weight.

To allow the user to navigate through all of the records, one at a time, you do not want to hold a DataReader open while the user navigates. you can read the DataReader records into objects. Or, you can retrieve the records into a DataSet, and display the DataRows from the DataTable one at a time.

I would suggest that, if possible, you retrieve all of the records at once if there are not too many. This will save repeated calls to the database.

On the other hand, if there are a lot of records, you could retrieve the first few (say, 10 or 20) and only retrieve the next set of records with a new database call if the user clicks beyond the initial set. This is lazy loading.

Answer 2

There is no need to go back to the bad old days. If you can give a pseudo code example, I can translate to vb.net for you.

This is kind of a generic way to do it.

Dim ds as DataSet

'populate your DataSet'

For each dr as DataRow in ds.Tables(<tableIndex>).Rows
  'Do something with the row'

Next

Per Edit 1: The user will navigate the results, not the query. So what you want to do is either a) get the results and display only the current rowindex of ds.Tables.Row() to them, or b) execute a new query with each navigation (not a real well performing option.)

Per comment: No, they havent. But the user usually will not be working interactively with the database like this. You will need to get your dataset / table of the results, and use the buttons to retrieve the relevant row from the dataset/table.

• The First Row is DataTable.Rows(0)
• The Last Row is DataTable.Rows(DataTable.Rows.Count-1)
• • for any row in between (store the currently displayed rowindex in your app), then call
• DataTable.Rows(currentRowIndex -1) for previous and
• DataTable.Rows(currentRowIndex +1) for next.

Answer 3

Here's a quick example of using the datareader:

            Dim cmd As New OleDb.OleDbCommand(sql, Conn) 'You can also use command parameter here
            Dim dr As OleDb.OleDbDataReader
            dr = cmd.ExecuteReader

            While dr.Read

    ‘Do something with data
    ‘ access fields
    dr("fieldname")
    ‘Check for null
    IsDBNull(dr("fieldname"))

            End While

            dr.Close()

Answer 4

It all depends on the usage: If you need only to list the results of one or more queries you should use the datareader. Has DOK pointed out, it's read-only and foward-only so it's fast. http://www.startvbdotnet.com/ado/sqlserver.aspx

If you need to navigate thou the records you should use a dataset. http://www.c-sharpcorner.com/UploadFile/raghavnayak/DataSetsIn.NET12032005003647AM/DataSetsIn.NET.aspx

The dataset also has the advantage of working "disconnected", so you build all the logic, and only when you need the data you call the Fill method. The dataset is populated and then you can start working with the data, now disconnected from the DB.

Hope it helps, Bruno Figueiredo http://www.brunofigueiredo.com