3

I have a query with a join like this:

string cmdString = "select tblPackages.*, tblPackageTypes.Name from tblPackages join tblPackageTypes on tblPackages.TypeId = tblPackageTypes.Id";

Now I use this SQL command and get back a datareader like this:

SqlCommand cmd = new SqlCommand(cmdString,con);
SqlDataReader dr;

I want to populate values of data reader to a list. I referred to this link but I didn't get what I needed.

I have following columns output from the sql

Id, TypeId, AllowedSMS, TimeSpan, Price, TypeName.

I just want to pass them in the list. Please help me.. and i am new to asp.net

Community
  • 1
  • 1
Robz
  • 1,140
  • 3
  • 14
  • 35

1 Answers1

11

Well, in your case, you need a class that can hold your columns:

public class MyClass
{
   public int Id { get; set; }
   public int TypeId { get; set; }
   public bool AllowedSMS { get; set; }
   public string TimeSpan { get; set; }  
   public decimal Price { get; set; }
   public string TypeName { get; set; } 
}

Of course, since you didn't define what datatype those columns are, this is just a guess - you might need to adapt to your concrete needs.

And now, you need to iterate over the SqlDataReader and fetch the values from the data reader and populate your newly created class:

using (SqlDataReader dr = cmd.ExecuteReader())
{
   List<MyClass> results = new List<MyClass>();

   while(dr.Read())
   {
       MyClass newItem = new MyClass();

       newItem.Id = dr.GetInt32(0);  
       newItem.TypeId = dr.GetInt32(1);  
       newItem.AllowedSMS = dr.GetBoolean(2);  
       newItem.TimeSpan = dr.GetString(3);
       newItem.Price = dr.GetDecimal(4);
       newItem.TypeName = dr.GetString(5);

       results.Add(newItem);
   }
}

// return the results here, or bind them to a gridview or something....
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    You could use Object Initializer to beautify this - newItem ;) – usefulBee Apr 17 '15 at 19:51
  • Is there a way (perhaps with Linq?) to select or cast `SqlDataReader dr` as `List` instead of interating each record with `dr.Read()`? Similar to a Lambda `.Select(a => new MyClass(){ Id = a.Id, TypeId = a.TypeId })` etc? – jbwebtech Oct 26 '15 at 05:22