public static List<Product> GetProducts()
{
List<Product> products = new List<Product>();
string query = "SELECT p.ProductId, p.Name, p.OwnerId, u.FullName "
+ "FROM dbo.[Product] p "
+ "INNER JOIN dbo.[User] u ON u.UserId = p.OwnerId "
+ "WHERE p.OwnerId = @ownerid";
SqlCommand command = new SqlCommand(query, _connection);
command.Parameters.AddWithValue("@ownerid", CurrentUser.UserId);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Product p = new Product();
p.ProductId = reader.GetGuid(0);
p.Name = reader.GetString(1);
p.OwnerId = reader.GetGuid(2);
p.OwnerName = reader.GetString(3);
products.Add(p);
}
}
return products;
}
public static bool UpdateProduct(Product product)
{
bool updated = false;
string query = "update product set Name = @name, OwnerId=@ownerid "
+ "output deleted.Name as [OldName], deleted.OwnerId [OldOwnerId]"
+ ", inserted.Name as [NewName], inserted.OwnerId [NewOwnerId]"
+ "from Product where ProductId=@productid";
SqlCommand command = new SqlCommand(query, _connection);
command.Parameters.Add(new SqlParameter("@name",product.Name));
command.Parameters.Add(new SqlParameter("@ownerid", product.OwnerId));
command.Parameters.Add(new SqlParameter("@productid", product.ProductId));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
updated = true;
string oldName = reader.GetString(0);
string oldOwnerId = reader.GetGuid(1).ToString();
string newName = reader.GetString(2);
string newOwnerId = reader.GetGuid(3).ToString();
reader.Close();
LogUpdate(product.ProductId, string.Format("Updated product {0}. Old Name: {1}, Old OwnerId: {2}, New Name: {3}, New OwnerId: {4}", product.ProductId, oldName, oldOwnerId, newName, newOwnerId));
}
}
return updated;
}
public static bool DeleteProduct(Product product)
{
string query = "Delete from Product where ProductId=@productid";
SqlCommand command = new SqlCommand(query, _connection);
command.Parameters.AddWithValue("@productid", product.ProductId);
int rows = command.ExecuteNonQuery();
if (rows > 0)
{
LogDelete(Guid.Empty, string.Format("Deleted {0} ({1})", product.Name, product.ProductId));
}
return rows > 0;
}
public static bool ProductExists(Product product)
{
string query = "Select 1 from Product where name=@name";
SqlCommand command = new SqlCommand(query,_connection);
command.Parameters.AddWithValue("@name", product.Name);
var result = command.ExecuteScalar();
return result != null;
}
public static bool CreateProduct(Product product)
{
string query="";
if (!ProductExists(product))
{
query = "insert into product (Name,OwnerId) output INSERTED.ProductId values (@name , @ownerid)";
SqlCommand command = new SqlCommand(query, _connection);
command.Parameters.AddWithValue("@name", product.Name);
command.Parameters.AddWithValue("@ownerid", product.OwnerId);
var productId = command.ExecuteScalar();
if (productId != null)
{
LogCreate((Guid)productId, "Created product '" + product.Name + "'");
}
return productId != null;
}
else
return false;
}
public static bool UpdateStock(Product product, int count)
{
// TODO: Add unit price
string query = "";
StockInfo stockInfo = GetStockInfo(product);
if (stockInfo != null)
{
query += "Update Stock set Count=@count from Stock Where ProductId=@productid";
}
else
{
query += "Insert Into Stock values (@productid,@count,5)";
}
SqlCommand command = new SqlCommand(query,_connection);
command.Parameters.Add(new SqlParameter("@productid", product.ProductId));
command.Parameters.Add(new SqlParameter("@count", count));
int rows = command.ExecuteNonQuery();
if(rows > 0)
{
LogUpdate(product.ProductId, "Stock updated from " + ((stockInfo != null) ? stockInfo.Count : 0) + " to " + count);
}
return rows > 0;
}
public static StockInfo GetStockInfo(Product selectedProduct)
{
StockInfo stockInfo = null;
string query = "SELECT TOP 1 ProductId, Count FROM dbo.Stock WHERE ProductId = @ProductId";
SqlCommand command = new SqlCommand(query, _connection);
command.Parameters.Add(new SqlParameter("@ProductId", selectedProduct.ProductId));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
stockInfo = new StockInfo()
{
ProductId = reader.GetGuid(0),
Count = reader.GetInt32(1)
};
}
}
return stockInfo;
}
private void CreateButton_Click(object sender, EventArgs e)
{
string name = ProductNameTextBox.Text;
User owner = (User)OwnerComboBox.SelectedItem;
Product product = new Product()
{
Name = name,
OwnerId = owner.UserId
};
bool created = DBHelper.CreateProduct(product);
if (created)
{
RefreshProducts();
}
}