// get all products and suppliers from joined tables
public static List<ProductSupplier> GetAllProductSupplier()
{
List<ProductSupplier> productSupplierList = new List<ProductSupplier>();
//Get connection
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT ProductSupplierId, p.ProductId, s.SupplierId, p.ProdName as ProdName, s.SupName as SupName FROM Products p, " +
"Suppliers s, Products_Suppliers ps " +
"WHERE p.ProductId = ps.ProductId AND s.SupplierId = ps.SupplierId AND " +
"ps.ProductSupplierId = productSupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
//Read the data from the query result
SqlDataReader reader = selectCommand.ExecuteReader();
//get the result and set it in productsupplier object
while (reader.Read())
{
ProductSupplier productSupplier = new ProductSupplier();
productSupplier.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
productSupplier.ProductId = Convert.ToInt32(reader["ProductId"]);
productSupplier.SupplierId = Convert.ToInt32(reader["SupplierId"]);
productSupplier.ProdName = reader["ProdName"].ToString();
productSupplier.SupName = reader["SupName"].ToString();
productSupplierList.Add(productSupplier);
}
reader.Close();
}
//catch (ConstraintException ex) // Added to temporarily resolve conflicts with unused tables
//{
// string qryAlter = "ALTER TABLE [Packages_Products_Suppliers] DROP CONSTRAINT [Packages_Products_Supplie_FK01]";
// SqlCommand cmdAlter = new SqlCommand(qryAlter, connection);
// cmdAlter.ExecuteNonQuery();
//}
// NOTE: Doesn't work as it should, instead run the following queries before running:
// "ALTER TABLE Packages_Products_Suppliers DROP CONSTRAINT Packages_Products_Supplie_FK01"
// "ALTER TABLE BookingDetails DROP CONSTRAINT FK_BookingDetails_Products_Suppliers"
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
//return the productSupplierList
return productSupplierList;
}
//Method to modify the product supplier details
private void btnModify_Click(object sender, EventArgs e)
{
//Gives Row Index
//int currentRow = productSupplierDataGridView.CurrentCell.RowIndex;
int currentRow = productSupplierDataGridView.CurrentRow.Index;
//Get old ProductSupplier details
ProductSupplier oldProductSupplier = new ProductSupplier();
oldProductSupplier.ProductSupplierId = Convert.ToInt32(productSupplierDataGridView.Rows[currentRow].Cells[0].Value);
oldProductSupplier.ProductId = Convert.ToInt32(productSupplierDataGridView.Rows[currentRow].Cells[1].Value);
oldProductSupplier.SupplierId = Convert.ToInt32(productSupplierDataGridView.Rows[currentRow].Cells[2].Value);
//Get new Product supplier details
int selectProduct = productDataGridView.CurrentRow.Index;
int selectSupplier = supplierDataGridView.CurrentRow.Index;
ProductSupplier productSupplier = new ProductSupplier();
productSupplier.ProductSupplierId = oldProductSupplier.ProductSupplierId;
productSupplier.ProductId = Convert.ToInt32(productDataGridView.Rows[selectProduct].Cells[0].Value);
productSupplier.SupplierId = Convert.ToInt32(supplierDataGridView.Rows[selectSupplier].Cells[0].Value);
try
{
//Check the database already having the product supplier relationship
List<ProductSupplier> productSuppliersize =
ProductSupplierDB.GetProductSupplierbyProdIdSupId(productSupplier.ProductId, productSupplier.SupplierId);
if (productSuppliersize.Count > 0)
{
lblProductSupplierInfo.Text = "The selected Product and Suppliers already exists.";
}
else // if product supplier relation is not present in database
{
lblProductSupplierInfo.Text = "";
bool updateStatus = ProductSupplierDB.UpdateProductSupplier(oldProductSupplier, productSupplier);
if (updateStatus)
{
productSupplierDataGridView.Rows[currentRow].Cells[1].Value = productSupplier.ProductId;
productSupplierDataGridView.Rows[currentRow].Cells[2].Value = productSupplier.SupplierId;
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
//Method to update productsupplier by giving new and old proudctsupplier details
public static bool UpdateProductSupplier(ProductSupplier oldProductSupplier, ProductSupplier productSupplier)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string updateStatement =
"UPDATE Products_Suppliers SET " +
"ProductId = @ProductId, " +
"SupplierId = @SupplierId " +
"WHERE ProductSupplierId = @OldProductSupplierId " +
"AND ProductId = @OldProductId " +
"AND SupplierId = @OldSupplierId";
SqlCommand updateCommand = new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue("@ProductId", productSupplier.ProductId);
updateCommand.Parameters.AddWithValue("@SupplierId", productSupplier.SupplierId);
updateCommand.Parameters.AddWithValue("@OldProductSupplierId", oldProductSupplier.ProductSupplierId);
updateCommand.Parameters.AddWithValue("@OldProductId", oldProductSupplier.ProductId);
updateCommand.Parameters.AddWithValue("@OldSupplierId", oldProductSupplier.SupplierId);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery(); //get count of updated field
if (count > 0)
return true;
else
return false;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
// Author: Linden
public static List<ProductSupplier> GetProductSuppliersByPackage(int packageId)
{
List<ProductSupplier> productSuppliers = new List<ProductSupplier>();
string qrySelect = "SELECT ps.ProductSupplierId, ps.ProductId, p.ProdName, ps.SupplierId, s.SupName " +
"FROM Packages_Products_Suppliers pps, Products_Suppliers ps, Products p, Suppliers s " +
"WHERE pps.PackageId = @PackageId AND pps.ProductSupplierId = ps.ProductSupplierId AND ps.ProductId = p.ProductId AND ps.SupplierId = s.SupplierId";
using (SqlConnection dbConn = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmdSelect = new SqlCommand(qrySelect, dbConn))
{
cmdSelect.Parameters.AddWithValue("@PackageId", packageId);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
ProductSupplier productSupplier = new ProductSupplier();
productSupplier.ProductSupplierId = Convert.ToInt32(dbReader["ProductSupplierId"]);
productSupplier.ProductId = Convert.ToInt32(dbReader["ProductId"]);
productSupplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
productSupplier.ProdName = Convert.ToString(dbReader["ProdName"]);
productSupplier.SupName = Convert.ToString(dbReader["SupName"]);
productSuppliers.Add(productSupplier);
}
dbReader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
}
return productSuppliers;
}
//Written by Geetha -- start
//Method to get productsupplier record based on productsupplierId
public static List<ProductSupplier> GetProductSupplierByProductSupplierId(int productSupplierId)
{
List<ProductSupplier> productSupplierList = new List<ProductSupplier>();
//Get connection
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductSupplierId", productSupplierId);
try
{
connection.Open();
//Read the data from the selectCommand query result
SqlDataReader reader = selectCommand.ExecuteReader();
//get the result and set it in productsupplier object
while (reader.Read())
{
ProductSupplier productSupplier = new ProductSupplier();
productSupplier.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
productSupplier.ProductId = Convert.ToInt32(reader["ProductId"]);
productSupplier.SupplierId = Convert.ToInt32(reader["SupplierId"]);
productSupplierList.Add(productSupplier);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
//return the productSupplierList
return productSupplierList;
}
