public static List <ProductSupplier> GetOneSuppliersForSelectedProduct(int productID, int supplierID)//return a single row according to ProductID and SupplierID
{
List <ProductSupplier> productsSuppliers = new List <ProductSupplier>();
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query
string selectQuery = "select ps.ProductSupplierId, ps.ProductID, ps.SupplierID, sup.SupName, prod.ProdName " +
"from Suppliers sup, Products_Suppliers ps, Products prod " +
"where ps.SupplierId = sup.SupplierId " +
"and ps.ProductId = prod.ProductId " +
"and ps.ProductId ='" + productID + "' "
+ "and ps.SupplierId='" + supplierID + "'";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
// Connect to DB
try
{
// open the connection
connection.Open();
// execute query
SqlDataReader reader = selectCommand.ExecuteReader();
// process the result
while (reader.Read())
{
ProductSupplier ps = new ProductSupplier();
// Assign properties to order
ps.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
ps.ProductID = Convert.ToInt32(reader["ProductID"]);
ps.SupplierID = Convert.ToInt32(reader["SupplierID"]);
ps.ProductName = reader["ProdName"].ToString();
ps.SupplierName = reader["SupName"].ToString();
// Add to list
productsSuppliers.Add(ps);
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close();
}
return(productsSuppliers);
}
public static List <ProductSupplier> GetAllProductsSuppliers()//find relationship
{
List <ProductSupplier> productsSuppliers = new List <ProductSupplier>();
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query
string selectQuery = "select ps.ProductSupplierId, ps.ProductID, ps.SupplierID, sup.SupName, prod.ProdName " +
"from Products_Suppliers ps " +
"inner join Suppliers sup on ps.SupplierId = sup.SupplierId " +
"inner join Products prod on ps.ProductId = prod.ProductId";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
// Connect to DB
try
{
// open the connection
connection.Open();
// execute query
SqlDataReader reader = selectCommand.ExecuteReader();
// process the result
while (reader.Read())
{
ProductSupplier ps = new ProductSupplier();
// Assign properties to order
ps.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
ps.ProductID = Convert.ToInt32(reader["ProductID"]);
ps.SupplierID = Convert.ToInt32(reader["SupplierID"]);
ps.ProductName = reader["ProdName"].ToString();
ps.SupplierName = reader["SupName"].ToString();
// Add to list
productsSuppliers.Add(ps);
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close();
}
return(productsSuppliers);
}
public static bool DeleteProductSupplier(ProductSupplier currentPS)
{
bool successfull = false;
int count;
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string deleteStatement = "delete from Products_Suppliers WHERE ProductSupplierId = @productsupplierid";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@productsupplierid", currentPS.ProductSupplierId);
try
{
// open the connection
connection.Open();
count = Convert.ToInt32(deleteCommand.ExecuteScalar());
if (count >= 1)
{
successfull = false;
}
else
{
successfull = true;
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close(); // close connecto no matter what
}
return(successfull);
}
public static bool UpdateProductSupplier(int NewSupplierId, ProductSupplier currentPS)
{
bool successful = false;
SqlConnection conn = TravelExpertsDB.GetConnection();
string updateString = "Update Products_Suppliers set SupplierId = @NewSupplierId where ProductSupplierId = @ProductSupplierID";
SqlCommand updateCommand = new SqlCommand(updateString, conn);
updateCommand.Parameters.AddWithValue("@NewSupplierId", NewSupplierId);
updateCommand.Parameters.AddWithValue("@ProductSupplierID", currentPS.ProductSupplierId);
try
{
conn.Open();
int count = updateCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
else
{
successful = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(successful);
}
