// returns an int for a new supplier id
public static int GetSupplierId()
{
SqlConnection connection = TravelExpertsDB.GetConnection();
SqlTransaction getidTransaction = null;
string selectQuery = "select max(SupplierId) as MaxID from Suppliers ";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
try
{
// open connection
connection.Open();
getidTransaction = connection.BeginTransaction();
selectCommand.Transaction = getidTransaction;
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
return((int)reader["MaxID"] + 1);
}
}
catch (Exception ex)
{
throw ex; // pass the buck
}
finally
{
connection.Close();
}
return(-1);// supplier table is empty.there is no recorder in it
}
//delete data from database
public static bool DeleteSupplier(Supplier oldsupplier)
{
bool successful = false;
SqlConnection connection = TravelExpertsDB.GetConnection();
SqlTransaction deleteTransaction = null;
try
{
// open connection
connection.Open();
deleteTransaction = connection.BeginTransaction();
SqlDataAdapter sda = new SqlDataAdapter("delete from Suppliers where SupplierId = '" + oldsupplier.SupplierId.ToString() + "'", connection);
sda.SelectCommand.Transaction = deleteTransaction;
int count = sda.SelectCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
deleteTransaction.Commit();
MessageBox.Show("Successfully deletes the selected supplier!!");
}
catch (Exception ex)
{
deleteTransaction.Rollback();
throw ex; // pass the buck
}
finally
{
connection.Close();
}
return(successful);
}
//updates existingsupplier record
public static bool UpdateSupplier(Supplier oldSupplier, Supplier newSupplier)
{
bool successful = false;
SqlConnection connection = TravelExpertsDB.GetConnection();
string updateString = "update Suppliers set SupName = @SupName where SupplierId = @SupplierId";
SqlCommand updateCommand = new SqlCommand(updateString, connection);
updateCommand.Parameters.AddWithValue("@SupName", newSupplier.SupName);
updateCommand.Parameters.AddWithValue("@SupplierId", oldSupplier.SupplierId);
try
{
// open connection
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
successful = true;
}
}
catch (Exception ex)
{
throw ex; // pass the buck
}
finally
{
connection.Close();
}
return(successful);
}
public static List <ProductSupplier> GetSuppliersForSelectedProduct(int productID)//return all selected suppliers for a specific product
{
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 + "' ";
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> GetAllSuppliersForSelectedProduct(int productID)//return a list of productsupplier corresponding to a specific productname
{
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 " + //joint PS table and Supplier Table
"and ps.ProductId = prod.ProductId " + //joint PS table and Prodcut Table
"and ps.ProductId ='" + productID + "' " + //set ProdcutID
"order by sup.SupName";
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();
// get PS object value from database
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; // display exception information
}
finally
{
connection.Close();
}
return(productsSuppliers);
}
public static List <ProductSupplier> GetAllProductsSuppliers()//get all of products and suppliers which has relationship
{
List <ProductSupplier> productsSuppliers = new List <ProductSupplier>();
// define connection of database
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 " + //join table supplier
"inner join Products prod on ps.ProductId = prod.ProductId"; //join table products
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();
// get PS object property values from database
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; // display exception information
}
finally
{
connection.Close();
}
return(productsSuppliers);//return List
}
public static List <Product> SearchAllProducts(string productName)
{
//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 where ProdName LIKE '%' + @ProdName + '%' ";
//defining the command
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@ProdName", productName);
//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 List <Supplier> GetAllsuppliers()
{
//create an empty list
List <Supplier> suppliers = new List <Supplier>();
Supplier supplier1;
//defining connection
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();
//process the result
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 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 DeleteProductSupplier(int ProductSupplierId)//delete selected row
{
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", ProductSupplierId);
try
{
// open the connection
connection.Open();
count = Convert.ToInt32(deleteCommand.ExecuteScalar());
if (count >= 1)
{
successfull = false;
}
else
{
successfull = true;
}
}
catch (Exception)
{
// throw ex; // let the form handle it
MessageBox.Show("Your selection is referenced in the other table ,so please contact to DataAdiministrator!");
}
finally
{
connection.Close(); // close connecto no matter what
}
return(successfull);
}
public static int getProductSupplierId(int productid, int supplierid)//return the ProductSupplierID in PRoductSupplier table
{
int ProductSupplierId = -1;
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query
string selectQuery = "select ProductSupplierId from Products_Suppliers where ProductID=@productid and SupplierID=@supplierid";
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@productid", productid);
selectCommand.Parameters.AddWithValue("@supplierid", supplierid);
// Connect to DB
try
{
// open the connection
connection.Open();
// execute query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// process the result
if (reader.Read())
{
ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close();
}
return(ProductSupplierId);
}
public static bool DeletePackageProductSupplier(int ProductSupplierId)
{
bool successfull = false;
int count;
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query command
string deleteStatement = "delete from Packages_Products_Suppliers WHERE ProductSupplierId = @productsupplierid";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("@productsupplierid", 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);
}
//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)";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
insertCommand.Parameters.AddWithValue("@SupplierId", supplierid);
insertCommand.Parameters.AddWithValue("@SupName", supname);
int i = insertCommand.ExecuteNonQuery();
if (i == 1)
{
successful = true;
}
}
catch (Exception ex)
{
throw ex; // pass the buck
}
finally
{
connection.Close();//close connect
}
return(successful);
}
public static bool referencedInPackages(int deleteproductsupplierid)//check there is referenced in package table when delete row
{
bool refPSinPackages = false;
string selectQuery = "select * from Packages_Products_Suppliers where ProductSupplierID=@deleteproductsupplierid ";
SqlConnection connection = TravelExpertsDB.GetConnection();
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@deleteproductsupplierid", deleteproductsupplierid);
// Connect to DB
try
{
// open the connection
connection.Open();
// execute query
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// process the result
if (reader.Read())
{
refPSinPackages = true;//referenced in Package_Prodcut_Supplier Table
}
}
catch (Exception ex)
{
throw ex; // let the form handle it
}
finally
{
connection.Close();
}
return(refPSinPackages);
}
public static bool AddProductsSuppliers(int productId, int supplierId)//insert a new productSupplier row in table of ProdcutSupplier table
{
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 UpdateProductSupplier(int NewSupplierId, int OldSupplierId, int ProductId)//updated selected row
{
bool successful = false;
SqlConnection conn = TravelExpertsDB.GetConnection();
// string updateString = "Update Products_Suppliers set SupplierId = @NewSupplierId where ProductSupplierId = @ProductSupplierID";
string updateString = "Update Products_Suppliers set SupplierId = @NewSupplierId where SupplierID = @OldSupplierId and ProductID=@ProductId";
SqlCommand updateCommand = new SqlCommand(updateString, conn);
updateCommand.Parameters.AddWithValue("@NewSupplierId", NewSupplierId);
updateCommand.Parameters.AddWithValue("@OldSupplierId", OldSupplierId);
updateCommand.Parameters.AddWithValue("@ProductId", ProductId);
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);
}
public static List <ProductSupplier> SearchAllSuppliersForSelectedProduct(int productID, string supplierName)//return a list of productsupplier objects corresponding to speicfic product and supplier
{
List <ProductSupplier> productsSuppliers = new List <ProductSupplier>();
// define connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// define the select query
string selectQuery = "";
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 " + //joint PS table and Supplier Table
"and ps.ProductId = prod.ProductId " + //join PS table and Product Table
"and ps.ProductId ='" + productID + "' " + //set ProductID
"and sup.SupName LIKE '%' + @ProdName + '%' "; //fuzzy querey using "%"
if (supplierName == "") //select all of products when not input specific character in textbox
{
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 " + //join PS table and Supplier Table
"and ps.ProductId = prod.ProductId " + //join PS table and Product Table
"and ps.ProductId ='" + productID + "' ";
}
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
selectCommand.Parameters.AddWithValue("@ProdName", supplierName);
// 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 PS object properties from database
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; // display exception information
}
finally
{
connection.Close();
}
return(productsSuppliers);
}