/*
* Author: Aaron Mill
* Date: July 6, 2015
*/
public static bool AddProduct(Product product)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string insertStatement =
"INSERT Products " +
"(ProdName) " +
"VALUES (@ProdName)";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@ProdName", product.ProdName);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
// Inform user and return execution status
if (count > 0)
{
// MessageBox.Show("Inserted record: " + product.ProductId);
return true;
}
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
//if selected on left is not null check for duplicate and if not pass into other list
if (lstAllProducts.SelectedItem != null)
{
int productId = ((Product)(lstAllProducts.SelectedItem)).ProductId;
string prodName = ((Product)(lstAllProducts.SelectedItem)).ProdName;
Product ProductToAdd = new Product(productId, prodName, SupplierToEdit.SupName, SupplierToEdit.SuplierId);
if (lstSuppliersProducts.Items.Contains(ProductToAdd))
{
MessageBox.Show("I am afraid product: "+ProductToAdd.ToString()+" has already been added.");
}
else
{
lstSuppliersProducts.Items.Add(ProductToAdd);
}
}
}
//function to delete the SQL data
/*Chen Code Start, Aaron helped by deleting Products_Products_Suppliers*/
public static bool DeleteProduct(Product product)
{
SqlConnection connection = TravelExpertsDB.GetConnection(); //connect the SQL7
string deleteForeign =
"DELETE FROM Products_Products_Suppliers " +
"WHERE ProductId = @ProductId ";
SqlCommand deleteForeignCommand =
new SqlCommand(deleteForeign, connection); //call the SQL deleteForeign command to delete the dependent table
deleteForeignCommand.Parameters.AddWithValue(
"@ProductId", product.ProductId);
string deleteStatement =
"DELETE FROM Products " +
"WHERE ProductId = @ProductId ";
SqlCommand deleteCommand =
new SqlCommand(deleteStatement, connection); //call the SQL delete command to delete the data
deleteCommand.Parameters.AddWithValue(
"@ProductId", product.ProductId);
try
{
connection.Open();
int count2 = deleteForeignCommand.ExecuteNonQuery();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0 && count2 > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/*
* Author: Aaron Mill
* Date: July 6, 2015
*/
public static bool UpdateProduct(Product product)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string updateStatement =
"UPDATE Products SET " +
"ProdName = @ProdName " +
"WHERE ProductId = @ProductId";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@ProdName", product.ProdName);
updateCommand.Parameters.AddWithValue(
"@ProductId", product.ProductId);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/*Chen Code Start*/
public static List<Product> GetProducts(string filter)
{
List<Product> products = new List<Product>();
SqlConnection connection = TravelExpertsDB.GetConnection();
string query = "SELECT ProductId, ProdName FROM Products " +
" WHERE ProductId > -1 " + filter;
SqlCommand command = new SqlCommand(query, connection);
try
{
// Open db connection and run query
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Read dataset fill Products list
while (reader.Read())
{
Product product = new Product();
product.ProductId = (int)reader["ProductId"];
product.ProdName = reader["ProdName"].ToString();
products.Add(product);
}
return products;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/* Author: Pooja Jairath
* Date: 6th July 2015
* Purpose: Function to retrieve product data based on ProductId from table Products, to make it available to be edited
*/
public static Product GetProduct(int productid)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT * from Products WHERE ProductId = @ProductId ";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductId", productid);
try
{
connection.Open();
SqlDataReader packReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (packReader.Read())
{
Product product = new Product();
product.ProductId = (int)packReader["ProductId"];
product.ProdName = packReader["ProdName"].ToString();
return product;
}
else
{
return null;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
// Author: Mark Poffenroth
/// <summary>
/// Gets list of Products objects NOT already included w/ specfied Package Id
/// </summary>
/// <param name="packageid">Package Id used to get Packages</param>
/// <returns>List of Product objects available to add to specfied Package Id.</returns>
public static List<Product> GetPackageProductsToAdd(int packageid)
{
List<Product> products = new List<Product>();
// Define query string
SqlConnection connection = TravelExpertsDB.GetConnection();
string query = "SELECT DISTINCT pr.ProductId, pr.ProdName " +
" FROM Products pr " +
" INNER JOIN Products_Suppliers ps ON pr.ProductId = ps.ProductId " +
" INNER JOIN Packages_Products_Suppliers pps ON pps.ProductSupplierId = ps.ProductSupplierId " +
" INNER JOIN Packages pa ON pa.PackageId = pps.PackageId " +
" WHERE pa.PackageId <> @PackageId";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@PackageId", packageid);
try
{
// Open db connection and run query
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Read dataset fill Packages list
while (reader.Read())
{
Product product = new Product();
product.ProductId = (int)reader["ProductId"];
product.ProdName = reader["ProdName"].ToString();
products.Add(product);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return products;
}
