public static List <Product> GetOffedProducts(Supplier selectedsupplier) { List <Product> offedprodcuts = new List <Product>(); // define connection SqlConnection connection = TravelExpertsDB.GetConnection(); // define the select query string selectQuery = "select ps.ProductSupplierId, ps.ProductID, ps.SupplierID, sup.SupName, prod.ProdName, ps.Enabled " + "from Products_Suppliers ps " + "inner join Suppliers sup on ps.SupplierId = sup.SupplierId " + "inner join Products prod on ps.ProductId = prod.ProductId " + "where sup.SupplierId='" + selectedsupplier.SupplierId.ToString() + "'"; 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(); Product ps = new Product(); // Assign properties to order //ps.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]); //ps.ProductID = Convert.ToInt32(reader["ProductID"]); // ps.SupplierID = Convert.ToInt32(reader["SupplierID"]); //ps.ProdName = reader["ProdName"].ToString(); // ps.SupplierName = reader["SupName"].ToString(); //ps.Enabled = (bool)reader["Enabled"]; ps.ProductID = Convert.ToInt32(reader["ProductID"]); ps.ProdName = reader["ProdName"].ToString(); // Add to list offedprodcuts.Add(ps); } } catch (Exception ex) { throw ex; // let the form handle it } finally { connection.Close(); } return(offedprodcuts); }
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 List <Supplier> GetAllsuppliers() //method that creates a list from db { //create an empty list List <Supplier> suppliers = new List <Supplier>(); Supplier supplier1; // sql statement to connect with database SqlConnection connection = TravelExpertsDB.GetConnection(); //defining the query string selectQuery = "select * from Suppliers order by SupplierID asc"; //defining the command SqlCommand selectCommand = new SqlCommand(selectQuery, connection); //connection to database try { //open the connection connection.Open(); //execute the command SqlDataReader reader = selectCommand.ExecuteReader(); //while there is data to read, read it and add to list while (reader.Read()) { supplier1 = new Supplier(); supplier1.SupplierId = Convert.ToInt32(reader["SupplierID"]); supplier1.SupName = reader["SupName"].ToString(); // Add to list suppliers.Add(supplier1); } } //exception handling from the program catch (Exception ex) { throw ex; } //always closing the connection finally { connection.Close(); } return(suppliers); }
public static List <Product> GetAllProducts() { //create an empty list List <Product> products = new List <Product>(); Product product; //defining connection SqlConnection connection = TravelExpertsDB.GetConnection(); //defining the query string selectQuery = "Select * from Products"; //defining the command SqlCommand selectCommand = new SqlCommand(selectQuery, connection); //connection to database try { //open the connection connection.Open(); //execute the command SqlDataReader reader = selectCommand.ExecuteReader(); //process the result while (reader.Read()) { product = new Product(); product.ProductID = Convert.ToInt32(reader["ProductID"]); product.ProdName = reader["ProdName"].ToString(); // Add to list products.Add(product); } } //exception handling from the program catch (Exception ex) { throw ex; } //always closing the connection finally { connection.Close(); } return(products); }
public static int GetProductsSuppliersId(int productId, int supplierId) { int id = 0; // prepare connection SqlConnection connection = TravelExpertsDB.GetConnection(); // Build the SQL query string queryString = "select ProductSupplierId from Products_Suppliers where ProductID = @productId and SupplierId = @supplierId"; // Create command from the query SqlCommand selectCommand = new SqlCommand(queryString, connection); selectCommand.Parameters.AddWithValue("@ProductId", productId); selectCommand.Parameters.AddWithValue("@SupplierId", supplierId); try { // open connection connection.Open(); // execute the statement SqlDataReader reader = selectCommand.ExecuteReader(); // process the result while (reader.Read()) { id = Convert.ToInt32(reader["ProductSupplierId"]); } } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(id); }
public static bool AddProductsSuppliers(int productId, int supplierId) { bool successful = false; // prepare connection SqlConnection connection = TravelExpertsDB.GetConnection(); // Build the SQL query string insertString = "INSERT INTO Products_Suppliers (ProductId, SupplierId) VALUES (@ProductId, @SupplierId)"; // Create command from the query SqlCommand insertCommand = new SqlCommand(insertString, connection); insertCommand.Parameters.AddWithValue("@ProductId", productId); insertCommand.Parameters.AddWithValue("@SupplierId", supplierId); try { // open connection connection.Open(); // execute the statement int i = insertCommand.ExecuteNonQuery(); if (i == 1) { successful = true; } } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(successful); }
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); }
//to check if there is reference with other table. foreign key public static bool referenceCheck(int deletesupplierid, string tablename) { bool refSupplier = false; string selectQuery = "select * from " + tablename + " where SupplierId=@deletesupplierid "; //query to check SqlConnection connection = TravelExpertsDB.GetConnection(); //connects to db SqlCommand selectCommand = new SqlCommand(selectQuery, connection); //selects command selectCommand.Parameters.AddWithValue("@deletesupplierid", deletesupplierid); // Connect to DB try { // open the connection connection.Open(); // execute query SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); // process the result if (reader.Read()) { refSupplier = true;//referenced in Package_Prodcut_Supplier Table. If is foreign key to other table. } } catch (Exception ex) { throw ex; // let the form handle it } finally { connection.Close(); } return(refSupplier); }
//add new supplier public static bool AddSupplier(int supplierid, string supname) { bool successful = false; SqlConnection connection = TravelExpertsDB.GetConnection(); if (supplierid == 0) { supplierid += 1; //empty table this will be the first recorder in supplier table } try { // open connection connection.Open(); string insertString = "INSERT into Suppliers (SupplierId, SupName) values (@SupplierId, @SupName)"; //add data to DB SqlCommand insertCommand = new SqlCommand(insertString, connection); insertCommand.Parameters.AddWithValue("@SupplierId", supplierid); insertCommand.Parameters.AddWithValue("@SupName", supname); int i = insertCommand.ExecuteNonQuery(); //insert command if (i == 1) { successful = true; //if insert true return successful } } catch (Exception ex) { throw ex; // exception handled } finally { connection.Close();//close connection } return(successful); }
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); }