コード例 #1
0
        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;
        }
コード例 #2
0
        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;

        }
コード例 #3
0
        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;
        }
コード例 #4
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;
 }
コード例 #5
0
        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;
        }
コード例 #6
0
        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;

        }
コード例 #7
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;
        }
コード例 #8
0
        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();
            }
        }