//deletes a supplier
public static bool DeleteSupplier(Supplier supp)
{
bool success = true;//sets up return value
SqlConnection connect = TravelExpertsDBConn.getDbConnection();
string deleteQuery = "DELETE FROM Suppliers WHERE SupplierID = @SupplierID AND SupName = @SupName";
SqlCommand cmd = new SqlCommand(deleteQuery, connect);
//inputs the parameters to check them
cmd.Parameters.AddWithValue("@SupplierID", supp.SupID);
cmd.Parameters.AddWithValue("@SupName", supp.SupName);
try
{
connect.Open();
int count = cmd.ExecuteNonQuery();
if (count == 0)
{
success = false;
}
}
catch (Exception e) { throw e; }
finally { connect.Close(); }
return(success);
}
//adds a supplier
public static bool AddSupplier(Supplier supp)
{
bool success = true;
SqlConnection connect = TravelExpertsDBConn.getDbConnection();
string insertQuery = "INSERT INTO Suppliers (SupplierID, SupName) VALUES(@SupplierID, @SupName)";
SqlCommand cmd = new SqlCommand(insertQuery, connect);
//applies the values to the query
cmd.Parameters.AddWithValue("@SupplierID", supp.SupID);
cmd.Parameters.AddWithValue("@SupName", supp.SupName);
try
{
connect.Open();
int count = cmd.ExecuteNonQuery();
if (count == 0)
{
success = false;
}
}
catch (Exception e) { throw e; }
finally { connect.Close(); }
return(success);
}
//adds an agent
public static int AddAgent(Agent agt)
{
int agentID = 0;
//Opens connection to the DB and preps insert
SqlConnection connect = TravelExpertsDBConn.getDbConnection();
string insertQuery = "INSERT INTO Agents (AgtFirstName, AgtLastName, AgtMiddleInitial, AgtBusPhone," +
"AgtEmail, AgtPosition, AgencyId) VALUES(@AgtFirstName, @AgtLastName," +
"@AgtMiddleInitial, @AgtBusPhone, @AgtEmail, @AgtPosition, @AgencyId)";
SqlCommand cmd = new SqlCommand(insertQuery, connect);
//applies the value to the query
cmd.Parameters.AddWithValue("@AgtFirstName", agt.AgtFirstName);
cmd.Parameters.AddWithValue("@AgtLastName", agt.AgtLastName);
cmd.Parameters.AddWithValue("@AgtMiddleInitial", agt.AgtMiddleInitial);
cmd.Parameters.AddWithValue("@AgtBusPhone", agt.AgtBusPhone);
cmd.Parameters.AddWithValue("@AgtEmail", agt.AgtEmail);
cmd.Parameters.AddWithValue("@AgtPosition", agt.AgtPosition);
cmd.Parameters.AddWithValue("@AgencyId", agt.AgencyID);
try
{
connect.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('Agents') FROM Agents"; //identity value
SqlCommand selectCommand = new SqlCommand(selectQuery, connect);
agentID = Convert.ToInt32(selectCommand.ExecuteScalar()); //single value
agt.AgentID = agentID;
}
catch (Exception e) { throw e; }
finally { connect.Close(); }
return(agentID);
}
/// <summary>
/// this method contains the logic to add the package by the user form the package form
/// </summary>
/// <param name="it passes the entire package object as parameter so that all information inside the object can be added"></param>
public static void PackageAdd(Package pkgObj)
{
string insertStatement = "INSERT INTO Packages (PkgName,PkgStartDate,PkgEndDate,PkgDesc,PkgBasePrice,PkgAgencyCommission) " +
"VALUES (@PkgName,@PkgStartDate,@PkgEndDate,@PkgDesc,@PkgBasePrice,@PkgAgencyCommission) ";
try
{
using (SqlConnection con = TravelExpertsDBConn.getDbConnection()) //doesn't require to close connection as USING method doest it by itself
{
using (SqlCommand cmd = new SqlCommand(insertStatement, con))
{
con.Open(); //databse connection opens
// cmd.Parameters.AddWithValue("@PackageId", pkgObj.PackageId); //auto-generated
cmd.Parameters.AddWithValue("@PkgName", pkgObj.PkgName);
cmd.Parameters.AddWithValue("@PkgBasePrice", pkgObj.PkgBasePrice);
if (pkgObj.PkgStartDate.HasValue)
{
cmd.Parameters.AddWithValue("@PkgStartDate", pkgObj.PkgStartDate);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
if (pkgObj.PkgEndDate.HasValue)
{
cmd.Parameters.AddWithValue("@PkgEndDate", pkgObj.PkgEndDate);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
if (pkgObj.PkgDesc != null)
{
cmd.Parameters.AddWithValue("@PkgDesc", pkgObj.PkgDesc);
}
else
{
cmd.Parameters.AddWithValue("@PkgDesc", "");
}
if (pkgObj.PkgAgencyCommission.HasValue)
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", pkgObj.PkgAgencyCommission);
}
else
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", 0);
}
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
//deletes an agent
public static bool DeleteAgent(Agent agt)
{
bool success = true;
SqlConnection connect = TravelExpertsDBConn.getDbConnection();
string deleteQuery = "DELETE FROM agents WHERE AgentId = @AgentID AND AgtFirstName = @AgtFirstName " +
"AND AgtMiddleInitial = @AgtMiddleInitial AND AgtLastName = @AgtLastName AND " +
"AgtBusPhone = @AgtBusPhone AND AgtEmail = @AgtEmail AND AgtPosition = @AgtPosition AND " +
"AgencyId = @AgencyID";
SqlCommand cmd = new SqlCommand(deleteQuery, connect);
//inputs the parameters to check them
cmd.Parameters.AddWithValue("@AgentID", agt.AgentID);
cmd.Parameters.AddWithValue("@AgtFirstName", agt.AgtFirstName);
cmd.Parameters.AddWithValue("@AgtMiddleInitial", agt.AgtMiddleInitial);
cmd.Parameters.AddWithValue("@AgtLastName", agt.AgtLastName);
cmd.Parameters.AddWithValue("@AgtBusPhone", agt.AgtBusPhone);
cmd.Parameters.AddWithValue("@AgtEmail", agt.AgtEmail);
cmd.Parameters.AddWithValue("@AgtPosition", agt.AgtPosition);
cmd.Parameters.AddWithValue("@AgencyID", agt.AgencyID);
try
{
connect.Open();
int count = cmd.ExecuteNonQuery();
if (count == 0)
{
success = false;
}
}
catch (Exception e) { throw e; }
finally { connect.Close(); }
return(success);
}
//Delete Product
public static bool DeleteProduct(Product p)
{
bool success = true;
SqlConnection con = TravelExpertsDBConn.getDbConnection();
string strSqlUpdate = "DELETE FROM Products " +
"WHERE ProductId = @ProductId " + //customer id identifies record to update
"AND ProdName = @ProdName";
SqlCommand cmd = new SqlCommand(strSqlUpdate, con);
//set parameters for old customer dat
cmd.Parameters.AddWithValue("@ProductId", p.ProductId);
cmd.Parameters.AddWithValue("@ProdName", p.ProdName);
try
{
con.Open();
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
success = false; //did not update, most likey b/c of concurreny exception event
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(success);
}
/// <summary>
/// below method has the logic to check if the selected package information to be deleted has any child dependency to be checked
/// </summary>
/// <returns>it returns the list of name of the package which is used to compare and show appropriate message to the user on the package form</returns>
public static List <string> CheckBeforeDelete()
{
List <string> packageNames = new List <string>();
string query = "SELECT PackageId,PkgName from packages Where EXISTS " +
"(SELECT b.packageid, p.packageid from bookings b,Packages_Products_Suppliers p where b.packageid=p.packageid)";
try
{
using (SqlConnection con = TravelExpertsDBConn.getDbConnection())
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
con.Open(); //databse connection opens
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //Data reader executes the query and bring all data before closing connection to the table
while (dr.Read()) //below block of code executes till there is data in the table
{
packageNames.Add(Convert.ToString(dr["PkgName"]));
}
}
return(packageNames); //returns the list of package
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// this method displays the related booking in the booking data grid on the Package form
/// </summary>
/// <param name="PackageId is passed as parameter to use it in SQL query to fetch correct records"></param>
/// <returns>list of related bookings for the selected package</returns>
public static List <Bookings> DisplayBookingsInGrid(int pkgId)
{
List <Bookings> bookingList = new List <Bookings>();
string selectQuery = "select BookingNo,CustomerId,TripTypeId from bookings where PackageId IN (Select PackageId from packages WHERE PackageId=@pkgId) "; //SQL query to get all fields from table
try
{
using (SqlConnection con = TravelExpertsDBConn.getDbConnection())
{
using (SqlCommand cmd = new SqlCommand(selectQuery, con))
{
con.Open(); //databse connection opens
cmd.Parameters.AddWithValue("@pkgId", pkgId); //binding it with PkgId parameter which is passed on in an arguement
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //Data reader executes the query and bring all data before closing connection to the table
while (dr.Read()) //below block of code executes till there is data in the table
{
Bookings bkngObj = new Bookings(); //instantiating the object of the class booking
bkngObj.BookingNo = (string)dr["BookingNo"];
bkngObj.CustomerId = (int)dr["CustomerId"];
bkngObj.TripTypeId = (string)dr["TripTypeId"];
bookingList.Add(bkngObj); //adding booking items into the list
}
}
return(bookingList); //returns the list of booking
}
}
catch (Exception ex)
{
throw ex;
}
}
//grabs the suppliers
public static List <Supplier> GetSuppliers()
{
//List and holder created
List <Supplier> suppliers = new List <Supplier>();
Supplier supplierHolder;
SqlConnection connect = TravelExpertsDBConn.getDbConnection();//defines db connection
string selectQuery = "SELECT SupplierID, SupName FROM Suppliers ORDER BY SupplierID";
SqlCommand cmd = new SqlCommand(selectQuery, connect);
try
{
connect.Open();
SqlDataReader reader = cmd.ExecuteReader();
//runs until all data has been read
while (reader.Read())
{
supplierHolder = new Supplier();
supplierHolder.SupID = (int)reader["SupplierID"];
supplierHolder.SupName = reader["SupName"].ToString();
suppliers.Add(supplierHolder);//adds to list
}
}
catch (Exception e) { throw e; }
finally { connect.Close(); }
return(suppliers);//returns the list
}
//gets the agency table from the database
public static List <Agency> GetAgencies()
{
List <Agency> agencies = new List <Agency>();
Agency agency;
SqlConnection connect = TravelExpertsDBConn.getDbConnection();
string selectQuery = "SELECT AgencyId, AgncyAddress, AgncyCity, AgncyProv, AgncyPostal, " +
"AgncyCountry, AgncyPhone, AgncyFax FROM Agencies";
SqlCommand cmd = new SqlCommand(selectQuery, connect);
try
{
connect.Open();
SqlDataReader reader = cmd.ExecuteReader();
//runs until all data has been read
while (reader.Read())
{
agency = new Agency();
agency.AgencyID = (int)reader["AgencyId"];
agency.AgencyAddress = reader["AgncyAddress"].ToString();
agency.AgencyCity = reader["AgncyCity"].ToString();
agency.AgencyProv = reader["AgncyProv"].ToString();
agency.AgencyPostal = reader["AgncyPostal"].ToString();
agency.AgencyCountry = reader["AgncyCountry"].ToString();
agency.AgencyFax = reader["AgncyFax"].ToString();
agencies.Add(agency);//adds to list
}
}catch (Exception e) { throw e; }
finally { connect.Close(); }
return(agencies);
}
//updates an agent
public static bool UpdateAgent(Agent oldAgent, Agent newAgent)
{
bool success = true;
SqlConnection connect = TravelExpertsDBConn.getDbConnection();
//only needs ID, rest checks for concurrency issues
string updateQuery = "UPDATE Agents SET AgtFirstName = @NewAgtFirstName, " +
"AgtMiddleInitial = @NewAgtMiddleInitial, " +
"AgtLastName = @NewAgtLastName, " +
"AgtBusPhone = @NewAgtBusPhone, " +
"AgtEmail = @NewAgtEmail, " +
"AgtPosition = @newAgtPosition, " +
"AgencyId = @NewAgencyId " +
"WHERE AgentId = @OldAgentId " +
"AND AgtFirstName = @OldAgtFirstName " +
"AND AgtMiddleInitial = @OldAgtMiddleInitial " +
"AND AgtLastName = @OldAgtLastName " +
"AND AgtBusPhone = @OldAgtBusPhone " +
"AND AgtEmail = @OldAgtEmail " +
"AND AgtPosition = @OldAgtPosition " +
"AND AgencyId = @OldAgencyId";
SqlCommand cmd = new SqlCommand(updateQuery, connect);
//sets the parameters
cmd.Parameters.AddWithValue("@NewAgtFirstName", newAgent.AgtFirstName);
cmd.Parameters.AddWithValue("@NewAgtMiddleInitial", newAgent.AgtMiddleInitial);
cmd.Parameters.AddWithValue("@NewAgtLastName", newAgent.AgtLastName);
cmd.Parameters.AddWithValue("@NewAgtBusPhone", newAgent.AgtBusPhone);
cmd.Parameters.AddWithValue("@NewAgtEmail", newAgent.AgtEmail);
cmd.Parameters.AddWithValue("@NewAgtPosition", newAgent.AgtPosition);
cmd.Parameters.AddWithValue("@NewAgencyId", newAgent.AgencyID);
cmd.Parameters.AddWithValue("@OldAgentId", oldAgent.AgentID);
cmd.Parameters.AddWithValue("@OldAgtFirstName", oldAgent.AgtFirstName);
cmd.Parameters.AddWithValue("@OldAgtMiddleInitial", oldAgent.AgtMiddleInitial);
cmd.Parameters.AddWithValue("@OldAgtLastName", oldAgent.AgtLastName);
cmd.Parameters.AddWithValue("@OldAgtBusPhone", oldAgent.AgtBusPhone);
cmd.Parameters.AddWithValue("@OldAgtEmail", oldAgent.AgtEmail);
cmd.Parameters.AddWithValue("@OldAgtPosition", oldAgent.AgtPosition);
cmd.Parameters.AddWithValue("@OldAgencyId", oldAgent.AgencyID);
try
{
connect.Open();
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
success = false; //did not update, concurrency issue
}
}
catch (Exception e) { throw e; }
finally { connect.Close(); }
return(success);
}
//get list of ProductSuppliers from database for a given supplier id
public static List <ProductSupplier> GetProductSuppliersBySupplierID(int sId)
{
List <ProductSupplier> ps = new List <ProductSupplier>(); //empty list
//create connection
SqlConnection con = TravelExpertsDBConn.getDbConnection();
//create sql statement
string strSqlSelect = "SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"ORDER BY ProductSupplierId" +
"WHERE SupplierId=@SupplierId";
//create sql command
SqlCommand cmd = new SqlCommand(strSqlSelect, con);
cmd.Parameters.AddWithValue("@SupplierId", sId);
//try-catch sql command execution
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ProductSupplier p = new ProductSupplier();
p.ProductSupplierId = Convert.ToInt32(dr["ProductSupplierId"]);
p.ProductId = Convert.ToInt32(dr["ProductId"]);
p.SupplierId = Convert.ToInt32(dr["SupplierId"]);
ps.Add(p);
}
}
catch (Exception ex) //handle sql exceptions
{
throw ex;
}
finally
{
con.Close();
}
//return list
return(ps);
}
//get Products
public static List <Product> GetProducts()
{
List <Product> products = new List <Product>(); //empty list
//create connection
SqlConnection con = TravelExpertsDBConn.getDbConnection();
//create sql statement
string strSqlSelect = "SELECT ProductId, ProdName FROM Products ORDER BY ProdName";
//create sql command
SqlCommand cmd = new SqlCommand(strSqlSelect, con);
//try-catch sql command execution
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Product p = new Product();
p.ProductId = Convert.ToInt32(dr["ProductId"]);
p.ProdName = dr["ProdName"].ToString();
products.Add(p);
}
}
catch (Exception ex) //handle sql exceptions
{
throw ex;
}
finally
{
con.Close();
}
//return list
return(products);
}
//Update ProductSupplier
public static bool UpdateProductSupplier(ProductSupplier oldProductSupplier, ProductSupplier newProductSupplier)
{
bool success = true;
SqlConnection con = TravelExpertsDBConn.getDbConnection();
string strSqlUpdate = "UPDATE Products_Suppliers " +
"SET ProductId = @ProductIdNew, SupplierId = @SupplierIdNew, " +
"WHERE ProductSupplierId = @ProductSupplierIdOld " + //customer Id Identifies record to update
"AND ProductId = @ProductIdOld " +
"AND SupplierId = @SupplierIdOld";
SqlCommand cmd = new SqlCommand(strSqlUpdate, con);
//set parameters for new customer data
cmd.Parameters.AddWithValue("@ProductIdNew", newProductSupplier.ProductId);
cmd.Parameters.AddWithValue("@SupplierIdNew", newProductSupplier.SupplierId);
//set parameters for old customer dat
cmd.Parameters.AddWithValue("@ProductSupplierIdOld", oldProductSupplier.ProductSupplierId);
cmd.Parameters.AddWithValue("@ProductIdOld", oldProductSupplier.ProductId);
cmd.Parameters.AddWithValue("@SupplierIdOld", oldProductSupplier.SupplierId);
try
{
con.Open();
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
success = false; //dId not update, most likey b/c of concurreny exception event
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(success);
}
//get ProductSupplier by Id
public static ProductSupplier getProductSupplierById(int ProductSupplierId)
{
ProductSupplier ps = null; //return null if no ProductSupplier exists for Id
SqlConnection con = TravelExpertsDBConn.getDbConnection();
//create sql statement
string strSqlSelect = "SELECT ProductSupplierId, ProductId, SupplierId FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId";
//create sql command
SqlCommand cmd = new SqlCommand(strSqlSelect, con);
cmd.Parameters.AddWithValue("@ProductSupplierId", ProductSupplierId);
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
ps = new ProductSupplier();
ps.ProductSupplierId = Convert.ToInt32(dr["ProductSupplierId"]);
ps.ProductId = Convert.ToInt32(dr["ProductId"]);
ps.SupplierId = Convert.ToInt32(dr["SupplierId"]);
}
}
catch (Exception ex) //handle sql exceptions
{
throw ex;
}
finally
{
con.Close();
}
//return list
return(ps);
}
//Add ProductSupplier
public static int AddProductSupplier(ProductSupplier ps)
{
int ProductSupplierId = 0;
//create connection
SqlConnection con = TravelExpertsDBConn.getDbConnection();
//create SQL statement
string strSqlInsert = "INSERT INTO Products_Suppliers(ProductId, SupplierId)" +
"VALUES(@ProductId, @SupplierId)";
//create sql command and populate parameters
SqlCommand cmd = new SqlCommand(strSqlInsert, con);
cmd.Parameters.AddWithValue("@ProductId", ps.ProductId);
cmd.Parameters.AddWithValue("@SupplierId", ps.SupplierId);
try
{
con.Open();
//execute insert statement
cmd.ExecuteNonQuery();
//get ProductSupplier Id of inserted record from database
string strSqlSelect = "SELECT IDENT_CURRENT('Products_Suppliers') FROM Products_Suppliers";
SqlCommand cmdSelect = new SqlCommand(strSqlSelect, con);
ProductSupplierId = Convert.ToInt32(cmdSelect.ExecuteScalar());
}
catch (Exception ex) //handle sql exceptions
{
throw ex;
}
finally
{
con.Close();
}
//return result
return(ProductSupplierId);
}
public static List <Supplier> DisplaySuppliersInList(int pkgId)
{
List <Supplier> supplierList = new List <Supplier>(); //crating empty list
Supplier supplierObj = null; //referencing supplier object
string selectQuery = "SELECT s.SupplierId as 'supplier',s.SupName,ps.ProductSupplierId " +
"FROM Suppliers s " +
"INNER JOIN Products_Suppliers ps " +
"ON s.SupplierId = ps.SupplierId " +
"INNER JOIN Packages_Products_Suppliers pps " +
"ON ps.ProductSupplierId = pps.ProductSupplierId " +
"INNER JOIN Packages pk " +
"ON pps.PackageId = pk.PackageId WHERE pk.PackageId = @pkgId";
try
{
using (SqlConnection con = TravelExpertsDBConn.getDbConnection())
{
using (SqlCommand cmd = new SqlCommand(selectQuery, con))
{
con.Open(); //databse connection opens
cmd.Parameters.AddWithValue("@pkgId", pkgId); //binding it with PkgId parameter which is passed on in an arguement
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //Data reader executes the query and bring all data before closing connection to the table
while (dr.Read()) //below block of code executes till there is data in the table
{
supplierObj = new Supplier(); //instantiating the object of the class Supplier
supplierObj.SupName = (string)dr["SupName"];
supplierList.Add(supplierObj); //adding supplier items into the list
}
}
return(supplierList); //returns the list of suppliers
}
}
catch (Exception ex)
{
throw ex;
}
}
//grabs the agents
public static List <Agent> GetAgents()
{
//list and holder created
List <Agent> agents = new List <Agent>();
Agent agentHolder;
SqlConnection connect = TravelExpertsDBConn.getDbConnection();//defines db connection
string selectQuery = "SELECT AgentId, AgtFirstName, AgtMiddleInitial, AgtLastName," +
"AgtBusPhone, AgtEmail, AgtPosition, AgencyId FROM agents";
SqlCommand cmd = new SqlCommand(selectQuery, connect);
try
{
connect.Open();
SqlDataReader reader = cmd.ExecuteReader();
//runs until all data has been read
while (reader.Read())
{
agentHolder = new Agent();
agentHolder.AgentID = (int)reader["AgentId"];
agentHolder.AgtFirstName = reader["AgtFirstName"].ToString();
agentHolder.AgtLastName = reader["AgtLastName"].ToString();
agentHolder.AgtMiddleInitial = reader["AgtMiddleInitial"].ToString();
agentHolder.AgtBusPhone = reader["AgtBusPhone"].ToString();
agentHolder.AgtEmail = reader["AgtEmail"].ToString();
agentHolder.AgtPosition = reader["AgtPosition"].ToString();
agentHolder.AgencyID = (int)reader["AgencyId"];
agents.Add(agentHolder);//adds to list
}
}
catch (Exception e) { throw e; }
finally { connect.Close(); }
return(agents);
}
/// <summary>
/// GetPackage() method mentions the logic about getting all packages from the Packages table from TravelExpert database from SQL
/// and the method is called at presentation layer on the Main form_load event.
/// </summary>
/// <returns>List of packages</returns>
public static List <Package> DisplayPackagesInGrid()
{
List <Package> packageList = new List <Package>(); //crating empty list
Package packageObj = null; //referencing package object
string selectQuery = "SELECT * FROM Packages ORDER BY PkgName ASC"; //SQL query to get all fields from table
try
{
using (SqlConnection con = TravelExpertsDBConn.getDbConnection())
{
using (SqlCommand cmd = new SqlCommand(selectQuery, con))
{
con.Open(); //databse connection opens
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //Data reader executes the query and bring all data before closing connection to the table
while (dr.Read()) //below block of code executes till there is data in the table
{
packageObj = new Package(); //instantiating the object of the class Package
packageObj.PackageId = (int)dr["PackageId"];
packageObj.PkgName = (string)dr["PkgName"];
packageObj.PkgStartDate = dr["PkgStartDate"] == DBNull.Value ? null : (DateTime?)dr["PkgStartDate"];
packageObj.PkgEndDate = dr["PkgEndDate"] == DBNull.Value ? null : (DateTime?)dr["PkgEndDate"];
packageObj.PkgDesc = dr["PkgDesc"] == DBNull.Value ? null : (string)dr["PkgDesc"];
packageObj.PkgBasePrice = double.Parse(dr["PkgBasePrice"].ToString());
packageObj.PkgAgencyCommission = dr["PkgAgencyCommission"] == DBNull.Value ? Convert.ToDouble(null) : double.Parse(dr["PkgAgencyCommission"].ToString());
packageList.Add(packageObj); //adding package items into the list
}
}
return(packageList); //returns the list of package
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// this below method has the logic of deleting the selected package information. it also takes care of database concurrancy
/// </summary>
/// <param name="the entire package object is passed as parameter as it contains all information to be deleted"></param>
/// <returns>it returns the boolean(true/false), true if delete is successful and false if not</returns>
public static bool PackageDelete(Package pkgObj)
{
bool result = true;
string deleteStatement = "DELETE FROM Packages WHERE PackageId = @PackageId " +
"AND PkgName = @PkgName " +
"AND (PkgDesc = @PkgDesc OR @PkgDesc is NULL AND PkgDesc is NULL) " +
"AND (PkgStartDate = @PkgStartDate OR @PkgStartDate is NULL AND PkgStartDate is NULL) " +
"AND (PkgEndDate = @PkgEndDate OR @PkgEndDate is NULL AND PkgEndDate is NULL) " +
"AND PkgBasePrice = @PkgBasePrice " +
"AND (PkgAgencyCommission = @PkgAgencyCommission OR @PkgAgencyCommission is NULL AND PkgAgencyCommission is NULL)";
try
{
using (SqlConnection con = TravelExpertsDBConn.getDbConnection())
{
using (SqlCommand cmd = new SqlCommand(deleteStatement, con))
{
con.Open();
cmd.Parameters.AddWithValue("@PackageId", pkgObj.PackageId);
cmd.Parameters.AddWithValue("@PkgName", pkgObj.PkgName);
cmd.Parameters.AddWithValue("@PkgBasePrice", pkgObj.PkgBasePrice);
if (pkgObj.PkgStartDate.HasValue)
{
cmd.Parameters.AddWithValue("@PkgStartDate", pkgObj.PkgStartDate);
}
else
{
cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
}
if (pkgObj.PkgEndDate.HasValue)
{
cmd.Parameters.AddWithValue("@PkgEndDate", pkgObj.PkgEndDate);
}
else
{
cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
}
if (String.IsNullOrEmpty(pkgObj.PkgDesc))
{
cmd.Parameters.AddWithValue("@PkgDesc", "");
}
else
{
cmd.Parameters.AddWithValue("@PkgDesc", pkgObj.PkgDesc);
}
if (pkgObj.PkgAgencyCommission.HasValue)
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", pkgObj.PkgAgencyCommission);
}
else
{
cmd.Parameters.AddWithValue("@PkgAgencyCommission", 0);
}
int count = cmd.ExecuteNonQuery();
if (count == 0) // optimistic concurrency violation
{
result = false;
}
}
}
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// this method contains the logic to save the updated record in the database.
/// </summary>
/// <param name="package"></param>
/// <param name="here 2 parameters are passed in, 1st one is old package object which is already ther in the database
/// and 2nd one is new package object which contains updated information from the package form"></param>
/// <returns>it returns the boolean(true/false), true if update is successful and false if not</returns>
public static bool PackageUpdate(Package package, Package newPackage)
{
bool result = true;
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";
try
{
using (SqlConnection con = TravelExpertsDBConn.getDbConnection())
{
using (SqlCommand cmd = new SqlCommand(updateStatement, con))
{
con.Open();
//cmd.Parameters.AddWithValue("@PackageId", newPackage.PackageId);
cmd.Parameters.AddWithValue("@newPkgName", newPackage.PkgName);
cmd.Parameters.AddWithValue("@newPkgBasePrice", newPackage.PkgBasePrice);
if (newPackage.PkgStartDate.HasValue)
{
cmd.Parameters.AddWithValue("@newPkgStartDate", newPackage.PkgStartDate);
}
else
{
cmd.Parameters.AddWithValue("@newPkgStartDate", DBNull.Value);
}
if (newPackage.PkgEndDate.HasValue)
{
cmd.Parameters.AddWithValue("@newPkgEndDate", newPackage.PkgEndDate);
}
else
{
cmd.Parameters.AddWithValue("@newPkgEndDate", DBNull.Value);
}
if (newPackage.PkgDesc == null)
{
cmd.Parameters.AddWithValue("@newPkgDesc", "");
}
else
{
cmd.Parameters.AddWithValue("@newPkgDesc", newPackage.PkgDesc);
}
if (newPackage.PkgAgencyCommission == null)
{
cmd.Parameters.AddWithValue("@newPkgAgencyCommission", 0);
}
else
{
cmd.Parameters.AddWithValue("@newPkgAgencyCommission", newPackage.PkgAgencyCommission);
}
cmd.Parameters.AddWithValue("@oldPackageId", package.PackageId);
cmd.Parameters.AddWithValue("@oldPkgName", package.PkgName);
cmd.Parameters.AddWithValue("@oldPkgBasePrice", package.PkgBasePrice);
if (package.PkgStartDate.HasValue)
{
cmd.Parameters.AddWithValue("@oldPkgStartDate", package.PkgStartDate);
}
else
{
cmd.Parameters.AddWithValue("@oldPkgStartDate", DBNull.Value);
}
if (package.PkgEndDate.HasValue)
{
cmd.Parameters.AddWithValue("@oldPkgEndDate", package.PkgEndDate);
}
else
{
cmd.Parameters.AddWithValue("@oldPkgEndDate", DBNull.Value);
}
if (package.PkgDesc == null)
{
cmd.Parameters.AddWithValue("@oldPkgDesc", "");
}
else
{
cmd.Parameters.AddWithValue("@oldPkgDesc", package.PkgDesc);
}
if (package.PkgAgencyCommission == null)
{
cmd.Parameters.AddWithValue("@oldPkgAgencyCommission", package.PkgAgencyCommission);
}
else
{
cmd.Parameters.AddWithValue("@oldPkgAgencyCommission", 0);
}
int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
result = false; // did not update (another user updated or deleted)
}
}
return(result);
}
}
catch (DBConcurrencyException ex)
{
throw ex;
}
}