CODEWITHSUNDEEP
c#.netado.net
C.J.
C.J.

Reputation: 3527

ADO.NET datareader loop by row then column

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:

  1. 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)
  2. There are around 500 columns and 1000 rows in each table, performance is a concern.
  3. 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

Upvotes: 0

Views: 1603

Answers (1)

to StackOverflow
to StackOverflow

Reputation: 124696

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.

Upvotes: 1

Related Questions