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