// retrieve object with given ID public static Bookings GetValue(int objID) { Bookings obj = null; // create connection SqlConnection connection = TravelExperts.GetConection(); // create SELECT command string query = "SELECT BookingId, BookingDate, BookingNo, TravelerCount, CustomerId, TripTypeId, PackageId " + "FROM Bookings " + "WHERE TripTypeId = @TripTypeId "; SqlCommand cmd = new SqlCommand(query, connection); // suply perameter value cmd.Parameters.AddWithValue("@TripTypeId", 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 Bookings(); obj.BookingId = Convert.ToInt32(reader["BookingId"]); obj.BookingDate = (DateTime)reader["BookingDate"]; obj.BookingNo = reader["BookingNo"].ToString(); obj.TravelerCount = Convert.ToSingle(reader["TravelerCount"]); obj.CustomerId = Convert.ToInt32(reader["CustomerId"]); obj.TripTypeId = reader["TripTypeId"].ToString(); if (reader["PackageId"].ToString() == "") { obj.PackageId = null; } else { obj.PackageId = Convert.ToInt32(reader["PackageId"].ToString()); } } reader.Close(); } catch (Exception ex) { throw ex; } finally // executes always { connection.Close(); } return(obj); }
// makes identival copy of Customer public Bookings Clone() { Bookings copy = new Bookings(); copy.BookingId = this.BookingId; copy.BookingDate = this.BookingDate; copy.BookingNo = this.BookingNo; copy.TravelerCount = this.TravelerCount; copy.CustomerId = this.CustomerId; copy.TripTypeId = this.TripTypeId; copy.PackageId = this.PackageId; return(copy); }
// Delete object // return indicator of success public static bool Delete(Bookings obj) { bool success = false; // create connection SqlConnection connection = TravelExperts.GetConection(); // create DELETE command string deleteStatment = "DELETE FROM Bookings " + "WHERE TripTypeId = @TripTypeId " + // needed for identification of object "AND TTName = @TTName "; // the rest - for optimistic concurrency SqlCommand cmd = new SqlCommand(deleteStatment, connection); // suply perameter value cmd.Parameters.AddWithValue("@BookingId", obj.BookingId); cmd.Parameters.AddWithValue("@BookingDate", obj.BookingDate); cmd.Parameters.AddWithValue("@BookingNo", obj.BookingNo); cmd.Parameters.AddWithValue("@TravelerCount", obj.TravelerCount); cmd.Parameters.AddWithValue("@TripTypeId", obj.TripTypeId); cmd.Parameters.AddWithValue("@CustomerId", obj.CustomerId); cmd.Parameters.AddWithValue("@TripTypeId", obj.TripTypeId); cmd.Parameters.AddWithValue("@PackageId", obj.PackageId); // 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); }
// insert new row to table // return new object public static int Add(Bookings obj) { int custID = 0; // create connection SqlConnection connection = TravelExperts.GetConection(); // create INSERT command // CustomerID is IDENTITY so no value provided string insertStatment = "INSERT INTO Bookings(BookingId, BookingDate, BookingNo, TravelerCount, CustomerId, TripTypeId, PackageId) " + "OUTPUT inserted.[TripTypeId] " + "VALUES(@BookingId, @BookingDate, @BookingNo, @TravelerCount, @CustomerId, @TripTypeId, @PackageId) "; SqlCommand cmd = new SqlCommand(insertStatment, connection); // suply perameter value cmd.Parameters.AddWithValue("@BookingId", obj.BookingId); cmd.Parameters.AddWithValue("@BookingDate", obj.BookingDate); cmd.Parameters.AddWithValue("@BookingNo", obj.BookingNo); cmd.Parameters.AddWithValue("@TravelerCount", obj.TravelerCount); cmd.Parameters.AddWithValue("@TripTypeId", obj.TripTypeId); cmd.Parameters.AddWithValue("@CustomerId", obj.CustomerId); cmd.Parameters.AddWithValue("@TripTypeId", obj.TripTypeId); cmd.Parameters.AddWithValue("@PackageId", obj.PackageId); // 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 <Bookings> GetAll() { // create SELECT command string query = "SELECT BookingId, BookingDate, BookingNo, TravelerCount, CustomerId, TripTypeId, PackageId " + "FROM Bookings "; SqlConnection connection = TravelExperts.GetConection(); List <Bookings> dataList = new List <Bookings>(); // epmty list Bookings 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 Bookings(); data.BookingId = Convert.ToInt32(reader["BookingId"]); data.BookingDate = (DateTime)reader["BookingDate"]; data.BookingNo = reader["BookingNo"].ToString(); data.TravelerCount = Convert.ToSingle(reader["TravelerCount"]); data.CustomerId = Convert.ToInt32(reader["CustomerId"]); data.TripTypeId = reader["TripTypeId"].ToString(); if (reader["PackageId"].ToString() == "") { data.PackageId = null; } else { data.PackageId = Convert.ToInt32(reader["PackageId"].ToString()); } dataList.Add(data); } return(dataList); }
// Update object // return indicator of success public static bool Update(Bookings oldObj, Bookings newObj) { bool success = false; // did not update // create connection SqlConnection connection = TravelExperts.GetConection(); // create UPDATE command string updateStatment = "UPDATE Bookings SET " + "BookingId = @NewNewBookingId, " + "BookingDate = @NewNewBookingDate, " + "BookingNo = @NewNewBookingNo, " + "TravelerCount = @NewNewTravelerCount, " + "TripTypeId = @NewNewTripTypeId, " + "CustomerId = @NewNewCustomerId, " + "TripTypeId = @NewTripTypeId, " + "PackageId = @NewPackageId " + "WHERE BookingId = @OldBookingId " + // identifies "AND BookingDate = @OldBookingDate " + // the rest - for optimistic concurrency "AND BookingNo = @OldBookingNo " + "AND TravelerCount = @OldTravelerCount " + "AND TripTypeId = @OldTripTypeId " + "AND CustomerId = @OldCustomerId " + "AND TripTypeId = @OldTripTypeId " + "AND PackageId = @OldPackageId "; SqlCommand cmd = new SqlCommand(updateStatment, connection); // suply perameter value // New object Values cmd.Parameters.AddWithValue("@NewBookingId", newObj.BookingId); cmd.Parameters.AddWithValue("@NewBookingDate", newObj.BookingDate); cmd.Parameters.AddWithValue("@NewBookingNo", newObj.BookingNo); cmd.Parameters.AddWithValue("@NewTravelerCount", newObj.TravelerCount); cmd.Parameters.AddWithValue("@NewTripTypeId", newObj.TripTypeId); cmd.Parameters.AddWithValue("@NewCustomerId", newObj.CustomerId); cmd.Parameters.AddWithValue("@NewTripTypeId", newObj.TripTypeId); cmd.Parameters.AddWithValue("@NewPackageId", newObj.PackageId); // ID cmd.Parameters.AddWithValue("@OldBookingId", oldObj.BookingId); // Old object Values cmd.Parameters.AddWithValue("@OldBookingDate", oldObj.BookingDate); cmd.Parameters.AddWithValue("@OldBookingNo", oldObj.BookingNo); cmd.Parameters.AddWithValue("@OldTravelerCount", oldObj.TravelerCount); cmd.Parameters.AddWithValue("@OldTripTypeId", oldObj.TripTypeId); cmd.Parameters.AddWithValue("@OldCustomerId", oldObj.CustomerId); cmd.Parameters.AddWithValue("@OldTripTypeId", oldObj.TripTypeId); cmd.Parameters.AddWithValue("@OldPackageId", oldObj.PackageId); // 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); }