// Delete object // return indicator of success public static bool Delete(Agencies obj) { bool success = false; // create connection SqlConnection connection = TravelExperts.GetConection(); // create DELETE command string deleteStatment = "DELETE FROM Agencies " + "WHERE AgencyId = @AgencyId " + // needed for identification of object "AND AgncyAddress = @AgncyAddress " + // the rest - for optimistic concurrency "AND AgncyCity = @AgncyCity " + "AND AgncyProv = @AgncyProv " + "AND AgncyPostal = @AgncyPostal " + "AND AgncyCountry = @AgncyCountry " + "AND AgncyPhone = @AgncyPhone " + "AND AgncyFax = @AgncyFax "; SqlCommand cmd = new SqlCommand(deleteStatment, connection); // suply perameter value cmd.Parameters.AddWithValue("@AgencyId", obj.AgencyId); cmd.Parameters.AddWithValue("@AgncyAddress", obj.AgncyAddress); cmd.Parameters.AddWithValue("@AgncyCity", obj.AgncyCity); cmd.Parameters.AddWithValue("@AgncyProv", obj.AgncyProv); cmd.Parameters.AddWithValue("@AgncyPostal", obj.AgncyPostal); cmd.Parameters.AddWithValue("@AgncyCountry", obj.AgncyCountry); cmd.Parameters.AddWithValue("@AgncyPhone", obj.AgncyPhone); cmd.Parameters.AddWithValue("@AgncyFax", obj.AgncyFax); // execute the command try { // open the conection connection.Open(); // execute the command int count = cmd.ExecuteNonQuery(); // check if successful if (count > 0) { success = true; } } catch (Exception ex) { throw ex; } finally // executes always { connection.Close(); } // retrieve generated customer nID to return return(success); }
// retrieve object with given ID public static Agencies GetValue(int objID) { Agencies obj = null; // create connection SqlConnection connection = TravelExperts.GetConection(); // create SELECT command string query = "SELECT AgencyId, AgncyAddress, AgncyCity, AgncyProv, AgncyPostal, AgncyCountry, AgncyPhone, AgncyFax " + "FROM Agencies " + "WHERE AgencyId = @AgencyId "; SqlCommand cmd = new SqlCommand(query, connection); // suply perameter value cmd.Parameters.AddWithValue("@AgencyId", objID); // run the SELECT query try { // open the conection connection.Open(); // run the command SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); // build object object to return if (reader.Read()) // if there is a object with this ID { obj = new Agencies(); obj.AgencyId = Convert.ToInt32(reader["AgencyId"]); obj.AgncyAddress = reader["AgncyAddress"].ToString(); obj.AgncyCity = reader["AgncyCity"].ToString(); obj.AgncyProv = reader["AgncyProv"].ToString(); obj.AgncyPostal = reader["AgncyPostal"].ToString(); obj.AgncyCountry = reader["AgncyCountry"].ToString(); obj.AgncyPhone = reader["AgncyPhone"].ToString(); obj.AgncyFax = reader["AgncyFax"].ToString(); } reader.Close(); } catch (Exception ex) { throw ex; } finally // executes always { connection.Close(); } return(obj); }
// makes identival copy of Customer public Agencies Clone() { Agencies copy = new Agencies(); copy.AgencyId = this.AgencyId; copy.AgncyAddress = this.AgncyAddress; copy.AgncyCity = this.AgncyCity; copy.AgncyProv = this.AgncyProv; copy.AgncyPostal = this.AgncyPostal; copy.AgncyCountry = this.AgncyCountry; copy.AgncyPhone = this.AgncyPhone; copy.AgncyFax = this.AgncyFax; return(copy); }
// insert new row to table // return new object public static int Add(Agencies obj) { int custID = 0; // create connection SqlConnection connection = TravelExperts.GetConection(); // create INSERT command // CustomerID is IDENTITY so no value provided string insertStatment = "INSERT INTO Agencies(AgencyId, AgncyAddress, AgncyCity, AgncyProv, AgncyPostal, AgncyCountry, AgncyPhone, AgncyFax) " + "OUTPUT inserted.[AgencyId] " + "VALUES(@AgencyId, @AgencyId) "; SqlCommand cmd = new SqlCommand(insertStatment, connection); // suply perameter value cmd.Parameters.AddWithValue("@AgencyId", obj.AgencyId); cmd.Parameters.AddWithValue("@AgncyAddress", obj.AgncyAddress); cmd.Parameters.AddWithValue("@AgncyCity", obj.AgncyCity); cmd.Parameters.AddWithValue("@AgncyProv", obj.AgncyProv); cmd.Parameters.AddWithValue("@AgncyPostal", obj.AgncyPostal); cmd.Parameters.AddWithValue("@AgncyCountry", obj.AgncyCountry); cmd.Parameters.AddWithValue("@AgncyPhone", obj.AgncyPhone); cmd.Parameters.AddWithValue("@AgncyFax", obj.AgncyFax); // execute the INSERT command try { // open the conection connection.Open(); // execute insert command custID = (int)cmd.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally // executes always { connection.Close(); } // retrieve generated customer nID to return return(custID); }
// retrieve all objects public static List <Agencies> GetAll() { // create SELECT command string query = "SELECT AgencyId, AgncyAddress, AgncyCity, AgncyProv, AgncyPostal, AgncyCountry, AgncyPhone, AgncyFax " + "FROM Agencies "; SqlConnection connection = TravelExperts.GetConection(); List <Agencies> dataList = new List <Agencies>(); // epmty list Agencies data; // for reading // create connection SqlCommand cmd = new SqlCommand(query, connection); // open the conection connection.Open(); // run the command SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // build object list to return while (reader.Read()) // if there is a object with this ID { data = new Agencies(); data.AgencyId = Convert.ToInt32(reader["AgencyId"]); data.AgncyAddress = reader["AgncyAddress"].ToString(); data.AgncyCity = reader["AgncyCity"].ToString(); data.AgncyProv = reader["AgncyProv"].ToString(); data.AgncyPostal = reader["AgncyPostal"].ToString(); data.AgncyCountry = reader["AgncyCountry"].ToString(); data.AgncyPhone = reader["AgncyPhone"].ToString(); data.AgncyFax = reader["AgncyFax"].ToString(); dataList.Add(data); } return(dataList); }
// Update object // return indicator of success public static bool Update(Agencies oldObj, Agencies newObj) { bool success = false; // did not update // create connection SqlConnection connection = TravelExperts.GetConection(); // create UPDATE command string updateStatment = "UPDATE Agencies SET " + "AgencyId = @NewAgencyId, " + "AgncyAddress = @NewAgncyAddress, " + "AgncyCity = @NewAgncyCity, " + "AgncyProv = @NewAgncyProv, " + "AgncyPostal = @NewAgncyPostal, " + "AgncyCountry = @NewAgncyCountry, " + "AgncyPhone = @NewAgncyPhone, " + "AgncyFax = @NewAgncyFax " + "WHERE AgencyId = @OldAgencyId " + // needed for identification of object "AND AgncyAddress = @OldAgncyAddress " + // the rest - for optimistic concurrency "AND AgncyCity = @OldAgncyCity " + "AND AgncyProv = @OldAgncyProv " + "AND AgncyPostal = @OldAgncyPostal " + "AND AgncyCountry = @OldAgncyCountry " + "AND AgncyPhone = @OldAgncyPhone " + "AND AgncyFax = @OldAgncyFax "; SqlCommand cmd = new SqlCommand(updateStatment, connection); // suply perameter value // New object Values cmd.Parameters.AddWithValue("@NewAgencyId", newObj.AgencyId); cmd.Parameters.AddWithValue("@NewAgncyAddress", newObj.AgncyAddress); cmd.Parameters.AddWithValue("@NewAgncyCity", newObj.AgncyCity); cmd.Parameters.AddWithValue("@NewAgncyProv", newObj.AgncyProv); cmd.Parameters.AddWithValue("@NewAgncyPostal", newObj.AgncyPostal); cmd.Parameters.AddWithValue("@NewAgncyCountry", newObj.AgncyCountry); cmd.Parameters.AddWithValue("@NewAgncyPhone", newObj.AgncyPhone); cmd.Parameters.AddWithValue("@NewAgncyFax", newObj.AgncyFax); // ID cmd.Parameters.AddWithValue("@OldAgencyId", oldObj.AgencyId); // Old object Values cmd.Parameters.AddWithValue("@OldAgncyAddress", oldObj.AgncyAddress); cmd.Parameters.AddWithValue("@OldAgncyCity", oldObj.AgncyCity); cmd.Parameters.AddWithValue("@OldAgncyProv", oldObj.AgncyProv); cmd.Parameters.AddWithValue("@OldAgncyPostal", oldObj.AgncyPostal); cmd.Parameters.AddWithValue("@OldAgncyCountry", oldObj.AgncyCountry); cmd.Parameters.AddWithValue("@OldAgncyPhone", oldObj.AgncyPhone); cmd.Parameters.AddWithValue("@OldAgncyFax", oldObj.AgncyFax); // execute the UPDATE command try { // open the conection connection.Open(); // execute the command int count = cmd.ExecuteNonQuery(); // check if successful if (count > 0) { success = true; // updated } } catch (Exception ex) { throw ex; } finally // executes always { connection.Close(); } // retrieve generated object ID to return return(success); }