public bool AddProducer(Producer producer)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql = "INSERT INTO NHACUNGCAP VALUES (@prID,@prName,@prAddress)";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@prID", producer.ProducerId);
cmd.Parameters.AddWithValue("@prName", producer.ProducerName);
cmd.Parameters.AddWithValue("@prName", producer.ProducerAddress);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, null, cmd);
}
}
public static bool IsExist(string table, string primaryK, string value)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
StringBuilder str_builder;
try
{
connection = DBConnection.getConnection();
connection.Open();
str_builder = new StringBuilder("SELECT 1 FROM ");
str_builder.Append(table).Append(" WHERE ");
str_builder.Append(primaryK).Append("=@value");
string sql = str_builder.ToString();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@value", value);
reader = cmd.ExecuteReader();
return(reader.Read());
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static bool EditNews(News news)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql =
"UPDATE TINTUC SET tieude=@title, anhmota=@picture,mota=@description,ngayviet=dateOfWrite,noidung=@content,loai=@kind WHERE id=@idNews";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@title", news.Title);
cmd.Parameters.AddWithValue("@picture", news.Picture);
cmd.Parameters.AddWithValue("@description", news.Description);
cmd.Parameters.AddWithValue("@dateOfWrite", news.DateOfWriting);
cmd.Parameters.AddWithValue("@content", news.Content);
cmd.Parameters.AddWithValue("@kind", news.Kind);
cmd.Parameters.AddWithValue("@idNews", news.NewsId);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, null, cmd);
}
}
public static bool AddNews(News news)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql =
"INSERT INTO TINTUC(tieude,anhmota,mota,ngayviet,noidung,loai) VALUES (@title,@picture,@description,@dateOfWrite,@content,@kind)";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@title", news.Title);
cmd.Parameters.AddWithValue("@picture", news.Picture);
cmd.Parameters.AddWithValue("@description", news.Description);
cmd.Parameters.AddWithValue("@dateOfWrite", news.DateOfWriting);
cmd.Parameters.AddWithValue("@content", news.Content);
cmd.Parameters.AddWithValue("@kind", news.Kind);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, null, cmd);
}
}
public static List <News> loadNews()
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
List <News> newses = new List <News>();
try
{
string sql = "SELECT * FROM TINTUC";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
newses.Add(new News().GetNews(reader));
}
}
return(newses.Count != 0 ? newses : null);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static bool RemoveOrders(String cartID)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql = "DELETE FROM GIOHANG WHERE MAGIOHANG=@cartID";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@cartID", cartID);
if (cmd.ExecuteNonQuery() < 1)
{
return(false);
}
sql = "DELETE FROM CHITIETDONHANG WHERE MAGIOHANG=@cartID";
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@cartID", cartID);
if (cmd.ExecuteNonQuery() > 0)
{
return(true);
}
return(false);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, null, cmd);
}
}
public static News FindByID(int newsID)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
try
{
string sql = "SELECT * FROM TINTUC WHERE ID=@newsID";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@newsID", newsID);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
return(new News().GetNews(reader));
}
}
return(null);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public bool EditProducer(Producer producer)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql = "UPDATE NHACUNGCAP SET TENNHACUNGCAP=@prName,DIACHI=@prAddress WHERE MANHACUNGCAP=@prID";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@prName", producer.ProducerName);
cmd.Parameters.AddWithValue("@prAddress", producer.ProducerAddress);
cmd.Parameters.AddWithValue("@prID", producer.ProducerId);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, null, cmd);
}
}
public static bool InsertCMT(Comment comment)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql =
"INSERT INTO BINHLUAN(HOTEN,NOIDUNG,MASANPHAM,SANPHAM,NGAYBINHLUAN) VALUES (@ten,@noidung,@ma_sp,@sp,NOW())";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@ten", comment.Name);
cmd.Parameters.AddWithValue("@noidung", comment.Content);
cmd.Parameters.AddWithValue("@ma_sp", comment.ProductId);
cmd.Parameters.AddWithValue("@sp", comment.Product);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, null, cmd);
}
}
public static ProductDetail getPrDetailByID(int productID)
{
MySqlDataReader reader = null;
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql =
"SELECT * FROM SANPHAM AS SP JOIN CHITIETSANPHAM AS CT ON SP.MASANPHAM=CT.MASANPHAM WHERE SP.MASANPHAM=@msp";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@msp", productID);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
return(new ProductDetail().GetProductDetail(reader));
}
}
return(null);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public bool AddUser(ThanhVien thanhVien)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
try
{
String sql =
"INSERT INTO THANHVIEN VALUES (@taikhoan,@matkhau,@ten,@gioitinh,@email,@sdt,@diachi,@level,@avatar)";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@taikhoan", thanhVien.TaiKhoan);
cmd.Parameters.AddWithValue("@matkhau", MD5.ConvertToMD5(thanhVien.MatKhau));
cmd.Parameters.AddWithValue("@ten", thanhVien.HoTen);
cmd.Parameters.AddWithValue("@gioitinh", thanhVien.GioiTinh);
cmd.Parameters.AddWithValue("@email", thanhVien.Email);
cmd.Parameters.AddWithValue("@sdt", thanhVien.SoDienThoai);
cmd.Parameters.AddWithValue("@diachi", thanhVien.DiaChi);
cmd.Parameters.AddWithValue("@level", thanhVien.Level);
cmd.Parameters.AddWithValue("@avatar", thanhVien.Avatar);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
//Admin, User
public static bool EditMember(Member member)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
try
{
string sql =
"UPDATE THANHVIEN SET HOTEN=@ten,GIOITINH=@gioitinh,EMAIL=@email,SODIENTHOAI=@phone,DIACHI=@diachi,LEVEL=@level WHERE TAIKHOAN=@taikhoan";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@ten", member.Name);
cmd.Parameters.AddWithValue("@gioitinh", member.Gender);
cmd.Parameters.AddWithValue("@email", member.Email);
cmd.Parameters.AddWithValue("@phone", member.Phone);
cmd.Parameters.AddWithValue("@diachi", member.Address);
cmd.Parameters.AddWithValue("@level", member.Level);
cmd.Parameters.AddWithValue("@taikhoan", member.UserName);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static List <Comment> LoadCMT(int productID)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
List <Comment> comments = new List <Comment>();
try
{
string sql = "SELECT HOTEN,SANPHAM,NOIDUNG,NGAYBINHLUAN FROM BINHLUAN WHERE MASANPHAM=@msp";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@msp", productID);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
comments.Add(new Comment().GetComment(reader));
}
}
return(comments.Count != 0 ? comments : null);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
//Admin, User
public static bool AddMember(Member member)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
try
{
String sql =
"INSERT INTO THANHVIEN VALUES (@taikhoan,@matkhau,@ten,@gioitinh,@email,@phone,@diachi,@level,@avatar)";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@taikhoan", member.UserName);
cmd.Parameters.AddWithValue("@matkhau", MD5.ConvertToMD5(member.Password));
cmd.Parameters.AddWithValue("@ten", member.Name);
cmd.Parameters.AddWithValue("@gioitinh", member.Gender);
cmd.Parameters.AddWithValue("@email", member.Email);
cmd.Parameters.AddWithValue("@phone", member.Phone);
cmd.Parameters.AddWithValue("@diachi", member.Address);
cmd.Parameters.AddWithValue("@level", member.Level);
cmd.Parameters.AddWithValue("@avatar", member.Avatar);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static Member checkLogin(string userName, string password)
{
MySqlDataReader reader = null;
MySqlConnection connection = null;
MySqlCommand cmd = null;
string sql = "SELECT * FROM THANHVIEN WHERE TAIKHOAN= @taikhoan AND MATKHAU= @matkhau";
try
{
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("taikhoan", userName);
cmd.Parameters.AddWithValue("matkhau", MD5.ConvertToMD5(password));
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
return(new Member().GetMember(reader));
}
}
return(null);
}
catch (Exception e)
{
Console.WriteLine(e);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static int countOfCate(String producer)
{
MySqlDataReader reader = null;
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql = "SELECT COUNT(*) AS SLUONG FROM SANPHAM WHERE NHACUNGCAP=@nhacungcap AND TRANGTHAI>0";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@nhacungcap", producer);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
return(reader.GetInt16("SLUONG"));
}
}
return(0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(0);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static MySqlDataReader findCateByKindAndGift(int kind)
{
MySqlDataReader reader = null;
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql =
"SELECT sp.masanpham, sp.tensanpham, sp.giaban, sp.giadagiam, sp.hinhanh, ct.quatang FROM sanpham as sp JOIN chitietsanpham as ct on sp.masanpham=ct.masanpham WHERE sp.loaisanpham=@loai and sp.trangthai>0 ORDER BY sp.giadagiam DESC";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@loai", kind);
reader = cmd.ExecuteReader();
return(reader);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static bool ChangePass(string usr_name, string pass)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
try
{
string sql = "UPDATE THANHVIEN SET MATKHAU=@pass WHERE TAIKHOAN=@usr";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@pass", MD5.ConvertToMD5(pass));
cmd.Parameters.AddWithValue("@usr", usr_name);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static List <ProductDetail> LoadProductDetail()
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
List <ProductDetail> productDetails = new List <ProductDetail>();
try
{
string sql = "SELECT * FROM sanpham as sp JOIN chitietsanpham as ct on sp.masanpham=ct.masanpham";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
productDetails.Add(new ProductDetail().GetProductDetail(reader));
}
}
return(productDetails.Count != 0 ? productDetails : null);
}
catch (SqlException e)
{
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static List <Product> LoadProducts()
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
List <Product> products = new List <Product>();
try
{
string sql = "SELECT * FROM SANPHAM";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
products.Add(new Product().GetProduct(reader));
}
}
return(products.Count != 0 ? products : null);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static List <Product> findCateByKindAndPrice(int kind, double salePrice)
{
MySqlDataReader reader = null;
MySqlConnection connection = null;
MySqlCommand cmd = null;
List <Product> products = new List <Product>();
try
{
string sql = "SELECT * FROM SANPHAM WHERE LOAISANPHAM=@loai_sp AND TRANGTHAI>0 AND GIADAGIAM>@gia";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@loai_sp", kind);
cmd.Parameters.AddWithValue("@gia", salePrice);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
products.Add(new Product().GetProduct(reader));
}
}
return(products.Count != 0 ? products : null);
}
catch (SqlException e)
{
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static bool UpdateUserInfor(ThanhVien thanhVien)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
try
{
string sql =
"UPDATE THANHVIEN SET HOTEN=@ten,GIOITINH=@gioitinh,EMAIL=@email,SODIENTHOAI=@sdt,DIACHI=@diachi WHERE TAIKHOAN=@taikhoan";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@ten", thanhVien.HoTen);
cmd.Parameters.AddWithValue("@gioitinh", thanhVien.GioiTinh);
cmd.Parameters.AddWithValue("@email", thanhVien.Email);
cmd.Parameters.AddWithValue("@sdt", thanhVien.SoDienThoai);
cmd.Parameters.AddWithValue("@diachi", thanhVien.DiaChi);
cmd.Parameters.AddWithValue("@taikhoan", thanhVien.TaiKhoan);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static List <ProductDetail> findCateByProducer(String id_producer, String sort, int page)
{
MySqlDataReader reader = null;
MySqlConnection connection = null;
MySqlCommand cmd = null;
List <ProductDetail> productDetails = new List <ProductDetail>();
String sql;
StringBuilder str_builder = new StringBuilder(
"SELECT * FROM SANPHAM AS SP JOIN CHITIETSANPHAM AS CT on SP.MASANPHAM=CT.MASANPHAM WHERE SP.TRANTHAI>0 AND SP.NHACUNGCAP=");
try
{
connection = DBConnection.getConnection();
connection.Open();
str_builder.Append("'").Append(id_producer).Append("'");
if (sort != null)
{
if (sort.Equals("desc"))
{
str_builder.Append(" ORDER BY SP.GIADAGIAM DESC ");
}
else if (sort.Equals("asc"))
{
str_builder.Append(" ORDER BY SP.GIADAGIAM ASC ");
}
}
str_builder.Append(" LIMIT @start,@end ");
sql = str_builder.ToString();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@start", (page - 1) * 8);
cmd.Parameters.AddWithValue("@end", 8);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
productDetails.Add(new ProductDetail().GetProductDetail(reader));
}
}
return(productDetails);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public bool EditProducer(ProductDetail productDetail)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql1 =
"UPDATE SANPHAM SET TENSANPHAM=@prName,GIADAGIAM=@salePrice,GIABAN=@price,SOLUONG=@amount,HINHANH=@picture,TRANGTHAI=@status,LOAISANPHAM=@kind WHERE MASANPHAM=@prID";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql1, connection);
cmd.Parameters.AddWithValue("@prName", productDetail.Product.ProductName);
cmd.Parameters.AddWithValue("@salePrice", productDetail.Product.SalePrice);
cmd.Parameters.AddWithValue("@price", productDetail.Product.Price);
cmd.Parameters.AddWithValue("@amount", productDetail.Product.Amount);
cmd.Parameters.AddWithValue("@picture", productDetail.Product.Picture);
cmd.Parameters.AddWithValue("@status", productDetail.Product.Status);
cmd.Parameters.AddWithValue("@kind", productDetail.Product.Kind);
cmd.Parameters.AddWithValue("@prID", productDetail.Product.ProductId);
if (cmd.ExecuteNonQuery() < 0)
{
return(false);
}
string sql2 =
"UPDATE CHITIETSANPHAM SET QUATANG=@gift,MANHINH=@screen,HEDIEUHANH=@os,CPU=@cpu,RAM=@ram,CAMERA=@cam,PIN=@pin WHERE MASANPHAM=@prID";
cmd = new MySqlCommand(sql1, connection);
cmd.Parameters.AddWithValue("@gift", productDetail.Gift);
cmd.Parameters.AddWithValue("@screen", productDetail.Technical.Screen);
cmd.Parameters.AddWithValue("@os", productDetail.Technical.OperatingSystem);
cmd.Parameters.AddWithValue("@cpu", productDetail.Technical.Cpu);
cmd.Parameters.AddWithValue("@ram", productDetail.Technical.Ram);
cmd.Parameters.AddWithValue("@cam", productDetail.Technical.Camera);
cmd.Parameters.AddWithValue("@pin", productDetail.Technical.Pin);
cmd.Parameters.AddWithValue("@prID", productDetail.Product.ProductId);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, null, cmd);
}
}
public bool add(ProductDetail productDetail)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql1 =
"INSERT INTO SANPHAM(TENSANPHAM,NHACUNGCAP,GIADAGIAM,GIABAN,SOLUONG,HINHANH,TRANGTHAI,LOAISANPHAM) VALUES (@prName,@producer,@salePrice,@price,@amount,@picture,@status,@kind)";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql1, connection);
cmd.Parameters.AddWithValue("@prName", productDetail.Product.ProductName);
cmd.Parameters.AddWithValue("@producer", productDetail.Product.Producer);
cmd.Parameters.AddWithValue("@salePrice", productDetail.Product.SalePrice);
cmd.Parameters.AddWithValue("@price", productDetail.Product.Price);
cmd.Parameters.AddWithValue("@amount", productDetail.Product.Amount);
cmd.Parameters.AddWithValue("@picture", productDetail.Product.Picture);
cmd.Parameters.AddWithValue("@status", productDetail.Product.Status);
cmd.Parameters.AddWithValue("@kind", productDetail.Product.Kind);
if (cmd.ExecuteNonQuery() < 0)
{
return(false);
}
string sql2 =
"INSERT INTO CHITIETSANPHAM(QUATANG,MAHINH,HEDIEUHANH,CPU,RAM,CAMERA,PIN) values (@gift,@screen,@os,@cpu,@ram,@cam,@pin)";
cmd = new MySqlCommand(sql1, connection);
cmd.Parameters.AddWithValue("@gift", productDetail.Gift);
cmd.Parameters.AddWithValue("@screen", productDetail.Technical.Screen);
cmd.Parameters.AddWithValue("@os", productDetail.Technical.OperatingSystem);
cmd.Parameters.AddWithValue("@cpu", productDetail.Technical.Cpu);
cmd.Parameters.AddWithValue("@ram", productDetail.Technical.Ram);
cmd.Parameters.AddWithValue("@cam", productDetail.Technical.Camera);
cmd.Parameters.AddWithValue("@pin", productDetail.Technical.Pin);
return(cmd.ExecuteNonQuery() > 0);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
ReleaseResources.Release(connection, null, cmd);
}
}
public static Member GetMember(string usrName)
{
MySqlDataReader reader = null;
MySqlConnection connection = null;
MySqlCommand cmd = null;
try
{
string sql =
"SELECT HOTEN,GIOITINH,EMAIL,SODIENTHOAI,DIACHI,LEVEL FROM THANHVIEN WHERE TAIKHOAN=@usrName";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@usrName", usrName);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
if (reader.Read())
{
return(null); //Chi duoc ton tai 1 tai khoan
}
string name = reader.GetString("hoten");
string gender = reader.GetString("gioitinh");
string email = reader.GetString("email");
string phone = reader.GetString("sodienthoai");
string address = reader.GetString("diachi");
int level = reader.GetInt16("level");
return(new Member(usrName, name, gender, email, phone, address, level));
}
}
return(null);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
//Admin
public static List <Member> LoadMember()
{
MySqlDataReader reader = null;
MySqlConnection connection = null;
MySqlCommand cmd = null;
List <Member> members = new List <Member>();
try
{
string sql = "SELECT TAIKHOAN,HOTEN,GIOITINH,EMAIL,SODIENTHOAI,DIACHI,LEVEL FROM THANHVIEN";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
string userName = reader.GetString("taikhoan");
string name = reader.GetString("hoten");
string gender = reader.GetString("gioitinh");
string email = reader.GetString("email");
string phone = reader.GetString("sodienthoai");
string address = reader.GetString("diachi");
int level = reader.GetInt16("level");
members.Add(new Member(userName, name, gender, email, phone, address, level));
}
}
return(members.Count != 0 ? members : null);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static List <CartDB> OrdersDetailList(String cartID)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
List <CartDB> cartDbs = new List <CartDB>();
try
{
string sql =
"SELECT TENSANPHAM,TRANGTHAI,SOLUONG,SOTIEN,HINHANH FROM CHITIETDONHANG WHERE MAGIOHANG=@cartID";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@cartID", cartID);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
string productName = reader.GetString("tensanpham");
int status = reader.GetInt16("tranthai");
int amount = reader.GetInt16("soluong");
double totalMoney = reader.GetDouble("sotien");
string picture = reader.GetString("hinhanh");
cartDbs.Add(new CartDB(productName, status, amount, totalMoney, picture));
}
}
return(cartDbs.Count != 0 ? cartDbs : null);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static List <CartDB> OrdersList()
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
List <CartDB> cartDbs = new List <CartDB>();
try
{
string sql = "SELECT MAGIOHANG,HOTEN,EMAIL,SODIENTHOAI,DIACHI,NGAYTHANHTOAN FROM GIOHANG";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
String cartID = reader.GetString("magiohang");
String name = reader.GetString("hoten");
String email = reader.GetString("email");
String phone = reader.GetString("sodienthoai");
String address = reader.GetString("diachi");
String dateOfpayment = reader.GetString("ngaythanhtoan");
cartDbs.Add(new CartDB(cartID, name, email, phone, address, dateOfpayment));
}
}
return(cartDbs.Count != 0 ? cartDbs : null);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}
public static Product getProductID(int productID)
{
MySqlConnection connection = null;
MySqlCommand cmd = null;
MySqlDataReader reader = null;
try
{
String sql =
"SELECT MASANPHAM,TENSANPHAM,GIADAGIAM,HINHDANH FROM SANPHAM WHERE TRANGTHAI>0 AND MASANPHAM=@msp";
connection = DBConnection.getConnection();
connection.Open();
cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@msp", productID);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
Product product = new Product();
product.ProductId = reader.GetInt16("masanpham");
product.ProductName = reader.GetString("tensanpham");
product.SalePrice = reader.GetDouble("giadagiam");
product.Picture = reader.GetString("hinhanh");
return(product);
}
}
return(null);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
ReleaseResources.Release(connection, reader, cmd);
}
}