public static ProductSupplier GetProductSupplierById(int productSupplierID)
{
int tempSupId;
int tempProdId;
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement =
"SELECT ProductSupplierId, ProductID,SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProductSupplierId", productSupplierID);
try
{
connection.Open();
SqlDataReader Reader = selectCommand.ExecuteReader
(CommandBehavior.SingleRow); // selecting by PK value
if (Reader.Read())
{ // we have a customer
ProductSupplier productSupplier = new ProductSupplier();
productSupplier.ProductSupplierId = (int)Reader["ProductSupplierId"];
tempSupId = (int)Reader["SupplierId"];
tempProdId = (int)Reader["ProductId"];
connection.Close();
productSupplier.Supplier = SupplierDB.GetSupplierByID(tempSupId);
productSupplier.Product = ProductDB.GetEntityById(tempSupId);
return(productSupplier);
}
else // no customer
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <ProductSupplier> GetProductSuppliers()
{
List <ProductSupplier> ProdSupList = new List <ProductSupplier>();
string connectionString = "Data Source=localhost\\SAIT;Initial Catalog=TravelExperts;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionString);
string selectStatement = "SELECT ProductSupplierId ,ps.ProductId as ProductId, ProdName, ps.SupplierId as SupplierId, SupName " +
"FROM Products_Suppliers as ps " +
"INNER JOIN Products as p ON p.ProductId = ps.ProductId " +
"INNER JOIN Suppliers as s on s.SupplierId = ps.SupplierId " +
"GROUP BY ProductSupplierId, ps.SupplierId, ps.ProductId, p.ProdName, s.SupName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
ProductSupplier productSupplier = new ProductSupplier();
Product product = new Product();
Supplier supplier = new Supplier();
productSupplier.ProductSupplierId = (int)reader["ProductSupplierId"];
product.ProductId = (int)reader["ProductId"];
product.Name = (string)reader["ProdName"];
supplier.SupplierId = (int)reader["SupplierId"];
supplier.Name = (string)reader["SupName"];
productSupplier.Product = product;
productSupplier.Supplier = supplier;
ProdSupList.Add(productSupplier);
}
connection.Close();
foreach (ProductSupplier ProdSup in ProdSupList)
{
ProdSup.Product = ProductDB.GetEntityById(ProdSup.Product.ProductId);
ProdSup.Supplier = SupplierDB.GetSupplierByID(ProdSup.Supplier.SupplierId);
}
}
catch (Exception)
{
throw;
}
return(ProdSupList);
}