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