public List<DonorBooking> GetAllDonorBookings()
{
List<DonorBooking> bookings = new List<DonorBooking>();
SqlConnection conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = " + System.Web.HttpContext.Current.Server.MapPath(privilegesDatabase));
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT D.bookingID, D.bookingDate, D.logonName, D.parkingID, D.durationHours, u.firstName, u.lastName FROM DonorBooking as D JOIN Users AS u ON(D.logonName=u.logonName)", conn);
var reader = cmd.ExecuteReader();
// write each record
while (reader.Read()) {
DateTime dtResult;
int bookingID;
int durationHours;
Int32.TryParse(reader["bookingID"] != null ? reader["bookingID"].ToString() : String.Empty, out bookingID);
Int32.TryParse(reader["durationHours"] != null ? reader["durationHours"].ToString() : String.Empty, out durationHours);
string readLogonName = reader["logonName"].ToString();
DateTime.TryParse(reader["bookingDate"] != null ? reader["bookingDate"].ToString() : String.Empty, out dtResult);
int? parkingID = reader.IsDBNull(reader.GetOrdinal("parkingID")) ? (int?)null : Int32.Parse(reader["parkingID"].ToString());
string firstName = reader["firstName"] != null ? reader["firstName"].ToString() : String.Empty;
string lastName = reader["lastName"] != null ? reader["lastName"].ToString() : String.Empty;
DonorBooking booking = new DonorBooking(bookingID, dtResult, readLogonName, durationHours, parkingID);
if (!String.IsNullOrEmpty(firstName) && !String.IsNullOrEmpty(lastName)) {
booking.DisplayName = firstName + " " + lastName;
}
if (dtResult != null) bookings.Add(booking);
}
cmd.Dispose();
reader.Close();
conn.Dispose();
return bookings;
}
public bool BookDonorAppointment(DateTime bookingDate, int durationHours, string logonName)
{
int rowsUpdated = 0;
DonorBooking booking = new DonorBooking(bookingDate, durationHours, logonName);
SqlConnection conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = " + System.Web.HttpContext.Current.Server.MapPath(privilegesDatabase));
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO DonorBooking (bookingDate, durationHours, logonName) values (@bookingDate, @durationHours, @logonName)", conn);
cmd.Parameters.Add("@bookingDate", SqlDbType.DateTime);
cmd.Parameters["@bookingDate"].Value = booking.BookingDate == DateTime.MinValue ? SqlDateTime.MinValue : booking.BookingDate;
cmd.Parameters.Add("@durationHours", SqlDbType.Int);
cmd.Parameters["@durationHours"].Value = booking.DurationHours;
cmd.Parameters.Add("@logonName", SqlDbType.VarChar, 35);
cmd.Parameters["@logonName"].Value = booking.LogonName;
rowsUpdated = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Dispose();
return rowsUpdated == 0 ? false : true;
}