ADO.NET datareader loop by row then column

Question

I need help to develop a logic. Take example data below:

For example:

Emp table

ID  | Fname   | Lname    | Salary  | DeptID
---------------------------------------
1   | John    | Smith    | 50000   | 1
2   | David   | Robinson | 80000   | 2
3   | Frank   | Adams    | 45000   | 10

My codes:

        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("Select * from emp", conn);
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                string EmpID = rdr[0].ToString();
                string Fname = rdr[1].ToString();
                string Lname = rdr[2].ToString();
                double sal = Convert.ToDouble(rdr[3]);
                int deptID = Convert.ToInt(rdr[4]);
            }
        }

What happen is, inside the while (rdr.Read()), the reader will go to the first row and read the first column (ID), second column (Fname), third column (Lname) etc. and once it is done with the first row, it will move to the second row first column, second column and so on.

How do I change my code so that the reader will loop in a way where: It will go to the first column then loop through all row before going to the next column? (The reader must NOT start reading the second column before finishing all rows in the first column.

Note:

The reason I need this kind of looping because I have specific task that requires me (for example, to compare all data in the first column, then taking the outcome from first column and compare to the next column)
There are around 500 columns and 1000 rows in each table, performance is a concern.
I need to reuse the same code for all other 80+ tables, thus I cannot hardcode the column name.

I'm not restricted to use only ADO.NET, but I'm not familiar with Entity Framework, LINQ etc, so if you have any other way to query the database, please let me know

double sal = rdr[3].ToString() that doesn't compile in C#, use the correct types and methods, f.e. rdr.GetDouble — Tim Schmelter
– Tim Schmelter, Commented Mar 13, 2014 at 21:21
@TimSchmelter sorry, I wasn't careful about the datatype while producing the dummy sample data. — C.J.
– C.J., Commented Mar 13, 2014 at 21:22
Why would you want to do this? This isn't how DataReaders work. Heck, it's not how traditional relational databases work. — Matt Hamilton
– Matt Hamilton, Commented Mar 13, 2014 at 21:23
@MattHamilton It is hard to explain... basically I'm dealing with legacy database from the 1960s, so yeah, it is definitely not relational database — C.J.
– C.J., Commented Mar 13, 2014 at 21:24
Don't use a Reader. Use an Adapter and fill a DataTable instead. — LarsTech
– LarsTech, Commented Mar 13, 2014 at 21:27

to StackOverflow · Accepted Answer · 2014-03-13 21:30:04Z

1

A datareader is forward-only so you can't do this.

2.There are around 500 columns and 1000 rows in each table, performance is a concern

If you only have 1000 rows and 500 columns, performance is unlikely to be a concern.

Read all the data into a suitable object (DataTable, or list of Employee objects), then do your stuff.

answered Mar 13, 2014 at 21:30

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C.J. Over a year ago

The problem is, I need to reuse the same code for all 80+ tables, I cannot just create one Employee Object because the other tables may use other classes. Most importantly, I simply CANNOT create a class because I DO NOT know what are the attributes exist in which table. (Unless I go through all 80 tables and 500 column manually to come out 80 classes for each table)

to StackOverflow Over a year ago

@C.J. - then try using a DataTable.

Collectives™ on Stack Overflow

ADO.NET datareader loop by row then column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related