SqlDataReader C#, SQL Server 2005, VS 2008

1
votes

I am trying to select food_ItemName and food_UnitPrice from t_Food table in SQL Server 2005 using C# and VS 2008.

I have the following code:

        private SqlConnection connection;

        private void GetDatabaseConnection()
        {
            string connectionString = @"Server = RZS-F839AD139AA\SQLEXPRESS; Integrated  Security = SSPI; Database = HotelCustomerManagementDatabase";
            connection = new SqlConnection(connectionString);
            connection.Open();
        }

        public Food PopulateFoodItemListview()
        {
           GetDatabaseConnection();
            string selectFoodItemQuery = @"SELECT food_ItemName, food_UnitPrice FROM t_Food";
            SqlCommand command = new SqlCommand(selectFoodItemQuery, connection);
            SqlDataReader reader = command.ExecuteReader();
            Food food = new Food();
            List foodList = new List();

            while (reader.Read())
            {
                food.ItemName.Add(reader.GetString(0));
                MessageBox.Show("ItemName: "+ food.ItemName);
                food.UnitPrice.Add(reader.GetDouble(1));
                MessageBox.Show("UnitPrice: " + food.UnitPrice);

            }
            connection.Close();
            return food;
        }

And in Food class I have the following code:

    public class Food
    {
        private List itemName = new List();
        private List unitPrice = new List();
        private double itemUnit;
        private Customer foodCustomer = new Customer();

        public List ItemName
        {
            get { return itemName; }
            set { itemName = value ; }
        }

        public List UnitPrice
        {
            get { return unitPrice; }
            set { unitPrice = value; }
        }

        public double ItemUnit
        {
            get { return itemUnit; }
            set { itemUnit = value; }
        }
        public double GetItemPrice(double itemUnit, double unitPrice)
        {
            double itemPrice = itemUnit*unitPrice;
            return itemPrice;
        }
}

In messageBox it supposed to show Rice, Mutton, Beef and their price 50, 100, 150. But it showhing ItemName: System.Collections.Generic.List`1[System.String] and ItemName: System.Collections.Generic.List`1[System.Double]

Whats the problem?

5
c#sql
Are you getting any errors? Is your SQL query returning the desired data? – Wallace Breza
Just a note, it is good practice to use generic collections when you can. It's better practice to not provide setters on properties that expose those collections. – Marc
@towhidulbashar - welcome to stackoverflow. Just to let you know you may want to go through all of your questions and mark an answer you used as being accepted. The community usually is at work but the users of the community still make time to answer questions on this site. So it is definately the right thing to do is mark all of your questions that are solved with an accepted answer. – JonH
@JonH :: I chose the accepted answer by clicking on tick mark. Is there anything I need to do to mark my question as a solved problem? Actually I'm new in Stackoverflow community. Thanks for your suggestion. – Towhid

5 Answers

4
votes

You should specify:

private List<string> itemName;

That way when you add a name you can do:

itemName = new List<string>();
itemName.Add("Pizza");

The problem you are noticing is food.itemName is simply a list not a string. So you can get the string from a list by : myList[someIndex], that is your List object followed by the index of the item in your list.

Once you fill your food itemName list you should be able to foreach it:

foreach(string f in food.itemName)
 MessageBox.Show(f);

Aside from this I am a bit concerned with the wording of this class. If a Food object represents one entity of food then why is itemName and price list members of this class? They should simply be properties of type string and double respectively. When you create a food object the object contains a name and a price. 

public sealed class Food
{
 public string itemName {get; set;}
 public double unitPrice {get; set;}
}

Here is how you could do it:

public sealed class Food 
 {
     public string itemName {get; set;}
     public double unitPrice {get; set;}
     public double itemUnit {get; set;}
     public double getItemPrice() { return itemUnit * unitPrice; }
     public Food() : this("unknown food", 0);
     public Food(string item, double price) { itemName = item; unitPrice = price; }

     //you might want to rethink your customer object as well.  Are
     //you associating one customer with one item of food ?
 }

And how to use the class:

 public sealed class MyUsageOfFood
 {
 public static void main() {
  List<Food> f = new List<Food>;
  f.Add(new Food("Pizza", 1.50));
  f.Add(new Food("Hamburger", 2.00));

  foreach(food t in f)
      MessageBox.Show(t.itemName);
 }}
4
votes

food.ItemName is a List, not a string, so the ToString() returns the type. What you want instead is:

food.ItemName[food.ItemName.Count - 1]

and same for UnitPrice:

food.UnitPrice[food.UnitPrice.Count - 1].ToString()
1
votes

Your Food class is meant to represent a single item of food, so your itemName and unitPrice members should be of type string and double, respectively (not List, which is used to store multiple values).

Then, PopulateFoodItemListview should return List<Food> (not Food). Inside your reader.Read() loop, you should create a new Food instance, populate it with the appropriate values from the database, and then add it to your List<Food> collection (which is then returned at the end of the method).

Update: Like this:

public List<Food> PopulateFoodItemListview()
{
    GetDatabaseConnection();
    string selectFoodItemQuery = @"SELECT food_ItemName, food_UnitPrice FROM t_Food";
    SqlCommand command = new SqlCommand(selectFoodItemQuery, connection);
    SqlDataReader reader = command.ExecuteReader();
    List<Food> foods = new List<Food>();
    List<string> foodList = new List<string>();

    while (reader.Read())
    {
        Food food = new Food();
        food.ItemName = reader.GetString(0);
        MessageBox.Show("ItemName: "+ food.ItemName);
        food.UnitPrice = reader.GetDouble(1);
        MessageBox.Show("UnitPrice: " + food.UnitPrice);
        foods.Add(food);
    }
    connection.Close();
    return foods;
}

public class Food
{
    private string itemName = "";
    private double unitPrice = 0.0;
    private double itemUnit;

    private Customer foodCustomer = new Customer();

    public string ItemName
    {
        get { return itemName; }
        set { itemName = value ; }
    }

    public double UnitPrice
    {
        get { return unitPrice; }
        set { unitPrice = value; }
    }

    public double ItemUnit
    {
        get { return itemUnit; }
        set { itemUnit = value; }
    }
    public double GetItemPrice(double itemUnit, double unitPrice)
    {
        double itemPrice = itemUnit*unitPrice;
        return itemPrice;
    }
}
0
votes

ItemName and UnitPrice are List objects, not individual items. When you pass objects to the MessageBox function, it will call ToString() on the object to get a displayable string. ToSting() on a List returns the strings you are seeing. You should use indexes to get to items in the list:

MessageBox(ItemName(1));

or 

MessageBox(UnitPrice(1));
0
votes

The problem is that food.Item name returns a List<string> and you are making the implicit call to List<string>::ToString which returns details on the type. You should either request the last entered item as food.ItemName[food.ItemName.Count - 1] or you could iterate through all of the items in the list to output all of the names/prices.