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(); }