CODEWITHSUNDEEP
c#sql-serversqldatareader
user9517834
user9517834

Reputation: 21

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

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?

Upvotes: 2

Views: 8792

Answers (1)

jason.kaisersmith
jason.kaisersmith

Reputation: 9650

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.

Upvotes: 5

Related Questions