public bool InsertShoes(ShoesDTO shoes)
{
bool result = false;
string sql = "Insert into Shoes(name, categoryID, brandID, material, description, originID, isDeleted) values (@name, @categoryID, @brandID, @material, @description, @originID, @isDeleted)";
SqlConnection cnn = new SqlConnection(Consts.Consts.connectionString);
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.Parameters.AddWithValue("@name", shoes.Name);
cmd.Parameters.AddWithValue("@categoryID", shoes.CategoryId);
cmd.Parameters.AddWithValue("@brandID", shoes.BrandId);
cmd.Parameters.AddWithValue("@material", shoes.Material);
cmd.Parameters.AddWithValue("@description", shoes.Description);
cmd.Parameters.AddWithValue("@originID", shoes.OriginId);
cmd.Parameters.AddWithValue("@isDeleted", false);
if (cmd.ExecuteNonQuery() > 0)
{
result = true;
}
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);
}
public List <ShoesDTO> GetAllShoes()
{
List <ShoesDTO> result = null;
string sql = "Select * From Shoes";
SqlConnection cnn = new SqlConnection(Consts.Consts.connectionString);
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
SqlCommand cmd = new SqlCommand(sql, cnn);
SqlDataReader dr = cmd.ExecuteReader();
int shoesId, categoryId, brandId, originId;
string name, material, des;
bool isDeleted;
result = new List <ShoesDTO>();
ShoesDTO dto = null;
while (dr.Read())
{
shoesId = dr.GetInt32(0);
name = dr.GetString(1);
categoryId = dr.GetInt32(2);
brandId = dr.GetInt32(3);
material = dr.GetString(4);
des = dr.GetString(5);
originId = dr.GetInt32(6);
isDeleted = dr.GetBoolean(7);
dto = new ShoesDTO(shoesId, name, categoryId, brandId, material, des, originId, isDeleted);
dto.BrandName = new BrandData().GetBrandNameByID(brandId);
dto.CategoryName = new CategoryData().GetCategoryNameByID(categoryId);
dto.OriginName = new OriginData().GetOriginNameByID(originId);
result.Add(dto);
}
cnn.Close();
return(result);
}
public bool UpdateShoes(ShoesDTO shoes)
{
bool result = false;
string sql = "Update Shoes Set name = @name, categoryID = @categoryID, brandID = @brandID, material = @material, description = @description, originID = @originID 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("@name", shoes.Name);
cmd.Parameters.AddWithValue("@categoryID", shoes.CategoryId);
cmd.Parameters.AddWithValue("@brandID", shoes.BrandId);
cmd.Parameters.AddWithValue("@material", shoes.Material);
cmd.Parameters.AddWithValue("@description", shoes.Description);
cmd.Parameters.AddWithValue("@originID", shoes.OriginId);
cmd.Parameters.AddWithValue("@shoesID", shoes.ShoesId);
result = cmd.ExecuteNonQuery() > 0;
//Console.Write(result);
return(result);
}