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); }