// Add new Product to database.
private void btnAdd_Click(object sender, EventArgs e)
{
//int gridCount = productDataGridView.Rows.Count;
//for (int i = 0; i < gridCount; i++)
//{
//}
//productDataGridView.Rows.Add(); // Doesn't work due to data binding
if (Validator.IsPresent(txtNewName))
{
Product newProduct = new Product();
newProduct.ProdName = txtNewName.Text;
try
{
ProductDB.AddProduct(newProduct);
txtNewName.Text = ""; // clear field
// reload the products table data (and related suppliers table)
products = ProductDB.GetAllProducts();
productDataGridView.DataSource = products;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
//Method to delete the product from product table by passing all values of product details
public static bool DeleteProduct(Product product)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string qryDelete = "DELETE FROM Products " +
"WHERE ProductId = @ProductId AND ProdName = @ProdName";
SqlCommand cmdDelete = new SqlCommand(qryDelete, dbConn);
cmdDelete.Parameters.AddWithValue("@ProductId", product.ProductId);
cmdDelete.Parameters.AddWithValue("@ProdName", product.ProdName);
try
{
dbConn.Open();
return cmdDelete.ExecuteNonQuery() > 0; // return true if there were any affected rows
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
//Method to add the product to the product table
public static void AddProduct(Product product)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string qryInsert = "INSERT Products (ProdName) VALUES (@ProdName)";
SqlCommand cmdInsert = new SqlCommand(qryInsert, dbConn);
cmdInsert.Parameters.AddWithValue("@ProdName", product.ProdName);
try
{
dbConn.Open();
cmdInsert.ExecuteNonQuery();
//string qrySelect = "SELECT IDENT_CURRENT('Products') from Products";
//SqlCommand cmdSelect = new SqlCommand(qrySelect, dbConn);
//int productId = Convert.ToInt32(cmdSelect.ExecuteScalar());
//return productId;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
// get all products from database, sorted for display
public static List<Product> GetAllProducts()
{
List<Product> products = new List<Product>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string qrySelect =
"SELECT ProductId, ProdName " +
"FROM Products " +
"ORDER BY ProductId"; // selected columns from Products table
SqlCommand cmdSelect = new SqlCommand(qrySelect, dbConn);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Product product = new Product();
product.ProductId = Convert.ToInt32(dbReader["ProductId"]);
product.ProdName = Convert.ToString(dbReader["ProdName"]);
products.Add(product);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return products;
}
//Method to update the product by passing old and new values of product details
public static bool UpdateProduct(Product oldProduct, Product newProduct)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
//string qryUpdate = "UPDATE Products SET ProductId = @NewProductId, ProdName = @NewProdName " +
string qryUpdate = "UPDATE Products SET ProdName = @NewProdName " +
"WHERE ProductId = @OldProductId AND ProdName = @OldProdName";
SqlCommand cmdUpdate = new SqlCommand(qryUpdate, dbConn);
cmdUpdate.Parameters.AddWithValue("@OldProductId", oldProduct.ProductId);
cmdUpdate.Parameters.AddWithValue("@OldProdName", oldProduct.ProdName);
//cmdUpdate.Parameters.AddWithValue("@NewProductId", newProduct.ProductId);
cmdUpdate.Parameters.AddWithValue("@NewProdName", newProduct.ProdName);
try
{
dbConn.Open();
return cmdUpdate.ExecuteNonQuery() > 0; // return true if there were any affected rows
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
// get single product from database by productId
public static Product GetProduct(int productId)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT ProductId, ProdName from Products p " +
"WHERE ProductId = @ProductId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductId", productId);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
Product product = new Product();
product.ProductId = Convert.ToInt32(reader["ProductId"]);
product.ProdName = reader["ProdName"].ToString();
return product;
}
else
{
return null;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
// Delete Product and links to Supplier from database.
private void btnDelete_Click(object sender, EventArgs e)
{
int index = productDataGridView.CurrentCell.RowIndex;
if (index < 0) return;
int prodId = Convert.ToInt32(productDataGridView.Rows[index].Cells[0].Value);
string prodName = Convert.ToString(productDataGridView.Rows[index].Cells[1].Value);
DialogResult formResult = MessageBox.Show("Are you sure you want to delete the following entry?\n" + prodId + "\n" + prodName,
"Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question); // Prompt user to avoid accidental deletion.
if (formResult == DialogResult.Yes)
{
try
{
ProductSupplierDB.DeleteLinksByProduct(prodId); // Delete links first to satisfy constraints
Product target = new Product();
target.ProductId = prodId;
target.ProdName = prodName;
ProductDB.DeleteProduct(target);
// reload the products table data (and related suppliers table)
products = ProductDB.GetAllProducts();
productDataGridView.DataSource = products;
//productDataGridView.Rows.RemoveAt(index);
/*
if (!ProductDB.DelProduct(product))
{
MessageBox.Show("Failed to delete matching record, it might have been changed/deleted.", "DB Error");
this.GetProduct(product.ProductCode);
if (product != null)
ShowProduct();
else
ResetForm();
}
else
ResetForm();
*/
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
// Modify existing Product in database.
private void btnModify_Click(object sender, EventArgs e)
{
if (Validator.IsPresent(txtNewName))
{
int index = productDataGridView.CurrentCell.RowIndex;
if (index < 0) return;
int prodId = Convert.ToInt32(productDataGridView.Rows[index].Cells[0].Value);
string prodName = Convert.ToString(productDataGridView.Rows[index].Cells[1].Value);
Product oldProduct = new Product();
oldProduct.ProductId = prodId;
oldProduct.ProdName = prodName;
Product newProduct = new Product();
newProduct.ProductId = prodId;
newProduct.ProdName = txtNewName.Text;
try
{
ProductDB.UpdateProduct(oldProduct, newProduct);
txtNewName.Text = ""; // clear field
// reload the products table data (and related suppliers table)
products = ProductDB.GetAllProducts();
productDataGridView.DataSource = products;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
// Sunny Xie added.
// show the products the supplier offer
public static List<Product> GetProductsBySupplierId(int supplierID)
{
List<Product> products = new List<Product>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string qrySelect = " select pr.ProductId, ProdName from Products_Suppliers ps "
+ "inner join Products pr on ps.ProductId = pr.ProductId where SupplierId =@supplierID "
+ "ORDER BY ps.ProductId";
SqlCommand cmdSelect =
new SqlCommand(qrySelect, dbConn);
cmdSelect.Parameters.AddWithValue("@supplierID", supplierID);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Product product = new Product();
product.ProductId = Convert.ToInt32(dbReader["ProductId"]);
product.ProdName = Convert.ToString(dbReader["ProdName"]);
products.Add(product);
/*
//string[] row1 = new string[] { id.ToString(), name };
int rowid = supplierDataGridView.Rows.Add(id.ToString(), name);
if (rowid % 2 == 1)
{
// set background color for specific rows
supplierDataGridView.Rows[rowid].DefaultCellStyle.BackColor = Color.LightBlue;
}
*/
}
}
catch (SqlException ex)
{
//MessageBox.Show(ex.Message, ex.GetType().ToString());
throw ex;
}
finally
{
dbConn.Close();
}
return products;
}