// Sorting Using DB OrderBy
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
ComboBox comboBox = (ComboBox)sender;
string selectedItem = (string)comboBox.SelectedItem;
List <BorrowHistory> tempList = new List <BorrowHistory>();
// Find the records (in the borrowHistory table) related to movies that were borrowed by this user but never returned (return date is 1900) and are overdue
switch ((string)comboBox.SelectedItem)
{
case "User ID":
tempList = context.BorrowHistories.Where(b => DateTime.Compare(b.BorrowDate, overDueDate) < 0 && DateTime.Compare(b.ReturnDate, new DateTime(1910, 1, 1, 0, 0, 0)) < 0).OrderBy(b => b.UserId).ToList();
break;
case "Movie ID":
tempList = context.BorrowHistories.Where(b => DateTime.Compare(b.BorrowDate, overDueDate) < 0 && DateTime.Compare(b.ReturnDate, new DateTime(1910, 1, 1, 0, 0, 0)) < 0).OrderBy(b => b.MovieId).ToList();
break;
case "Borrow Date":
tempList = context.BorrowHistories.Where(b => DateTime.Compare(b.BorrowDate, overDueDate) < 0 && DateTime.Compare(b.ReturnDate, new DateTime(1910, 1, 1, 0, 0, 0)) < 0).OrderBy(b => b.BorrowDate).ToList();
break;
}
bhList.Clear();
foreach (BorrowHistory bh in tempList) // convert the normal list to an observable list
{
bhList.Add(bh);
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
movieList = new ObservableListSource <Movie>();
this.dataGridView1.DataSource = movieList;
returningUser = context.Users.Where(u => u.Email.Equals(txtUserEmail.Text)).FirstOrDefault();
if (returningUser == null) // user does not exist
{
MessageBox.Show("User not found, Please try again.");
this.txtUserEmail.Focus();
movieList.Clear();
return;
}
// Find the records (in the borrowHistory table) related to movies that were borrowed by this user but never returned (return date is 1900)
List <BorrowHistory> bhList = context.BorrowHistories.Where(b => b.UserId == returningUser.UserId && DateTime.Compare(b.ReturnDate, new DateTime(1910, 1, 1, 0, 0, 0)) < 0).ToList();
// Find the movies related to above records
foreach (BorrowHistory bh in bhList)
{
movieList.Add(context.Movies.Where(m => m.MovieId == bh.MovieId).FirstOrDefault());
}
// Add movies to the form
dataGridView1.Columns[8].Visible = false;
dataGridView1.Columns[9].Visible = false;
}
