public ActionResult Product(Product product)
{
if (!dao.getCurrentUserIsAdmin())
{
return RedirectToAction("Index", "Home");
}
dso.editProduct(product);
return RedirectToAction("Product");
}
public bool editProduct(Product product)
{
db.Entry(product).State = EntityState.Modified;
db.SaveChanges();
return true;
}
internal bool UpdateProduct(Product product)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
_transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "UPDATE product SET name=@name, description=@description, price=@price, buy_price=@buy_price, stock=@stock, category_id=@category_id where id = @id;";
cmd.Parameters.AddWithValue("id", (long)product.Id);
cmd.Parameters.AddWithValue("name", product.ProductName);
cmd.Parameters.AddWithValue("description", product.ProductDescription);
cmd.Parameters.AddWithValue("price", product.Price);
cmd.Parameters.AddWithValue("buy_price", product.BuyPrice);
cmd.Parameters.AddWithValue("stock", (int)product.Stock);
cmd.Parameters.AddWithValue("category_id", (long)product.Category.Id);
//Parameters
bool success = parseNonqueryResult(cmd.ExecuteNonQuery());
if (success)
{
_transaction.Commit();
_transaction.Dispose();
}
return success;
}
}
internal List<Product> GetProducts(string pattern = null)
{
//Krijg een lijst met producten
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
if (pattern == null)
{ //Als je niets invult dan moet je alle producten laten zien
cmd.CommandText = "SELECT * FROM product;";
}
else
{ //Anders mag je wat de user heeft ingetypt gebruiken als zoekparameters
cmd.CommandText = "SELECT * FROM product LEFT JOIN category ON product.category_id = category.id WHERE product.name LIKE '%' || @pattern || '%' OR description LIKE '%' || @pattern ||'%' OR category.name LIKE '%' || @pattern ||'%';";
cmd.Parameters.AddWithValue("pattern", pattern);
}
NpgsqlDataReader reader = cmd.ExecuteReader(); //intialiseren
List<Product> products = new List<Product>();
while (reader.Read())
{
Product product = new Product();
product.Id = (ulong)reader.GetInt32(reader.GetOrdinal("id"));
product.BuyPrice = reader.GetDecimal(reader.GetOrdinal("buy_price"));
product.Price = reader.GetDecimal(reader.GetOrdinal("price"));
product.ProductName = reader.GetString(reader.GetOrdinal("name"));
product.ProductDescription = reader.GetString(reader.GetOrdinal("description"));
product.Category = this.GetCategory((ulong)reader.GetInt64(reader.GetOrdinal("category_id")));
product.Stock = (ulong)reader.GetInt64(reader.GetOrdinal("stock"));
//TODO: finish loading image properly
//product.image = byteArrayToImage((byte[])reader.GetValue(reader.GetOrdinal("image")));
products.Add(product); //Blijf producten toeveogen
}
reader.Close();
return products; //return alle producten
}
}
internal Product GetProduct(ulong productId)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
cmd.CommandText = "SELECT * FROM product WHERE product.id=@productId;";
cmd.Parameters.AddWithValue("productId", (long)productId);
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.Read()) //lees een product
{
Product product = new Product();
product.Id = (ulong)reader.GetInt32(reader.GetOrdinal("id"));
product.BuyPrice = reader.GetDecimal(reader.GetOrdinal("buy_price"));
product.Price = reader.GetDecimal(reader.GetOrdinal("price"));
product.ProductName = reader.GetString(reader.GetOrdinal("name"));
product.ProductDescription = reader.GetString(reader.GetOrdinal("description"));
product.Category = this.GetCategory((ulong)reader.GetInt64(reader.GetOrdinal("category_id")));
product.Stock = (ulong)reader.GetInt64(reader.GetOrdinal("stock"));
reader.Close();
return product;
}
reader.Close();
return null; //Anders return je null
}
}
internal bool CreateProduct(Product product)
{
//Maken van een product
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = _connection;
_transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "INSERT INTO product(name, description, price, buy_price, stock, category_id)"
+ "VALUES(@name, @description, @price, @buy_price, @stock, @category_id);";
cmd.Parameters.AddWithValue("name", product.ProductName);
cmd.Parameters.AddWithValue("description", product.ProductDescription);
cmd.Parameters.AddWithValue("price", product.Price);
cmd.Parameters.AddWithValue("buy_price", product.BuyPrice);
cmd.Parameters.AddWithValue("stock", (int)product.Stock);
cmd.Parameters.AddWithValue("category_id", (long)product.Category.Id);
//image
//Parameters
bool success = parseNonqueryResult(cmd.ExecuteNonQuery());
if (success)
{
_transaction.Commit();
_transaction.Dispose();
return success; //Commit als het sucessvol is
}
_transaction.Rollback();
_transaction.Dispose();
return success;
}
}