SqlDataReader C#, SQL Server 2005, VS 2008
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-F839AD139AASQLEXPRESS; 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?
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);
}}
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()
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;
}
}