public List <ShoesDTO> FindByName(string search) //tim kiem giay theo ten, hien thi o trang search: name, category, brand, price, image
{
List <ShoesDTO> result = null;
string sql = "Select shoesID, name, categoryID, brandID From Shoes Where isDeleted = @Deleted And name like @name";
SqlConnection cnn = new SqlConnection(Consts.Consts.connectionString);
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.Parameters.AddWithValue("@Deleted", false);
cmd.Parameters.AddWithValue("@name", "%" + search + "%");
SqlDataReader dr = cmd.ExecuteReader();
result = new List <ShoesDTO>();
while (dr.Read())
{
int shoesID = dr.GetInt32(0);
string name = dr.GetString(1);
int categoryID = dr.GetInt32(2);
string categoryName = new CategoryData().GetCategoryNameByID(categoryID);
int brandID = dr.GetInt32(3);
string brandName = new BrandData().GetBrandNameByID(brandID);
float price = GetPriceByShoesID(shoesID);
string image = GetImageByShoesID(shoesID);
result.Add(new ShoesDTO(shoesID, name, categoryName, brandName, price, image));
}
cnn.Close();
return(result);
}
//H
public ShoesDTO GetShoesInformationByShoesID(int shoesID)
{
ShoesDTO shoes = null;
string sql = "SELECT * FROM Shoes WHERE shoesID = @shoesID";
SqlConnection cnn = new SqlConnection(Consts.Consts.connectionString);
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.Parameters.AddWithValue("@shoesID", shoesID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int shoesId = (int)dr[0];
string name = (string)dr[1];
int categoryID = (int)dr[2];
string categoryName = new CategoryData().GetCategoryNameByID(categoryID);
int brandID = (int)dr[3];
string brandName = new BrandData().GetBrandNameByID(brandID);
string material = (string)dr[4];
string description = (string)dr[5];
int originID = (int)dr[6];
string originName = new OriginData().GetOriginNameByID(originID);
shoes = new ShoesDTO
{
ShoesId = shoesId,
Name = name,
CategoryId = categoryID,
CategoryName = categoryName,
BrandId = brandID,
BrandName = brandName,
Material = material,
Description = description,
OriginId = originID,
OriginName = originName
};
}
cnn.Close();
return(shoes);
}
public ShoesDTO GetShoesDetailByProductID(int productID)
{
int shoesID = GetShoesIDByProductID(productID);
ShoesDTO shoes = null;
string sql = "Select name, categoryID, brandID, material, description, originID, P.price, P.color From Shoes, Products P Where Shoes.isDeleted = @isDeleted and Shoes.shoesID = @shoesID and P.productID = @productID";
SqlConnection cnn = new SqlConnection(Consts.Consts.connectionString);
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.Parameters.AddWithValue("@isDeleted", false);
cmd.Parameters.AddWithValue("@shoesID", shoesID);
cmd.Parameters.AddWithValue("@productID", productID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string name = (string)dr[0];
int categoryID = (int)dr[1];
string categoryName = new CategoryData().GetCategoryNameByID(categoryID);
int brandID = (int)dr[2];
string brandName = new BrandData().GetBrandNameByID(brandID);
string material = (string)dr[3];
string description = (string)dr[4];
int originID = (int)dr[5];
string originName = new OriginData().GetOriginNameByID(originID);
double price = (double)dr[6];
string color = (string)dr[7];
shoes = new ShoesDTO(shoesID, name, categoryName, brandName, (float)price, null);
shoes.Color = color;
shoes.Material = material;
shoes.Description = description;
shoes.OriginName = originName;
}
cnn.Close();
return(shoes);
}