/// <summary>
/// Public static method gets Suppliers from database, builds Supplier objects from
/// their returned info, then loads each object into a list
/// </summary>
/// <returns>list of Supplier objects</returns>
public static List<Supplier> GetSuppliers()
{
List<Supplier> suppliers = new List<Supplier>();
Supplier supplier; // for reading
//Sql connection block to connect to TravelExpertsDB; closes connection at end of block
//Executes query to retrieve SupplierId's
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT * " +
"FROM Suppliers " +
"ORDER BY SupplierId";
//sql command block; disposes command at end of block
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);//new sqldatareader for accessing db
while (reader.Read())
{
// Create new supplier with current row information
supplier = new Supplier((int)reader["SupplierId"], reader["SupName"].ToString());
suppliers.Add(supplier); // add to the list
}
} // cmd object recycled
}// connection object recycled
SqlConnection connection2 = TravelExpertsDB.GetConnection();//create new sql connection
//for loop that iterates through the created list of Supplier objects
for (int i = 0; i < suppliers.Count(); i++)
{
//runs a query that retrieves Products associated with each Supplier
String subQuery = "SELECT p.ProductId, p.ProdName " +
"FROM Products p JOIN Products_Suppliers ps " +
"ON p.ProductId = ps.ProductId " +
"WHERE ps.SupplierId = @SupplierId; ";
//clear current Suppliers's list property for Products
//to be loaded with the retrieved Products
suppliers[i].Products.Clear();
//query that populates each retrieved Product's information into the Supplier
//object's class property for Products
//sql command block; disposes command at end of block
using (SqlCommand subCmd = new SqlCommand(subQuery, connection2))
{
connection2.Open();
//define and add sql parameter for our sqlcommand
subCmd.Parameters.AddWithValue("@SupplierId", suppliers[i].SupplierId);
SqlDataReader subReader =
subCmd.ExecuteReader(CommandBehavior.CloseConnection);//new sqldatareader for accessing db
while (subReader.Read())
{
//add a new product to the current supplier
suppliers[i].Products.Add(new Product((int)subReader["ProductId"], subReader["ProdName"].ToString()));
}
connection2.Close();
}
}
return suppliers;
}
// Update Package: oldPackage - before update, newPackage - new data
public static bool UpdatePackage(Packages oldPackage, Packages newPackage)
{
bool success = false;
// create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string updateStatement =
"UPDATE Packages SET " +
"PkgName=@NewPkgName, " +
"PkgStartDate=@NewPkgStartDate, " +
"PkgEndDate=@NewPkgEndDate, " +
"PkgDesc=@NewPkgDesc, " +
"PkgBasePrice=@NewPkgBasePrice, " +
"PkgAgencyCommission=@NewPkgAgencyCommission " +
"WHERE PackageId=@OldPackageId " + // need for identification
"AND PkgName=@OldPkgName " +
"AND (PkgStartDate=@OldPkgStartDate OR PkgStartDate IS NULL AND @OldPkgStartDate IS NULL) " +
"AND (PkgEndDate=@OldPkgEndDate OR PkgEndDate IS NULL AND @OldPkgEndDate IS NULL) " +
"AND (PkgDesc=@OldPkgDesc OR PkgDesc IS NULL AND @OldPkgDesc IS NULL) " +
"AND PkgBasePrice=@OldPkgBasePrice " +
"AND (PkgAgencyCommission = @OldPkgAgencyCommission OR PkgAgencyCommission IS NULL AND @OldPkgAgencyCommission IS NULL) ";
using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
{
//supply paramter value, this way can avoid sql injection problem
cmd.Parameters.AddWithValue("@OldPackageId", oldPackage.PackageId);
cmd.Parameters.AddWithValue("@NewPkgName", newPackage.PkgName);
cmd.Parameters.AddWithValue("@NewPkgDesc", newPackage.PkgDesc);
cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPackage.PkgBasePrice);
if (newPackage.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@NewPkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgStartDate", (DateTime)newPackage.PkgStartDate);
}
if (newPackage.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@NewPkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgEndDate", (DateTime)newPackage.PkgEndDate);
}
if (newPackage.PkgAgencyCommission.Equals(null))
{
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", Convert.ToDecimal(newPackage.PkgAgencyCommission));
}
cmd.Parameters.AddWithValue("@OldPkgName", oldPackage.PkgName);
cmd.Parameters.AddWithValue("@OldPkgDesc", oldPackage.PkgDesc);
cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPackage.PkgBasePrice);
if (oldPackage.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@OldPkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgStartDate", (DateTime)oldPackage.PkgStartDate);
}
if (oldPackage.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@OldPkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgEndDate", (DateTime)oldPackage.PkgEndDate);
}
if (oldPackage.PkgAgencyCommission.Equals(null))
{
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", Convert.ToDecimal(oldPackage.PkgAgencyCommission));
}
try
{
// open the connection
connection.Open();
// execute the delete command
int count = cmd.ExecuteNonQuery(); // returns the number of rows affected
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
}
}
//return the indicator of success
return(success);
}
// delete a product from Products table
// return indicator of success
public static bool DeleteProduct(Products product)
{
bool success = false;
// create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string deleteStatement1 =
"DELETE FROM Packages_Products_Suppliers " +
"WHERE ProductSupplierId IN " +
"(SELECT ProductSupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductId=@ProductId)";
string deleteStatement2 =
"DELETE FROM Products_Suppliers " +
"WHERE ProductId=@ProductId";
string deleteStatement3 =
"DELETE FROM Products " +
"WHERE ProductId=@ProductId " + // need for identification
"AND ProdName=@ProdName"; // the AND is to ensure no one is updating this product
connection.Open();
// start a location transaction
SqlTransaction sqlTran = connection.BeginTransaction();
// Enlist a command in the current transaction
SqlCommand command = connection.CreateCommand();
command.Transaction = sqlTran;
//supply paramter value, this way can avoid sql injection problem
command.Parameters.AddWithValue("@ProductId", product.ProductId);
command.Parameters.AddWithValue("@ProdName", product.ProdName);
try
{
// Execute three separate commands
command.CommandText = deleteStatement1;
command.ExecuteNonQuery();
command.CommandText = deleteStatement2;
command.ExecuteNonQuery();
command.CommandText = deleteStatement3;
command.ExecuteNonQuery();
// Commit the transaction
sqlTran.Commit();
success = true;
}
catch (Exception)
{
try
{
//Atttemp to roll back the transaction
sqlTran.Rollback();
success = false;
}
catch (Exception exRollback)
{
throw exRollback;
}
}
finally // executes always
{
connection.Close();
}
}
//return the indicator of success
return(success);
}
// insert a new package into Packages table
// return new package Id
public static int AddPackage(Packages package)
{
int packageId = 0;
// create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatement = "INSERT INTO Packages(PkgName, PkgStartDate, " +
" PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"OUTPUT inserted.PackageId " +
"VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
//supply paramter value, this way can avoid sql injection problem
cmd.Parameters.AddWithValue("@PkgName", package.PkgName);
if (package.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", (DateTime)package.PkgStartDate);
}
if (package.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", (DateTime)package.PkgEndDate);
}
cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice);
if (package.PkgAgencyCommission.Equals(null))
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission);
}
// execute INSERT command
try
{
// open the connection
connection.Open();
// execute insert command and get inserted ID
packageId = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
}
}
return(packageId);
}
// delete a package from Packages table
// return indicator of success
public static bool DeletePackage(Packages package)
{
bool success = false;
// create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string deleteStatement1 =
"DELETE FROM Packages_Products_Suppliers " +
"WHERE PackageId=@PackageId ";
string deleteStatement2 =
"DELETE FROM Packages " +
"WHERE PackageId=@PackageId " + // need for identification
"AND PkgName=@PkgName " +
"AND (PkgStartDate=@PkgStartDate OR PkgStartDate IS NULL AND @PkgStartDate IS NULL) " +
"AND (PkgEndDate=@PkgEndDate OR PkgEndDate IS NULL AND @PkgEndDate IS NULL) " +
"AND (PkgDesc = @PkgDesc OR PkgDesc IS NULL AND @PkgDesc IS NULL) " +
"AND PkgBasePrice=@PkgBasePrice " +
"AND (PkgAgencyCommission=@PkgAgencyCommission OR PkgAgencyCommission IS NULL AND @PkgAgencyCommission IS NULL) ";
connection.Open();
// start a location transaction
SqlTransaction sqlTran = connection.BeginTransaction();
// Enlist a command in the current transaction
SqlCommand cmd = connection.CreateCommand();
cmd.Transaction = sqlTran;
//supply paramter value, this way can avoid sql injection problem
cmd.Parameters.AddWithValue("@PackageId", package.PackageId);
cmd.Parameters.AddWithValue("@PkgName", package.PkgName);
if (package.PkgStartDate == null)
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", package.PkgStartDate);
}
if (package.PkgEndDate == null)
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", package.PkgEndDate);
}
cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice);
if (package.PkgAgencyCommission.Equals(null))
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission);
}
try
{
// Execute three separate commands
cmd.CommandText = deleteStatement1;
cmd.ExecuteNonQuery();
cmd.CommandText = deleteStatement2;
cmd.ExecuteNonQuery();
// Commit the transaction
sqlTran.Commit();
success = true;
}
catch (Exception)
{
try
{
//Atttemp to roll back the transaction
sqlTran.Rollback();
success = false;
}
catch (Exception exRollback)
{
throw exRollback;
}
}
finally // executes always
{
connection.Close();
}
}
//return the indicator of success
return(success);
}
// retrieve package info with given package ID
public static Packages GetPackageById(int packageId)
{
Packages package = null;
//create the conneciton
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create select command
string query = "SELECT PackageId, PkgName,PkgStartDate, PkgEndDate, " +
"PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId=@PackageId";
// any exception not handled here is automaticlly thrown to the form
// where the method was called
using (SqlCommand cmd = new SqlCommand(query, connection))
{
//supply parameter value
cmd.Parameters.AddWithValue("@PackageId", packageId);
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// close connection as soon as done with reading
//build product object to return
if (reader.Read()) // if there is a product with this ID
{
package = new Packages();
package.PackageId = (int)reader["PackageId"];
package.PkgName = reader["PkgName"].ToString();
int col_st = reader.GetOrdinal("PkgStartDate"); //column number of Start Date
if (reader.IsDBNull(col_st)) // if reader contains DBNull in this column
{
package.PkgStartDate = null; // make it null in the object
}
else // it is not null
{
package.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]);
}
int col_en = reader.GetOrdinal("PkgEndDate"); //column number of End Date
if (reader.IsDBNull(col_en)) // if reader contains DBNull in this column
{
package.PkgEndDate = null; // make it null in the object
}
else // it is not null
{
package.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]);
}
package.PkgDesc = reader["PkgDesc"].ToString();
package.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]);
int col_comm = reader.GetOrdinal("PkgAgencyCommission"); //column number of End Date
if (reader.IsDBNull(col_comm)) // if reader contains DBNull in this column
{
package.PkgEndDate = null; // make it null in the object
}
else // it is not null
{
package.PkgAgencyCommission = (Decimal)reader["PkgAgencyCommission"];
}
}
} // command object recycled
} // connection object recyled
return(package);
}
// get all Packages
public static List <Packages> GetPackages()
{
List <Packages> packages = new List <Packages>(); // empty list of packages
//create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create select command
string query = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"ORDER BY PackageId";
// any exception not handled here is automaticlly thrown to the form
// where the method was called
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// close connection as soon as done with reading
while (reader.Read())
{
Packages pkg = new Packages(); // for reading
pkg.PackageId = (int)reader["PackageId"];
pkg.PkgName = reader["PkgName"].ToString();
int col_std = reader.GetOrdinal("PkgStartDate"); //column number of Start Date
if (reader.IsDBNull(col_std)) // if reader contains DBNull in this column
{
pkg.PkgStartDate = null; // make it null in the object
}
else // it is not null
{
pkg.PkgStartDate = (DateTime)reader["PkgStartDate"];
}
int col_end = reader.GetOrdinal("PkgEndDate"); //column number of End Date
if (reader.IsDBNull(col_end)) // if reader contains DBNull in this column
{
pkg.PkgEndDate = null; // make it null in the object
}
else // it is not null
{
pkg.PkgEndDate = (DateTime)reader["PkgEndDate"];
}
pkg.PkgDesc = reader["PkgDesc"].ToString();
pkg.PkgBasePrice = (Decimal)reader["PkgBasePrice"];
int col_comm = reader.GetOrdinal("PkgAgencyCommission"); //column number of End Date
if (reader.IsDBNull(col_comm)) // if reader contains DBNull in this column
{
pkg.PkgAgencyCommission = null; // make it null in the object
}
else // it is not null
{
pkg.PkgAgencyCommission = (Decimal)reader["PkgAgencyCommission"];
}
packages.Add(pkg);
}
} // command object recycled
} // connection object recyled
return(packages);
}
// Update Product: oldProduct - before update, newProduct - new data
public static bool UpdateProductSupplier(Products_Suppliers oldProductSupplier, Products_Suppliers newProductSupplier)
{
bool success = false;
// create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string updateStatement =
"UPDATE Products_Suppliers SET " +
"ProductId=@NewProductId " +
"SupplierId=@NewSupplierId " +
"WHERE ProductSupplierId=@OldProductSupplierId " + // need for identification
"AND (ProductId=@OldProductId OR ProductId IS NULL AND @OldProductId IS NULL) " +
"AND (SupplierId=@OldSupplierId OR SupplierId IS NULL AND @OldSupplierId IS NULL)"; // the AND is to ensure no one is updating this product
using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
{
//supply paramter value, this way can avoid sql injection problem
if (newProductSupplier.ProductId == null)
{
cmd.Parameters.AddWithValue("@NewProductId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewProductId", newProductSupplier.ProductId);
}
if (newProductSupplier.SupplierId == null)
{
cmd.Parameters.AddWithValue("@NewSupplierId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewSupplierId", newProductSupplier.SupplierId);
}
if (oldProductSupplier.ProductId == null)
{
cmd.Parameters.AddWithValue("@OldProductId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldProductId", oldProductSupplier.ProductId);
}
if (oldProductSupplier.SupplierId == null)
{
cmd.Parameters.AddWithValue("@OldSupplierId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldSupplierId", oldProductSupplier.SupplierId);
}
try
{
// open the connection
connection.Open();
// execute the delete command
int count = cmd.ExecuteNonQuery(); // returns the number of rows affected
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
}
}
//return the indicator of success
return(success);
}
/// <summary>
/// Generates custom list of Supplier Names.
/// Begins with names of suppliers that provide a given product.
/// Then proceeds with names of suppliers that do NOT provide that product.
/// </summary>
/// <param name="prodName"></param>
/// <returns>List of supplier names.</returns>
public static List <string> GetSuppliersForPPS(string prodName)
{
List <string> supplierList = new List <string>();
supplierList.Add("");
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT Suppliers.SupName " +
"FROM Products_Suppliers " +
"JOIN Suppliers ON Products_Suppliers.SupplierId = Suppliers.SupplierId " +
"JOIN Products ON Products_Suppliers.ProductId = Products.ProductId " +
"WHERE ProdName = '" + prodName + "' " +
"ORDER BY Suppliers.SupName";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dr.Read())
{
string supName = (string)dr["SupName"];
supplierList.Add(supName);
}
}
}
}
supplierList.Add("");
supplierList.Add("----------");
supplierList.Add("");
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT Suppliers.SupName " +
"FROM Suppliers " +
"WHERE SupName NOT IN (" +
"SELECT Suppliers.SupName " +
"FROM Products_Suppliers " +
"JOIN Suppliers ON Products_Suppliers.SupplierId = Suppliers.SupplierId " +
"JOIN Products ON Products_Suppliers.ProductId = Products.ProductId " +
"WHERE ProdName = '" + prodName + "') " +
"ORDER BY Suppliers.SupName";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dr.Read())
{
string supName = (string)dr["SupName"];
supplierList.Add(supName);
}
}
}
}
return(supplierList);
}
public static PackageHC GetPackageById(int packageId)
{
PackageHC selectedPkg = new PackageHC();
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string selectQuery =
"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, " +
"PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId = @PackageId";
using (SqlCommand cmd = new SqlCommand(selectQuery, connection))
{
cmd.Parameters.AddWithValue("@PackageId", packageId);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.Read()) // if package with given ID exists
{
selectedPkg = new PackageHC();
selectedPkg.PackageId = (int)reader["PackageId"];
selectedPkg.PkgName = reader["PkgName"].ToString();
// for any column that can be null need to determine if it is DBNull
// and set accordingly
int colSD = reader.GetOrdinal("PkgStartDate"); // column number of PkgStartDate
if (reader.IsDBNull(colSD)) // if reader contains DBNull in this column
{
selectedPkg.PkgStartDate = null; // make it null in the object
}
else // it is not null
{
selectedPkg.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]);
}
int colED = reader.GetOrdinal("PkgEndDate"); // column number of PkgEndDate
if (reader.IsDBNull(colED)) // if reader contains DBNull in this column
{
selectedPkg.PkgEndDate = null; // make it null in the object
}
else // it is not null
{
selectedPkg.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]);
}
selectedPkg.PkgDesc = reader["PkgDesc"].ToString();
selectedPkg.PkgBasePrice = (decimal)reader["PkgBasePrice"];
int colAC = reader.GetOrdinal("PkgAgencyCommission"); // column number of PkgAgencyCommission
if (reader.IsDBNull(colAC)) // if reader contains DBNull in this column
{
selectedPkg.PkgAgencyCommission = null; // make it null in the object
}
else // it is not null
{
selectedPkg.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
}
}
}
}
return(selectedPkg);
}
public static List <PackageHC> GetPackages()
{
List <PackageHC> packages = new List <PackageHC>(); // empty list
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string selectQuery =
"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, " +
"PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages";
using (SqlCommand cmd = new SqlCommand(selectQuery, connection))
{
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
PackageHC pkg;
while (reader.Read())
{
pkg = new PackageHC();
pkg.PackageId = (int)reader["PackageId"];
pkg.PkgName = reader["PkgName"].ToString();
// for any column that can be null need to determine if it is DBNull
// and set accordingly
int colSD = reader.GetOrdinal("PkgStartDate"); // column number of PkgStartDate
if (reader.IsDBNull(colSD)) // if reader contains DBNull in this column
{
pkg.PkgStartDate = null; // make it null in the object
}
else // it is not null
{
pkg.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]);
}
int colED = reader.GetOrdinal("PkgEndDate"); // column number of PkgEndDate
if (reader.IsDBNull(colED)) // if reader contains DBNull in this column
{
pkg.PkgEndDate = null; // make it null in the object
}
else // it is not null
{
pkg.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]);
}
pkg.PkgDesc = reader["PkgDesc"].ToString();
pkg.PkgBasePrice = (decimal)reader["PkgBasePrice"];
int colAC = reader.GetOrdinal("PkgAgencyCommission"); // column number of PkgAgencyCommission
if (reader.IsDBNull(colAC)) // if reader contains DBNull in this column
{
pkg.PkgAgencyCommission = null; // make it null in the object
}
else // it is not null
{
pkg.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
}
packages.Add(pkg);
}
}
}
}
return(packages);
}
/// <summary>
/// Public static method gets Products from database, builds Product objects from
/// their returned info, then loads each object into a list
/// </summary>
/// <returns>list of Product objects</returns>
public static List <Product> GetProducts()
{
List <Product> products = new List <Product>();
Product product; // for reading
//Sql connection block to connect to TravelExpertsDB; closes connection at end of block
//Executes query to retrieve ProductId's
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT * " +
"FROM Products " +
"ORDER BY ProductId";
//sql command block; disposes command at end of block
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
SqlDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
int productId = (int)reader["ProductId"]; // get product ID
// Run subquery on product id to get list of suppliers
// Create new product with current row information
product = new Product((int)reader["ProductId"], reader["ProdName"].ToString());
products.Add(product); // add to the list
}
} // cmd object recycled
}// connection object recycled
SqlConnection connection2 = TravelExpertsDB.GetConnection();//create new sql connection
//for loop that iterates through the created list of Product objects
for (int i = 0; i < products.Count(); i++)
{
//runs a query that retrieves Suppliers associated with each Product
String subQuery = "SELECT s.SupplierId, s.SupName " +
"FROM Suppliers s JOIN Products_Suppliers ps " +
"ON s.SupplierId = ps.SupplierId " +
"WHERE ps.ProductId = @ProductId; ";
//clear current Product's list property for Suppliers
//to be loaded with the retrieved Suppliers
products[i].Suppliers.Clear();
//query that populates each retrieved Supplier's information into the Product
//object's class property for Suppliers
//sql command block; disposes command at end of block
using (SqlCommand subCmd = new SqlCommand(subQuery, connection2))
{
connection2.Open();
//define and add sql parameter for our sqlcommand
subCmd.Parameters.AddWithValue("@ProductId", products[i].ProductId);
SqlDataReader subReader =
subCmd.ExecuteReader(CommandBehavior.CloseConnection);//new sqldatareader for accessing db
while (subReader.Read())
{
//add a new supplier to the current product
products[i].Suppliers.Add(new Supplier((int)subReader["SupplierId"], subReader["SupName"].ToString()));
}
connection2.Close();
}
}
return(products);
}