public static int UpdateProductSupplier(Products_suppliers oldProdSupplier, Products_suppliers newProdSupplier)
{
int count = 0;
string updateStatement = "UPDATE Products_Suppliers SET " +
"ProductId = @ProductId, " +
"SupplierId = @SupplierId " +
"WHERE ProductSupplierId = @ProductSupplierId " +
"AND (ProductId = @ProductId " +
" OR ProductId IS NULL AND @OldProductId IS NULL)" +
"AND (SupplierId = @SupplierId " +
" OR SupplierId IS NULL AND @SupplierId IS NULL)";
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(updateStatement, con))
{
if (newProdSupplier.ProductId == null)
{
cmd.Parameters.AddWithValue("@NewProductId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewProductId", newProdSupplier.ProductId);
}
if (newProdSupplier.SupplierId == null)
{
cmd.Parameters.AddWithValue("@NewSupplierId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewSupplierId", newProdSupplier.SupplierId);
}
cmd.Parameters.AddWithValue("@ProductSupplierId", oldProdSupplier.ProductSupplierId); // PK is not null
if (oldProdSupplier.ProductId == null)
{
cmd.Parameters.AddWithValue("@OldProductId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldProductId", oldProdSupplier.ProductId);
}
if (oldProdSupplier.SupplierId == null)
{
cmd.Parameters.AddWithValue("@OldSupplierId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldSupplierId", oldProdSupplier.SupplierId);
}
try
{
con.Open();
count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
}
return(count);
}
// select products that are not yet included in packages
public static List <AvailableProducts> GetAvailableProducts(string search = "")
{
List <AvailableProducts> products = new List <AvailableProducts>();
AvailableProducts prod = null;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectQuery = "SELECT q.ProductSupplierId, p.ProdName, s.SupName " +
"FROM Products p, Products_Suppliers q, Suppliers s " +
"WHERE p.ProductId = q.ProductId and " +
"s.SupplierId = q.SupplierId and ProductSupplierId NOT IN " +
"(SELECT ProductSupplierId FROM Packages_Products_Suppliers) ";
// check search string
if (search != "")
{
// update query
selectQuery += " AND (p.ProdName LIKE @search or s.SupName LIKE @search)";
}
// add order to query
selectQuery += " ORDER BY ProdName";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
// check search
if (search != "")
{
// bind
cmd.Parameters.AddWithValue("@search", "%" + search + "%");
}
// check
try
{
// open the connection
connection.Open();
// execute the SELECT query
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read()) // we have a customer
{
// create new object
prod = new AvailableProducts();
prod.ProductSupplierId = (int)dr["ProductSupplierId"];
prod.ProdName = (string)dr["ProdName"];
prod.SupName = (string)dr["SupName"];
products.Add(prod);
}
dr.Close();
return(products);
}
catch (SqlException ex)
{
throw ex;
}
}