public static int AddProductSupplier(suppliers prosup)
{
int prosupID = 0;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create INSERT command
// ProductSupplierID is IDENTITY so no value provided
string insertStatement =
"INSERT INTO Products_Suppliers(ProductId, SupplierId) OUTPUT inserted.ProductSupplierId VALUES(@ProductId, @SupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@ProductId", prosup.ProductId);
cmd.Parameters.AddWithValue("@SupplierId", prosup.SupplierId);
try
{
connection.Open();
// execute insert command and get inserted ID
prosupID = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(prosupID);
}
public static suppliers GetProductSupplierId(int prosupID)
{
suppliers prosup = null;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create SELECT command
string query = " SELECT * FROM Products_Suppliers WHERE ProductSupplierId = @ProductSupplierId";
// string query =
//"SELECT p.ProductId, p.ProdName, s.SupplierId, s.SupName, ps.ProductSupplierId FROM Products as p JOIN Products_Suppliers as ps on p.ProductId = ps.ProductId join Suppliers s on ps.SupplierId = s.SupplierId WHERE ProductSupplierId = @ProductSupplierId";
SqlCommand cmd = new SqlCommand(query, connection);
// supply parameter value
cmd.Parameters.AddWithValue("@ProductSupplierId", prosupID);
// run the SELECT query
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
// build customer object to return
if (reader.Read()) // if there is a customer with this ID
{
prosup = new suppliers
{
ProductSupplierId = (int)reader["ProductSupplierId"],
ProductId = (int)reader["ProductId"],
SupplierId = (int)reader["SupplierId"]
//ProdName = (string)reader["ProdName"],
//SupName = (string)reader["SupName"]
};
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(prosup);
}
//public static class Products_suppliersDB
//{
public static List <suppliers> GetSuppliers()
{
List <suppliers> productSuppliers = new List <suppliers>(); //empty list
suppliers pro; //just for reading
//create the connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//create the command for SELECT query to get the product suppliers
string query = "SELECT * FROM Products_Suppliers ";
SqlCommand selectCmd = new SqlCommand(query, connection);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = selectCmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) //while there still is data to read
{
pro = new suppliers
{
ProductSupplierId = (int)reader["ProductSupplierId"],
ProductId = (int)reader["ProductId"],
SupplierId = (int)reader["SupplierId"]
};
productSuppliers.Add(pro);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of product
return(productSuppliers);
}
public static bool DeleteProductSupplier(suppliers prosup)
{
bool success = false;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create DELETE command
string deleteStatement =
"DELETE FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId " + // needed for identification
"AND ProductId = @ProductId " + // the rest - for optimistic concurrency
"AND SupplierId = @SupplierId ";
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@ProductSupplierId", prosup.ProductSupplierId);
cmd.Parameters.AddWithValue("@ProductId", prosup.ProductId);
cmd.Parameters.AddWithValue("@SupplierId", prosup.SupplierId);
try
{
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
public static int AddProductSupplier(suppliers prosup)
{
int prosupID = 0;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create INSERT command
// ProductSupplierID is IDENTITY so no value provided
string insertStatement =
"INSERT INTO Products_Suppliers(ProductId, SupplierId) OUTPUT inserted.ProductSupplierId VALUES(@ProductId, @SupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@ProductId", prosup.ProductId);
cmd.Parameters.AddWithValue("@SupplierId", prosup.SupplierId);
try
{
connection.Open();
// execute insert command and get inserted ID
prosupID = (int)cmd.ExecuteScalar();
//cmd.ExecuteNonQuery();
// retrieve generate customer ID to return
//string selectStatement =
// "SELECT IDENT_CURRENT('Customers')";
//SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
//custID = Convert.ToInt32(selectCmd.ExecuteScalar()); // returns single value
// // (int) does not work in this case
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(prosupID);
}
public static bool UpdateProductSupplier(suppliers oldprosup, suppliers newprosup)
{
bool success = false; // did not update
// connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// update command
string updateStatement =
"UPDATE Products_Suppliers SET ProductId = @NewProductId, SupplierId = @NewSupplierId " +
"WHERE ProductSupplierId = @OldProductSupplierId " +
"AND ProductId = @OldProductId " +
"AND SupplierId = @OldSupplierId";
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewProductId ", newprosup.ProductId);
cmd.Parameters.AddWithValue("@NewSupplierId", newprosup.SupplierId);
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldprosup.ProductSupplierId);
cmd.Parameters.AddWithValue("@OldProductId", oldprosup.ProductId);
cmd.Parameters.AddWithValue("@OldSupplierId", oldprosup.SupplierId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
public static suppliers GetSuppliers(string Id, string Id1)
{
suppliers productSuppliers;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create SELECT command
string query = "SELECT ProductId, SupplierId FROM Products_Suppliers WHERE ProductId = @ProductId AND SupplierId = @SupplierId ";
SqlCommand cmd = new SqlCommand(query, connection);
productSuppliers = new suppliers();
cmd.Parameters.AddWithValue("@ProductId", int.Parse(Id));
cmd.Parameters.AddWithValue("@SupplierId", int.Parse(Id1));
try
{
connection.Open();
// execute the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// check if successful
if (reader.Read())
{
productSuppliers.ProductId = (int)reader["ProductId"];
productSuppliers.SupplierId = (int)reader["SupplierId"];
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(productSuppliers);
}
public static bool UpdateSupplier(Suppliers supplier, suppliers newSupplier)
{
throw new NotImplementedException();
}