public List <CustomerOrderDetailModel> GetList()
{
using (var sqlConnection = new SqlConnection(_stringConnection))
{
using (var sqlCommand = new SqlCommand(_getListCommand, sqlConnection))
{
sqlConnection.Open();
using (var sqlDataReader = sqlCommand.ExecuteReader())
{
if (sqlDataReader.HasRows)
{
while (sqlDataReader.Read())
{
CustomerOrderDetailModel customerOrderDetailModel = new CustomerOrderDetailModel()
{
OrderId = (int)sqlDataReader["order_id"],
CustomerId = (int)sqlDataReader["customer_id"],
ProductId = (int)sqlDataReader["product_id"],
FirstName = sqlDataReader["first_name"].ToString(),
LastName = sqlDataReader["last_name"].ToString(),
ProductName = sqlDataReader["product_name"].ToString(),
ListPrice = (decimal)sqlDataReader["list_price"]
};
_customerOrderDetailModels.Add(customerOrderDetailModel);
}
}
}
}
}
return(_customerOrderDetailModels);
}
public IList <CustomerOrderDetailModel> GetList()
{
List <CustomerOrderDetailModel> customerOrderDetailModels = new List <CustomerOrderDetailModel>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("select CustomerOrderId, CustomerId, ItemId, FirstName, LastName, [Description], Price from CustomerOrderDetail", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
CustomerOrderDetailModel customerOrderDetailModel = new CustomerOrderDetailModel()
{
CustomerOrderId = Convert.ToInt32(reader["CustomerOrderId"]),
CustomerId = Convert.ToInt32(reader["CustomerId"]),
ItemId = Convert.ToInt32(reader["ItemId"]),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Description = reader["Description"].ToString(),
Price = Convert.ToDecimal(reader["Price"])
};
customerOrderDetailModels.Add(customerOrderDetailModel);
}
}
}
}
}
return(customerOrderDetailModels);
}
//List of read database View lines. IList: best practice to return interface instead of a concrete list.
public IList <CustomerOrderDetailModel> GetList()
{
List <CustomerOrderDetailModel> customerOrderDetailModels = new List <CustomerOrderDetailModel>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT CustomerOrderId, CustomerId, ItemId, FirstName, LastName, [Description], Price FROM CustomerOrderDetail", connection))
{
using (SqlDataReader reader = command.ExecuteReader())//executes the sql command in " " through connection and gives back a reader
{
if (reader.HasRows)
{
while (reader.Read())
{
CustomerOrderDetailModel customerOrderDetailModel = new CustomerOrderDetailModel()
{
//CustomerOrderId = (int)reader["CustomerOrderId"]; //Ez nem jo?
CustomerOrderId = Convert.ToInt32(reader["CustomerOrderId"]),
CustomerId = Convert.ToInt32(reader["CustomerId"]),
ItemId = Convert.ToInt32(reader["ItemId"]),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Description = reader["Description"].ToString(),
Price = Convert.ToDecimal(reader["Price"])
};
customerOrderDetailModels.Add(customerOrderDetailModel);
}
}
}
}
}
return(customerOrderDetailModels);
}
protected void btnAdd_Click(object sender, EventArgs e)
{
CustomerOrderDetailModel customerOrderDetail = new CustomerOrderDetailModel();
int i = 0;
customerOrderDetail.CustomerName = txtcustomername.Text.ToString();
OracleConnection conn = new OracleConnection(connectionString);
string query = "select " +
"orders.ordernumber," +
" odetails.OrderUnit," +
" odetails.linetotal," +
" bill.OrderAmount, " +
"dish.dish_code," +
" dish.dishname, " +
"dish.local_or_another_name," +
"daddress.address_name," +
"lpoint.point " +
"from customer" +
" LEFT OUTER JOIN " +
"customerorderbill bill" +
" ON" +
" customer.Customer_ID = bill.customer_id " +
"left join " +
"orders on" +
" bill.ordernumber = orders.ordernumber" +
" left join" +
" orderdetail odetails on orders.ordernumber = odetails.ordernumber" +
" left join" +
" dish on " +
"odetails.dish_id = dish.dish_id " +
"left join" +
" deliveryaddress daddress on" +
" daddress.address_id = orders.address_id" +
" left join " +
"loyaltypoint lpoint on" +
" lpoint.loyaltypoint_code = odetails.loyaltypoint_code" +
" where Upper(customer.fullname)=Upper('" + customerOrderDetail.CustomerName + "') ";
OracleCommand cmd = new OracleCommand(query, conn);
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
OrderDetail order = new OrderDetail();
order.OrderNumber = reader["OrderNumber"].ToString();
order.DishCode = reader["Dish_Code"].ToString();
order.LineTotal = Convert.ToDecimal(reader["LineTotal"].ToString());
order.TotalAmount = Convert.ToDecimal(reader["OrderAmount"].ToString());
order.DishName = reader["DishName"].ToString();
order.LocalName = reader["Local_or_Another_Name"].ToString();
order.Orderunit = int.Parse(reader["Orderunit"].ToString());
if (int.TryParse(reader["Point"].ToString(), out (i)))
{
order.LoyaltyPoint = i;
}
order.DeliveryAddress = reader["Address_Name"].ToString();
customerOrderDetail.orderDetails.Add(order);
}
conn.Close();
dgvcustomerorderdetail.DataSource = null;
dgvcustomerorderdetail.DataSource = customerOrderDetail.orderDetails;
dgvcustomerorderdetail.DataBind();
}
