// retrieve product info with given product ID
public static Products GetProductById(int productId)
{
Products product = null;
//create the conneciton
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create select command
string query = "SELECT ProductId, ProdName " +
"FROM Products " +
"WHERE ProductId=@ProductId";
// 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("@ProductId", productId);
//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
{
product = new Products();
product.ProductId = (int)reader["ProductId"];
product.ProdName = reader["ProdName"].ToString();
}
} // command object recycled
} // connection object recyled
return(product);
}
public static List <Products> ProductDetail(int i)
{
List <Products> product = new List <Products>();
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT ProductId, ProdName " +
"FROM Products " +
"WHERE ProductId = " + i;
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Products prod = new Products();
prod.ProductId = (int)reader["ProductID"];
prod.ProdName = reader["ProdName"].ToString();
product.Add(prod);
}
return(product);
}
}
}
/// Get products
/// @Rohit
public static Products GetProducts(int ProductID) // get product name based on selected productID
{
Products product = null;
using (SqlConnection connection = TravelExpertsDB.GetConnection()) // connection made
{
//SQL query with parameter selection to extract one product data
string query = "SELECT * FROM products WHERE productid = @productID"; // sql query to retrieve data
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@productID", ProductID);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
product = new Products();
product.ProductID = (int)reader["productID"];
product.ProductName = reader["prodName"].ToString();
}
}
}
}
return(product); // return the product name
}// closes the connection
public static List <Products> GetProducts()
{
List <Products> products = new List <Products>(); // empty list of Products
//create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create select command
string query = "SELECT ProductId, ProdName " +
"FROM Products";
// 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())
{
Products prod = new Products(); // for reading
prod.ProductId = (int)reader["ProductId"];
prod.ProdName = reader["ProdName"].ToString();
products.Add(prod);
}
} // command object recycled
} // connection object recyled
return(products);
}
// Add supplier db method
public void AddSupplier(int supplierId, string supplierName)
{
SqlConnection con = TravelExpertsDB.GetConnection();
try
{
// Insert into Supplier Query
string addSupplierQuery = @"INSERT INTO Suppliers (SupplierId, SupName) VALUES(@SupplierID, @SupplierName)";
// SQL command
SqlCommand sqlSupplierCommand = new SqlCommand(addSupplierQuery, con);
con.Open();
sqlSupplierCommand.Parameters.AddWithValue("@SupplierID", supplierId);
sqlSupplierCommand.Parameters.AddWithValue("@SupplierName", supplierName);
sqlSupplierCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
// Edit supplier db method
public void EditSupplier(int supplierId, string supplierName)
{
SqlConnection con = TravelExpertsDB.GetConnection();
try
{
// Update Supplier Query
string updateQuery = @"UPDATE Suppliers SET SupplierId = @SupplierID, SupName = @SupplierName WHERE SupplierId = @SupplierID";
// SQL command
SqlCommand sqlCommand = new SqlCommand(updateQuery, con);
con.Open();
sqlCommand.Parameters.AddWithValue("@SupplierID", supplierId);
sqlCommand.Parameters.AddWithValue("@SupplierName", supplierName);
sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static List <Supplier> GetSupplier(ListView tableView)
{
List <Supplier> suppliers = new List <Supplier>();
//sql connection
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
// Supplier Query
string selectSuppliersQuery = @"SELECT SupplierId, SupName FROM Suppliers ORDER BY SupplierId";
// SQL command
using (SqlCommand cmd = new SqlCommand(selectSuppliersQuery, con))
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
tableView.View = View.Details;
tableView.FullRowSelect = true;
//Data Table Columns
tableView.Columns.Add("SupplierID").Width = 100;
tableView.Columns.Add("SupplierName").Width = 300;
while (reader.Read())
{
// variables
var item = new ListViewItem();
item.SubItems[0].Text = reader[0].ToString();
item.SubItems.Add(reader["SupName"].ToString());
tableView.Items.Add(item);
}
}
}
return(suppliers);
}
//public static List<Supplier> GetProduct(ListView tableView, int suppId)
//{
// List<Supplier>products = new List<Supplier>();
// //sql connection
// using (SqlConnection con = TravelExpertsDB.GetConnection())
// {
// string productQuery = @"SELECT Products.ProductId, Products.ProdName " +
// "FROM Products " +
// "INNER JOIN Products_suppliers ON Products_suppliers.ProductId = Products.ProductId " +
// "INNER JOIN Suppliers ON Suppliers.SupplierId = Products_suppliers.SupplierId " +
// "WHERE Suppliers.SupplierId = @SupplierId";
// // SQL command
// SqlCommand sqlCommand = new SqlCommand(productQuery, con);
// try
// {
// con.Open();
// sqlCommand.Parameters.AddWithValue("@SupplierId", suppId);
// //sqlCommand.Parameters.AddWithValue("@ProdName", supplierName);
// sqlCommand.ExecuteScalar();
// SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
// tableView.View = View.Details;
// tableView.FullRowSelect = true;
// //Data Table Columns
// tableView.Columns.Add("SupplierId").Width = 70;
// tableView.Columns.Add("ProdName").Width = 100;
// while (reader.Read())
// {
// // variables
// var item = new ListViewItem();
// item.SubItems[0].Text = reader[0].ToString();
// item.SubItems.Add(reader["ProdName"].ToString());
// tableView.Items.Add(item);
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// con.Close();
// }
// }
// return products;
//}
public static List <Supplier> GetProducts(ListBox listBoxPackage, int suppId)
{
List <Supplier> products = new List <Supplier>();
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
string productQuery = @"SELECT Products.ProductId, Products.ProdName " +
"FROM Products " +
"INNER JOIN Products_suppliers ON Products_suppliers.ProductId = Products.ProductId " +
"INNER JOIN Suppliers ON Suppliers.SupplierId = Products_suppliers.SupplierId " +
"WHERE Suppliers.SupplierId = @SupplierId";
using (SqlCommand sqlCommand = new SqlCommand(productQuery, con))
{
sqlCommand.Parameters.AddWithValue("@SupplierId", suppId);
con.Open();
if (listBoxPackage.ValueMember != null)
{
SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand);
DataTable prod = new DataTable();
adapter.Fill(prod);
listBoxPackage.DisplayMember = "ProdName";
listBoxPackage.ValueMember = "ProductId";
listBoxPackage.DataSource = prod;
}
}
}
return(products);
}
/// <summary>
/// Public static class method for retrieving all ProductId's from database
/// </summary>
/// <returns>int list of ProductId's</returns>
public static List <int> GetProductIds()
{
//int list variable to store returned productIds
List <int> productIds = new List <int>();
int productId; //stores each retrieved id
//Sql connection block to connect to TravelExpertsDB; closes connection at end of block
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT ProductId " +
"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);//new sqldatareader for accessing db
while (reader.Read())
{
productId = (int)reader["ProductId"];
productIds.Add(productId); // add to the list
}
} // cmd object recycled
}// connection object recycled
return(productIds);
}
public static List <int> GetProductSupplierIds()
{
List <int> productSupplierIds = new List <int>(); // empty list of ProductSupplier Ids
int id; // for reading
//create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create select command
string query = "SELECT ProductSupplierId FROM Products_Suppliers " +
"ORDER BY ProductSupplierId";
// 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())
{
id = (int)reader["ProductSupplierId"];
productSupplierIds.Add(id);
}
} // command object recycled
} // connection object recyled
return(productSupplierIds);
}
/// <summary>
/// Adds product supplier pair to DB
/// </summary>
/// <param name="productId">Product ID</param>
/// <param name="supplierId">Supplier ID</param>
/// <returns>Successful?</returns>
public static bool addProductSupplier(int productId, int supplierId)
{
bool success = false;//bool success value returned to tell if query successful
//Sql connection block to connect to TravelExpertsDB; closes connection at end of block
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query =
"INSERT INTO Products_Suppliers " +
"(ProductId,SupplierId) " +
"VALUES (@ProductId, @SupplierId) ; ";
// Adds all parameters to new SQL Command
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
// Adds all parameters to new SQL Command
cmd.Parameters.AddWithValue("@ProductId", productId);
cmd.Parameters.AddWithValue("@SupplierId", supplierId);
success = cmd.ExecuteNonQuery() > 0; // Success if rows have been deleted
} // cmd object recycled
}// connection object recycled
return(success);
}
/// <summary>
/// Public static class method for editing products
/// </summary>
/// <param name="oldProduct">Product class object for getting old values</param>
/// <param name="newProduct">Product class object for getting new values</param>
/// <returns></returns>
public static bool UpdateProduct(Product oldProduct, Product newProduct)
{
bool success;
// Opens connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query =
"UPDATE Products " +
"SET " +
"ProdName = @NewProductName " +
"WHERE " +
"ProductId = @OldProductId AND " +
"ProdName = @OldProdName ; ";
// Creates command and adds all proper parameters
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
//Add parameters for sql query
cmd.Parameters.AddWithValue("@NewProductName", newProduct.ProductName);
cmd.Parameters.AddWithValue("@OldProductId", oldProduct.ProductId);
cmd.Parameters.AddWithValue("@OldProdName", oldProduct.ProductName);
success = cmd.ExecuteNonQuery() > 0; // Success if rows changed
} // cmd object recycled
}// connection object recycled
return(success);
}
// get all ProductSupplier Ids
public static List <Products_Suppliers> ProductSuppliers(int i)
{
List <Products_Suppliers> productSupplierList = new List <Products_Suppliers>();
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT ps.ProductSupplierId, p.ProductId, p.ProdName, s.SupplierId, s.SupName " +
"FROM Products_Suppliers AS ps " +
"INNER JOIN Products AS p on ps.ProductId = p.ProductId " +
"INNER JOIN Suppliers AS s on ps.SupplierId = s.SupplierId " +
"where p.ProductId = " + i +
" ORDER BY SupName";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Products_Suppliers productSupplierItem = new Products_Suppliers();
productSupplierItem.ProductSupplierId = (int)reader[0];
productSupplierItem.ProductId = (int)reader[1];
productSupplierItem.ProdName = reader[2].ToString();
productSupplierItem.SupplierId = (int)reader[3];
productSupplierItem.SupName = reader[4].ToString();
productSupplierList.Add(productSupplierItem);
}
return(productSupplierList);
}
}
}
public static List <Product> GetEngagedProducts()
{
List <Product> engagedProducts = new List <Product>(); // empty list
Product engProd; // object instance for reading
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string selectQuery =
"SELECT DISTINCT prod.ProductId, prod.ProdName " +
"FROM Products AS prod " +
"INNER JOIN Products_Suppliers AS prs ON prod.ProductId=prs.ProductId " +
"INNER JOIN Packages_Products_Suppliers AS pps " +
"ON prs.ProductSupplierId=pps.ProductSupplierId " +
"WHERE pps.PackageId IS NOT NULL " +
"ORDER BY prod.ProdName";
using (SqlCommand cmd = new SqlCommand(selectQuery, connection))
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read()) // if products for given ID exists
{
engProd = new Product();
engProd.ProductId = (int)reader["ProductId"];
engProd.ProdName = reader["ProdName"].ToString();
engagedProducts.Add(engProd);
}
}
}
return(engagedProducts);
}
/// <summary>
/// Gets a list of suppliers by product id
/// </summary>
/// <returns>Supplier list</returns>
public static List <Supplier> getSuppliersByProductId(int id)
{
List <Supplier> suppliers = new List <Supplier>();
SqlConnection connection = TravelExpertsDB.GetConnection();
// Gets suppliers related to product id
String query = "SELECT ps.SupplierId, SupName " +
"FROM " +
"Products_Suppliers ps " +
"JOIN Suppliers s " +
"ON ps.SupplierId = s.SupplierId " +
"WHERE ps.ProductId = @id " +
"ORDER BY ps.SupplierId ";
using (SqlCommand command = new SqlCommand(query, connection))
{
connection.Open();
command.Parameters.AddWithValue("@id", id);
SqlDataReader reader =
command.ExecuteReader(CommandBehavior.CloseConnection);
// Add all found suppliers to list
while (reader.Read())
{
suppliers.Add(new Supplier((int)reader["SupplierId"], reader["SupName"].ToString()));
}
connection.Close();
}
return(suppliers);
}
/// <summary>
/// Generates a list of supplier names.
/// </summary>
/// <returns>List of supplier names.</returns>
public static List <string> GetAllSuppliers()
{
List <string> suppliersList = new List <string>();
suppliersList.Add("");
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT SupName " +
"FROM Suppliers " +
"ORDER BY SupName";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dr.Read())
{
string supplierName = (string)dr["SupName"];
suppliersList.Add(supplierName);
}
}
}
}
return(suppliersList);
}
/// <summary>
/// Gets sorted list of all product suppliers in DB. Key is prodsupplier id. Value is formatted string describing the pair.
/// </summary>
/// <returns>sorted list</returns>
public static SortedList <int, string> getProductsSuppliersIdAndString()
{
SortedList <int, string> productsSuppliers = new SortedList <int, string>();
SqlConnection connection = TravelExpertsDB.GetConnection();
String query = "SELECT ProductSupplierId, ProdName, SupName " +
"FROM " +
"Products_Suppliers ps " +
"JOIN Products pr " +
"ON ps.ProductId = pr.ProductId " +
"JOIN Suppliers s " +
"ON ps.SupplierId = s.SupplierId ; ";
using (SqlCommand command = new SqlCommand(query, connection))
{
connection.Open();
SqlDataReader reader =
command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
productsSuppliers.Add((int)reader["ProductSupplierId"], reader["ProdName"].ToString() + " from " + reader["SupName"].ToString());
}
connection.Close();
}
return(productsSuppliers);
}
/// <summary>
/// Gets a list of products by supplier id
/// </summary>
/// <returns>Product list</returns>
public static List <Product> getProductsBySupplierId(int id)
{
List <Product> products = new List <Product>();
SqlConnection connection = TravelExpertsDB.GetConnection();
// Gets products related to supplier id
String query = "SELECT ps.ProductId, ProdName " +
"FROM " +
"Products_Suppliers ps " +
"JOIN Products pr " +
"ON ps.ProductId = pr.ProductId " +
"WHERE ps.SupplierId = @id " +
"ORDER BY ps.ProductID ; ";
using (SqlCommand command = new SqlCommand(query, connection))
{
connection.Open();
command.Parameters.AddWithValue("@id", id);
SqlDataReader reader =
command.ExecuteReader(CommandBehavior.CloseConnection);
// Add all found products to list
while (reader.Read())
{
products.Add(new Product((int)reader["ProductId"], reader["ProdName"].ToString()));
}
connection.Close();
}
return(products);
}
/// <summary>
/// Removes product supplier pair to DB
/// </summary>
/// <param name="productId">Product ID</param>
/// <param name="supplierId">Supplier ID</param>
/// <returns>Successful?</returns>
public static bool removeProductSupplier(int productId, int supplierId)
{
bool success = false;//bool success value returned to tell if query successful
//Sql connection block to connect to TravelExpertsDB; closes connection at end of block
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query =
"DELETE FROM Products_Suppliers " +
"WHERE " +
"ProductId = @ProductId AND " +
"SupplierId = @SupplierId ; ";
//sql command block; disposes command at end of block
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
//Adds all parameters to new SQL Command
cmd.Parameters.AddWithValue("@ProductId", productId);
cmd.Parameters.AddWithValue("@SupplierId", supplierId);
success = cmd.ExecuteNonQuery() > 0; // Success if rows have been deleted
} // cmd object recycled
}// connection object recycled
return(success);
}
/// <summary>
/// Public static class method for deleting products
/// </summary>
/// <param name="oldProduct">Product class object for products being deleted</param>
/// <returns></returns>
public static bool DeleteProduct(Product oldProduct)
{
bool success = false;
// Opens connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query =
"DELETE FROM Products " +
"WHERE " +
"ProductId = @OldProductId AND " +
"ProdName = @OldProductName ; ";
// Adds all parameters to new SQL Command
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
//Add parameters for sql query
cmd.Parameters.AddWithValue("@OldProductId", oldProduct.ProductId);
cmd.Parameters.AddWithValue("@OldProductName", oldProduct.ProductName);
success = cmd.ExecuteNonQuery() > 0; // Success if rows have been deleted
} // cmd object recycled
}// connection object recycled
return(success);
}
/// <summary>
/// Delete's a record in Packages_Products_Suppliers
/// </summary>
/// <param name="packageId">Package ID of target record</param>
/// <param name="prodName">Product name of target record</param>
/// <param name="supName">Supplier name of target record</param>
/// <returns>True if delete successful, false if not.</returns>
public static bool DeletePPSWithPackageIdThenConfirm(int packageId, string prodName, string supName)
{
bool successfullyDeleted;
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string deleteStatement = "DELETE FROM Packages_Products_Suppliers " +
"WHERE PackageId = " + packageId.ToString() +
"AND ProductId = (SELECT ProductId FROM Products WHERE ProdName = '" + prodName + "') " +
"AND SupplierId = (SELECT SupplierId FROM Suppliers WHERE SupName = '" + supName + "')";
using (SqlCommand cmd = new SqlCommand(deleteStatement, connection))
{
connection.Open();
if (cmd.ExecuteNonQuery() > 0)
{
successfullyDeleted = true;
}
else
{
successfullyDeleted = false;
}
}
}
return(successfullyDeleted);
}
/// <summary>
/// Generates a filtered list of product names.
/// </summary>
/// <returns>Filtered list of product names.</returns>
public static List <string> GetFilteredProducts()
{
List <string> productsList = new List <string>();
productsList.Add("");
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT ProdName " +
"FROM Products " +
"ORDER BY ProdName";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dr.Read())
{
string prodName = (string)dr["ProdName"];
productsList.Add(prodName);
}
}
}
}
return(productsList);
}
public static List <Product> GetProducts()
{
List <Product> products = new List <Product>(); // empty list
Product prod; // object instance for reading
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string selectQuery =
"SELECT ProductId, ProdName " +
"FROM Products";
using (SqlCommand cmd = new SqlCommand(selectQuery, connection))
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read()) // if products for given ID exists
{
prod = new Product();
prod.ProductId = (int)reader["ProductId"];
prod.ProdName = reader["ProdName"].ToString();
products.Add(prod);
}
}
}
return(products);
}
// retrieve productSupplier info with given productSupplier Id
public static Products_Suppliers GetProductSupplierById(int productSupplierId)
{
Products_Suppliers productSupplier = null;
//create the conneciton
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create select command
string query = "SELECT ProductSupplierId, ps.ProductId, ps.SupplierId, ProdName, SupName " +
"FROM Products_Suppliers ps " +
"JOIN Products p ON ps.ProductId=p.ProductId " +
"JOIN Suppliers s ON ps.SupplierId=s.SupplierId " +
"WHERE ProductSupplierId=@ProductSupplierId";
// 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("@ProductSupplierId", productSupplierId);
//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
{
productSupplier = new Products_Suppliers();
productSupplier.ProductSupplierId = (int)reader["ProductSupplierId"];
int col_pro = reader.GetOrdinal("ProductId"); //column number of ProductId
if (reader.IsDBNull(col_pro)) // if reader contains DBNull in this column
{
productSupplier.ProductId = null; // make it null in the object
}
else // it is not null
{
productSupplier.ProductId = (int)reader["ProductId"];
}
int col_sup = reader.GetOrdinal("SupplierId"); //column number of SupplierId
if (reader.IsDBNull(col_sup)) // if reader contains DBNull in this column
{
productSupplier.SupplierId = null; // make it null in the object
}
else // it is not null
{
productSupplier.SupplierId = (int)reader["SupplierId"];
}
productSupplier.ProdName = reader["ProdName"].ToString();
productSupplier.SupName = reader["SupName"].ToString();
}
} // command object recycled
} // connection object recyled
return(productSupplier);
}
// 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["PkgStartDate"]);
}
package.PkgDesc = reader["PkgDesc"].ToString();
package.PkgBasePrice = (decimal)reader["PkgBasePrice"];
package.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
}
} // command object recycled
} // connection object recyled
return(package);
}
public static List <Packages> PackageDetail(int i)
{
List <Packages> package = new List <Packages>();
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId = " + i;
using (SqlCommand cmd = new SqlCommand(query, connection))
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
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"];
}
package.Add(pkg);
}
return(package);
}
}
}
///Written by Raymond Edeamrere
public static bool AddSupplier(Supplier newSupplier)
{
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
connection.Open();
SqlCommand cmd = connection.CreateCommand();
SqlTransaction addNewPkgTran;
int rowsAffected;
string insert = "INSERT INTO Suppliers (SupplierId, SupName )" +
"VALUES (@SupplierId, @SupName)";
// start transaction
addNewPkgTran = connection.BeginTransaction();
// set up cmd properties
cmd.Connection = connection;
cmd.Transaction = addNewPkgTran;
cmd.CommandText = insert;
// add non-nullable parameters
cmd.Parameters.AddWithValue("@SupplierId", newSupplier.SupplierId);
// nullable parameters
if (string.IsNullOrEmpty(newSupplier.SupName))
{
cmd.Parameters.AddWithValue("@SupName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@SupName", newSupplier.SupName);
}
try
{
// run query
rowsAffected = cmd.ExecuteNonQuery();
// insert failed
if (rowsAffected != 1)
{
addNewPkgTran.Rollback();
return(false);
}
// commit transaction
addNewPkgTran.Commit();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
}
// insert a new product supplier into Products_Suppliers table
// return new product_supplier Id
public static int AddProductSupplier(Products_Suppliers productSupplier)
{
int productSupplierId = 0;
// create the connection
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatement = "INSERT INTO Products_Suppliers(ProductId, SupplierId) " +
"OUTPUT inserted.ProductSupplierId " +
"VALUES(@ProductId, @SupplierId)";
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
//supply paramter value, this way can avoid sql injection problem
if (productSupplier.ProductId == null)
{
cmd.Parameters.AddWithValue("@ProductId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@ProductId", productSupplier.ProductId);
}
if (productSupplier.SupplierId == null)
{
cmd.Parameters.AddWithValue("@SupplierId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@SupplierId", productSupplier.SupplierId);
}
// execute INSERT command
try
{
// open the connection
connection.Open();
// execute insert command and get inserted ID
productSupplierId = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
}
}
return(productSupplierId);
}
/// <summary>
/// Add a new package to DB
/// </summary>
/// <param name="newPkg">package to add</param>
/// <returns>true if successful, false otherwise</returns>
public static bool AddPackage(Package newPkg)
{
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
connection.Open();
SqlCommand cmd = connection.CreateCommand();
SqlTransaction addNewPkgTran;
int rowsAffected;
string insert = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, " +
"PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"VALUES (@PkgName, @NewPkgStartDate, @NewPkgEndDate, " +
"@NewPkgDesc, @NewPkgBasePrice, @NewPkgAgencyCommission)";
// start transaction
addNewPkgTran = connection.BeginTransaction();
// set up cmd properties
cmd.Connection = connection;
cmd.Transaction = addNewPkgTran;
cmd.CommandText = insert;
// add non-nullable parameters
cmd.Parameters.AddWithValue("@PkgName", newPkg.PkgName);
cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPkg.PkgBasePrice);
// for nullable properties, have to check for null
ProcessNewPkgNullables(cmd, newPkg, "New");
try
{
// run query
rowsAffected = cmd.ExecuteNonQuery();
// insert failed
if (rowsAffected != 1)
{
addNewPkgTran.Rollback();
return(false);
}
// commit transaction
addNewPkgTran.Commit();
return(true);
}
catch (Exception ex)
{
throw ex;
}
} // close and recycle connection
}
// update order: current - before update, updated - new data
public static bool UpdatePackage(Package current, Package updated)
{
bool success = false; // no success yet
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 " +
" AND PkgName = @OldPkgName " +
" AND PkgStartDate = @OldPkgStartDate " +
" AND PkgEndDate = @OldPkgEndDate " +
" AND PkgDesc = @OldPkgDesc " +
" AND PkgBasePrice = @OldPkgBasePrice " +
" AND PkgAgencyCommission = @OldPkgAgencyCommission";
using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
{
// provide values for parameters
cmd.Parameters.AddWithValue("@NewPkgName", updated.PkgName);
cmd.Parameters.AddWithValue("@NewPkgStartDate", updated.PkgStartDate);
cmd.Parameters.AddWithValue("@NewPkgEndDate", updated.PkgEndDate);
cmd.Parameters.AddWithValue("@NewPkgDesc", updated.PkgDesc);
cmd.Parameters.AddWithValue("@NewPkgBasePrice", updated.PkgBasePrice);
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", updated.PkgAgencyCommission);
cmd.Parameters.AddWithValue("@OldPackageId", current.PackageId);
cmd.Parameters.AddWithValue("@OldPkgName", current.PkgName);
cmd.Parameters.AddWithValue("@OldPkgStartDate", current.PkgStartDate);
cmd.Parameters.AddWithValue("@OldPkgEndDate", current.PkgEndDate);
cmd.Parameters.AddWithValue("@OldPkgDesc", current.PkgDesc);
cmd.Parameters.AddWithValue("@OldPkgBasePrice", current.PkgBasePrice);
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", current.PkgAgencyCommission);
// open connection
connection.Open();
// execute UPDATE command
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true;
}
} // command object recycled
} // connection closed and recycled
return(success);
}
