// private Mobile GetMobileInfo(MySqlDataReader reader) // { // Mobile mobi = new Mobile(); // mobi.MobileID = reader.GetInt32("Mobile_id"); // mobi.MobileName = reader.GetString("Mobile_Name"); // mobi.MobilePrice = reader.GetDecimal("Mobile_price"); // return mobi; // } public List <Order> OrderHistory(int Customer_id) { if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } query = $@"select ord.Order_id as Order_id,cust.Customer_Username,cust.Customer_email,mb.Mobile_id,mb.Mobile_Name,mb.Mobile_Price, ordt.Order_date,ordt.Amount from Customer cust inner join Orders ord on ord.Customer_order = cust.Customer_id inner join Orderdetails ordt on ord.Order_id = ordt.Order_id inner join Mobile mb on ordt.Mobile_id = mb.Mobile_id where ord.Customer_order = {Customer_id};"; MySqlCommand command = new MySqlCommand(query, connection); reader = command.ExecuteReader(); List <Order> ListOrders = new List <Order>(); if (reader == null) { System.Console.WriteLine("Bạn chưa từng đặt hàng\n"); } else { while (reader.Read()) { ListOrders.Add(GetOrdered(reader)); } } reader.Close(); DatabaseAccess.CloseConnection(); return(ListOrders); }
public bool CreatOrder(Order order) { bool result = false; if (order == null) { return(result); } MySqlCommand command = connection.CreateCommand(); command.CommandText = @"Lock tables Orders write, Mobile write, Orderdetails write"; command.ExecuteNonQuery(); MySqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; try { command.CommandText = "insert into Orders(Customer_order) values (@Customer_id)"; command.Parameters.AddWithValue("@Customer_id", order.OrderCustomer.CustomerID); command.ExecuteNonQuery(); query = $@"select Order_id from Orders where Customer_order = {order.OrderCustomer.CustomerID} order by Order_id desc limit 1;"; MySqlCommand command1 = new MySqlCommand(query, connection); reader = command1.ExecuteReader(); if (reader.Read()) { order.OrderID = reader.GetInt32("Order_id"); } reader.Close(); command.CommandText = "insert into OrderDetails(Order_id,Mobile_id,Amount,Order_date) values (@Order_id,@Mobile_id,@Amount,now())"; command.Parameters.Clear(); command.Parameters.AddWithValue("@Order_id", order.OrderID); command.Parameters.AddWithValue("@Mobile_id", order.OrderMobile.MobileID); command.Parameters.AddWithValue("@Amount", order.Amount); command.ExecuteNonQuery(); command.CommandText = "update Mobile set Mobile_quantity = Mobile_quantity -@Amount where Mobile_id = '" + order.OrderMobile.MobileID + "';"; command.Parameters.Clear(); command.Parameters.AddWithValue("@Amount", order.Amount); command.ExecuteNonQuery(); transaction.Commit(); result = true; } catch (System.Exception e) { transaction.Rollback(); System.Console.WriteLine(e); return(result); } finally { command.CommandText = "unlock tables"; command.ExecuteNonQuery(); DatabaseAccess.CloseConnection(); } return(result); }
public Mobile GetMobilebyId(int MobileID) { query = @"Select * from Mobile where Mobile_id = " + MobileID + ";"; MySqlCommand command = new MySqlCommand(query, connection); reader = command.ExecuteReader(); Mobile mobile = null; if (reader.Read()) { mobile = GetMobileDetail(reader); } reader.Close(); DatabaseAccess.CloseConnection(); return(mobile); }
public int Register(string Username, string Password, string Email, string Address, string Name, int Age, int Phonenumber, int CMT) { try { DatabaseAccess.OpenConnection(); query = @"insert into Customer(Customer_Username,Customer_password,Customer_email,Customer_address,Customer_Name,Customer_Age,Customer_Phone,Customer_CMT) values ('" + Username + "','" + Password + "','" + Email + "','" + Address + "','" + Name + "'," + Age + "," + Phonenumber + "," + CMT + ");"; MySqlCommand command = new MySqlCommand(query, connection); command.ExecuteNonQuery(); } catch { } finally { DatabaseAccess.CloseConnection(); } return(10); }
public Customer GetCustomer(string CustomerUsername) { DatabaseAccess.OpenConnection(); query = @"Select * from Customer where Customer_Username = '******';"; MySqlCommand command = new MySqlCommand(query, connection); reader = command.ExecuteReader(); Customer customer = null; if (reader.Read()) { customer = GetCustomerInfo(reader); } reader.Close(); DatabaseAccess.CloseConnection(); return(customer); }
public List <Mobile> GetMobilebyName(string MobileName) { if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } query = @"select * from Mobile where Mobile_name like '%" + MobileName + "%';"; MySqlCommand command = new MySqlCommand(query, connection); reader = command.ExecuteReader(); List <Mobile> listMobiles = new List <Mobile>(); if (reader != null) { listMobiles = GetListMobiles(command); } reader.Close(); DatabaseAccess.CloseConnection(); return(listMobiles); }
public int VerifyRegister(string Username, string Email) { int a; query = @"Select * from Customer where Customer_Username = '******' or Customer_email ='" + Email + "';"; MySqlCommand command = new MySqlCommand(query, connection); reader = command.ExecuteReader(); if (reader.Read()) { System.Console.WriteLine("Tài khoản hoặc email đã tồn tại\n"); a = 1; } else { System.Console.WriteLine("Bạn có thể sử dụng tài khoản và email này\n"); a = 2; } reader.Close(); DatabaseAccess.CloseConnection(); return(a); }
public int VerifyLogin(string CustomerUsername, string CustomerPassword) { int b; query = @"Select * from Customer where Customer_Username = '******' and Customer_password ='******';"; MySqlCommand command = new MySqlCommand(query, connection); reader = command.ExecuteReader(); if (reader.Read()) { System.Console.WriteLine("Đăng nhập thành công"); b = 1;; } else { System.Console.WriteLine("Sai tên đăng nhập hoặc mật khẩu"); b = 0; } reader.Close(); DatabaseAccess.CloseConnection(); return(b); }