How to access specific row in datareader?

Question

I got OutOfMemoryException while download bulk reports . So I alter the code by using DataReader instead of DataSet .

How to achieve below code in DataReader because am helpless to use any DataTables & DataSet in my coding.

if (dataSet.Tables[0].Rows[i + 1]["Sr No"].ToString() == dataSet.Tables[0].Rows[i]["Sr No"].ToString())

Retrieving Data Using a DataReader - MSDN - Microsoft https://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx — andy, Aug 02 '16 at 06:50
https://stackoverflow.com/questions/1855556/datareader-best-practices — andy, Aug 02 '16 at 06:51
@LucMorin i mean DataReader is read Records in sequential process from record set. while i read 3 record i want to know the value of 4 th record SRNO column. so how to achieve this — wiki, Aug 02 '16 at 07:02
As` DataReader` is a read and forward only connection oriented architecture , we can not read more than one record at a time. So it will be better if you use `DataSet` and find out what causes the error `OutOfMemoryException`. — Rojalin Sahoo, Aug 02 '16 at 07:34
@RojalinSahoo Loading (and keeping) large number of rows in memory is probably what is causing the `OutOfMemoryException` in the first place. If that's actually the case, then using `DataSet` would be exactly the wrong thing to do. — Branko Dimitrijevic, Aug 02 '16 at 08:34
@BrankoDimitrijevic I agree with you but he wants to use multiple record at a time , so I thought may `DataSet` Will work. The best method will be restrict duplicate value insertion if you have authorization of inserting or else do select only distinct value from `DataBase`. — Rojalin Sahoo, Aug 02 '16 at 09:38

monstertjie_za · Accepted Answer · 2016-08-02T07:30:52.650

I don't think you can access a row that has not been executed. The way I understand it, the DataReader returns row by row as it reads it from the Database.

You can try the following:

This will loop through each row that the DataReader will return in the dataset. Here you can check certain values/conditions, as follow:

while (dataReader.Read())
{
    var value = dataReader["Sr No"].ToString();
    //Your custom code here
}

Alternatively, you can also specify the column index, instead of the name, if you wish to do so, as follow:

while (dataReader.Read())
{
    var value = dataReader.GetString(0); //The 0 stands for "the 0'th column", so the first column of the result.
    //Your custom code here
}

UPDATE Why don't you place all the values read from the DataReader, into a list, and you can use this list afterwards for comparison between values if you need it.

Whatever u suggested is fine but for eg. if i go to while loop for first time it retrieves 1 row [sr.no] column . BUT how do i get 2 row [srno] column . — wiki, Aug 02 '16 at 07:04
I don't think you can access a row that has not been executed. The way I understand it, the DataReader returns row by row as it reads it from the Database. — monstertjie_za, Aug 02 '16 at 07:13

Branko Dimitrijevic · Answer 2 · 2016-08-02T08:38:01.673

Don't filter rows at the client level.

It's better to search for the right rows on the server, before they even reach the client, instead of fetching all the rows into the client and then filtering there. Simply incorporate your search criteria in the SQL query itself, and then fetch the rows that the server has already found for you.

Doing that:

Allows the server to use indexes and other database structures to identify the "interesting" rows potentially much faster than linearly searching through all rows.
You network connection between client and server will not be saturated by gazillion rows, most of which will be discarded at the end anyway.
May allow your client to deal with one row at a time in a simple way (e.g. using DbDataReader), as opposed to additional processing and/or storing multiple rows in memory (or even all rows, as you did).

In your particular case, looks like you'll need a self-join, or perhaps an analytic (aka. "window") function. Without knowing more about your database structure or what are you actually trying to accomplish, I can't know how your exact query is going to look like, bit it will probably be something along these lines:

-- Sample data...

CREATE TABLE T (
    ID int PRIMARY KEY,
    SR_NO int
);

INSERT INTO T VALUES
    (1, 100),
    (2, 101),
    (3, 101),
    (4, 100);

-- The actual query...

SELECT
    *
FROM (
    SELECT
        *,
        LEAD(SR_NO) OVER (ORDER BY ID) NEXT_SR_NO
    FROM
        T T1
) Q
WHERE
    SR_NO = NEXT_SR_NO;

Result:

ID  SR_NO   NEXT_SR_NO
2   101     101

score 0 · Answer 3 · edited Jun 30 '19 at 06:44

0

You can do this by using a do-while loop. Before checking the condition, the next row will be read and you can also access that row. Try this code :

do
{
    your code
}
while(myreader.Read())

edited Jun 30 '19 at 06:44

BenT

3,172
3
18
38

answered Jun 30 '19 at 04:29

Aqib Ali

1

How to access specific row in datareader?

3 Answers3