Navigating ADO.NET
I have been doing development in both VB6 and VB.NET and I have used ADODB objects in VB6 to handle navigation through recordsets (i.e. MoveFirst, MoveNext, etc. methods) and I have used ADO.NET to processing queries row by row (i.e. for each row in the table. Rows ...)
But now I seem to have arrived at a dilemma. Now I am creating 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 kind of object that supports this functionality, or do I have to resort to using the old ADODB COM object?
Edit: just to clarify, I want the user to be able to navigate on demand by navigating forward or backward. Walking through the lines is an easy task.
source to share
No need to go back to the bad old days. If you can provide some pseudo code example, I can translate to vb.net for you.
This is kind of a common 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 through the results, not by request. So what you want to do is either a) get the results and display only the current rowindex from ds.Tables.Row () for them, or b) execute a new query with each navigation (this is not a very efficient option).
Per comment: No, they don't. But the user will usually not work interactively with such a database. You will need to get your dataset / result table and use buttons to retrieve the corresponding 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 (keep the currently displayed rowindex in your application) then call
- DataTable.Rows (currentRowIndex -1) for previous and
- DataTable.Rows (currentRowIndex +1) for next.
source to share
It all depends on the usage: If you only need to list the results of one or more queries, you must use a datareader. Whether DOK has a hint, it is read-only and only to be fast. http://www.startvbdotnet.com/ado/sqlserver.aspx
If you need to navigate records, you must use a dataset. http://www.c-sharpcorner.com/UploadFile/raghavnayak/DataSetsIn.NET12032005003647AM/DataSetsIn.NET.aspx
The dataset also has the advantage of being "disabled", so you create all the logic and only when you need the data that you call the Fill method. The dataset is full, and then you can start working with the data now disconnected from the DB.
Hope this helps, Bruno Figueiredo http://www.brunofigueiredo.com
source to share
There are many ways to do this in .Net. I like to use DataReader, which can return multiple recordsets. You can loop through your records using DataReader.Read.
One of the advantages of using a DataReader is that it is the only read-only object, so it is fast and lightweight.
To allow the user to navigate through all records, one at a time, you do not want the DataReader to open while the user navigates. you can read DataReader entries into objects. Or, you can get records in the DataSet and display the DataRows from the DataTable one at a time.
I would suggest that if possible, you fetch all the records immediately if there are not many of them. This will keep the repeated calls in the database.
On the other hand, if there are many records, you can get the first few (say 10 or 20) and only get the next set of records with a new call to the database if the user clicks outside of the original set.This is lazy loading.
source to share
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()
source to share