//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; } }