How to loop through the records of a table using ADO.Net

  • Thread starter Thread starter =?iso-8859-1?Q?Jan_Weing=E4rtner?=
  • Start date Start date
?

=?iso-8859-1?Q?Jan_Weing=E4rtner?=

Hi,

the new ADO.Net does not support SQL cursors.
So how can i loop through the entire set of records of a table.
The fetching of all data into a DataSet is (of course) not
a solution.
Is there a recommended way?

thanks and best regards,
Jan
 
Hi Jan,

You might use DataReader class.
It is a forward only read only cursor
Create a command object and do something like:
IDataReader rdr = command.ExecuteReader();
try
{
while (rdr.Read())
{
IDataRecord rc = (IDataRecord)rdr;
// read data using rc methods
}
finally
{
rdr.Close();
}
 
Maurice,
If you only want to read the data you can use a DataReader
Click to expand...

A DataReader also needs an SQL statement like "SELECT * from ...".
AFAIK, the method DataReader.Read() fetches the data from the local main
memory and not directly from the data base.
To loop through all records !!without saving the entire data set in the
main memory!! i would need something like "FetchNextRecord()" which works
on the data base. A "SELECT * from tableName" is not a good idea if you
have a table with 100.000 records.

Jan
 
Jan,

A DataReader reads the data into memory but doesn't keep it in memory it
gives you a record at the time and you can do with that record whatever you
like. For example a DataAdaptor uses a DataReader behind the scene to fill a
DataTable. It does however mean that all data comes across the network to
the local PC, if that is a problem you need to use a stored procedure and
keep all processing in SQL server itself.

Maurice
www.TheProblemSolver.nl
 
An example:
I want the user enable to navigate through - let's say his adresses - by
using functions like "Go to first/next/previous/last adress".
A DataReader reads the data into memory but doesn't keep it in memory it
gives you a record at the time
Click to expand...

But where does _this_ record come from? It comes not _directly_
from the database, but from an internal buffer, used by DataReader.
If you create a data reader based on an SqlCommand object with the SQL
statement "SELECT * from Adresses", the database server gives you
the _entire_ result set.
This is applyable for small numbers of records only.

ODBC, ADO 2.x and the (older) Borland database access models are supporting
server side cursors and thats why also the "first/next/prev/last" commands.
It does however mean that all data comes across the network to
Click to expand...

Yes, we talk about a client side data manipulation, but it should be possible
to fetch the data in small pieces (record after record) and not in one step.

I think, the basic problem is the connection-less type of the new ADO.Net
acces model.

Jan
 
Hi Jan,

I do not know if you use C# or VB.net and therefore you get an answer in
VB.net although it is in C# the same because the important part is the
dataset.

You can loop very easy through a dataset (which holds the tables)
The first item from the first row in the first datatable in a dataset is

ds.tables(0).rows(0).items(0)

The length in rows of a table, I think you understand it already

ds.tables(0).rows.count

Therefore the last row is
ds.tables(0).rows(ds.tables(0).rows.count)

to loop through a table in vb, and that is the only vb code I see except
that in C it is [0]

for i as integer = ds.tables(0).rows(0).count-1
'do something
next

I hope that with this it becomes clear to you?
If not feel free to ask.

Cor
 
Jan,

A DataReader doesn't retrieve all the data at once, it will most likely
retreive more than a single record at the time but not a complete result
set. However it doesn't sound like a solution to your problem as a
DataReader is a read-only and forward only object so it doesn't allow the
user to navigate. .NET doesn't natively allow you to use a connected
resultset for anything else than read-only forward scrolling, the only
connected object is the DataReader.

What I do in a scenario like this is:
- Get a filter criteria from the user so I can limit the number of record.
- Create an fill a DataTable with a few fields (including the primary key)
based on the filter from step one.
- In the PositionChanged event fill a second datatable with the complete
current record base on the primary key from the table in step 2. This table
can be updated and saved etc.

An alternative is to use the ADO 2.7 recordset that does allow using a
server side cursor.

Maurice
www.TheProblemSolver.nl
 
Hi Jan,

I need more specs to understand what you're after.

If you want to loop through all the data, perhaps, depending upon the user's
wishes, you have to load the table into memory. If you wish to make changes
to it, it has to be a data adapter, not a data reader. Even if you wish to
only read, if you want to move up or down (these are really misnomers), then
it can't be via a data reader, as that is forward only.

If the table is large (say 100,000 rows +), you could try to find ways to
narrow the cols and filter the table before loading it into a
dataset/datatable.

Pls give us more specs and we might be able offer more help.

Bernie Yaeger
 
Hi and thanks for all replys.
All my misgivings seems to be confirmed.
Unlike ODBC, BDE and ADO 2.x the new ADO.Net does not support server
side cursors (or "record pointers" as every file based database supports).
A record loop for large amounts of data has to be simulated in a more
ore less awkward way.

best regards,
Jan
 
Wow. This thread has really generated a flood of information--much of it accurate, some of it not so...

I think the fundamental problem here is not that ADO.NET does not support cursors (because it does and I'll get to that later), but that you want to "loop" through a table. Let's talk a moment about the reasons behind wanting to visit each row.
a.. Suppose the user wants to search for something and feels that they have to visit each row in a table looking for a match. If you have a table larger than a few dozen rows would you simply display all of the rows on the screen and let the user visually search? Most of us wouldn't unless you have a screen with really tiny fonts and issue the user a magnifying glass. We would ask for a search criteria or two and use these as WHERE clause arguments to focus the search on a few (dozen) rows. This does not require that you move the entire table over the wire and return it to the user. There are cases where an application needs to perform complex operations (such as graphical representation of data), but again, being selective about what you return is important. Most of the time, the user can help narrow the search. It's far faster (and easier on all other users) if you only fetch what the user can deal with at the moment. You can speed up the process by requerying for more data behind the scenes while they peruse the dozen or so rows you just presented. Having fetched the entire table only to have the user abandon the search after the first couple of pages is a big waste of resources.
b.. Suppose you want to manipulate the rows in a table--that is, change values in selected rows based on some criteria. For example, you wanted to find any Part in the Parts table that needed to be reordered and execute some code to start the reorder process. In this case all of the processing can be done server-side (assuming you aren't using JET). Simply write a SP that performs the work on the server--updating or inserting rows where necessary. In this case no data is brought to the client--only the results of the operation. While it's rare to change all the rows in a table, this can be easily done (even with JET) by creating an UPDATE query that makes the changes (again without bringing the rows to the client (except for JET which does all of its physical IO over the wire).
c.. If you need to produce a report, there are times when the report needs to perform calculations or aggregates on all of the rows in a table. In this case we suggest that you use a server-side reporting engine like Reporting Services or use "roll-up" techniques to create a summary table using server-side code that eliminates the need to bring the raw data to the client for processing.
The point here is that when using SQL Server (or Oracle and other real "server" DBMSs) that you should avoid bringing the data to the client for processing. Do the processing on the server. It's faster, it requires less network overhead, it's less likely to lock up the data for others, and it's more scalable. Capture parameters from the user and bring "results" to the client--don't make them do all the work.

As for creating cursors using ADO.NET--while ADO.NET does not have a server-side cursor implementation in the current version (Whidbey does), you can create cursors today by using the CREATE CURSOR ANSI SQL calls. I explain how to do this in my session at VSLive in San Francisco next month. See http://www.betav.com/pinnacle.htm "...Doing the Impossible (Again)" for details (you might have to subscribe to get a copy).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi Jan,
A record loop for large amounts of data has to be simulated in a more
ore less awkward way.
Click to expand...

I hope I do not tread on someones toes but using a database with a reader
looks to me something as using a database in a Cobol situation.

It sounds the same to me as someone who once said to me in past: "with a
database I cannot fix a hole in a punching card".

For me (and also for others you saw in this thread) are the datatables the
way to go.

Just my 2 Eurocents

Cor
 
To add a little thing, before it is misunderstood.

The reader is of course very useful in a real streaming situation.

Cor
 
Back
Top