How to read multiple rows and multiple columns using SqlDataReader in C#

Question

I need to know how I can get the values returned by multiple rows and multiple columns of a query using SqlDataReader in C#. Data returned through the table:

see results of table here

I want to display all of these in labels. Here is the code I have so far:

try
{
    SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
    connection.Open();

    string query = ("select cardname,cardnumber,expiry,cardballance from vwallet where username='" + uname + "'");

    SqlCommand cmd = new SqlCommand(query, connection);
    SqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())
    {
         //cardname = reader[0].ToString();
         //cardnumber = reader[1].ToString();
         //expiry = reader[2].ToString();
         //cardballance = reader[3].ToString();
         reader.
    }
}

Note: I want to display the result returned by the query i.e cardnames, cardnumbers, expiry and cardballance into labels.

My current understanding is that the code I wrote will read only one row's column and assign to variables (declared already in the code, not pasted declaration here).

How can I read all the data returned from the table depicted above?

jason.kaisersmith · Accepted Answer · 2020-01-12 07:49:51Z

5

You are almost there. You just need an array or collection to store the rows in.

public class MyCard
{
    public string Name { get; set; }
    public string Number { get; set; }
    public string Expiry { get; set; }
    public string Balance { get; set; }
   //Please note:  This needs updating to match the data type used in your DB table.
   //I have used string to show you a simple example.
}

Then update your code to include:

SqlDataReader reader = cmd.ExecuteReader();
List<MyCard> MyCardList = new List<MyCard>();
while (reader.Read())
{
     MyCard mycard = new MyCard();
     mycard.Name = reader[0].ToString();
     mycard.Number = reader[1].ToString();
     mycard.Expiry = reader[2].ToString();
     mycard.Balance = reader[3].ToString();
     MyCardList.Add(mycard);
}
//Remember to close the reader and dispose of objects correctly.

Then you have a list of MyCard objects with all your data.

answered Jan 12, 2020 at 7:49

jason.kaisersmith

9,7233 gold badges35 silver badges61 bronze badges

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

2 Comments

marc_s Over a year ago

And the code reading from the SqlDataReader would also need to take into account that maybe some of the columns will be returned as NULL and this the reader[x].ToString() would crash .....

TylerH Over a year ago

Not necessarily. OP knows the constraints of their source data; we should not presume to solve errors OP did not have.

Collectives™ on Stack Overflow

How to read multiple rows and multiple columns using SqlDataReader in C#

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