public void saveData() { if (!IsValid()) { return; } int idEmployee = int.Parse(cbHousekeeper.SelectedValue.ToString()); var cleaning = db.CleaningRoom .FirstOrDefault(X => X.Date == dtpDate.Value.Date && X.EmployeeID == idEmployee); if (cleaning == null) { cleaning = new CleaningRoom { Date = dtpDate.Value.Date, EmployeeID = idEmployee }; db.CleaningRoom.Add(cleaning); db.SaveChanges(); } int idRoom = int.Parse(cbRoomNumber.SelectedValue.ToString()); db.CleaningRoomDetail.Add(new CleaningRoomDetail { CleaningRoomID = cleaning.ID, RoomID = idRoom }); db.SaveChanges(); loadData(); }
private void btnImport_Click(object sender, EventArgs e) { var excel = new Excel.Application(); var workbooks = excel.Workbooks; var dialog = new OpenFileDialog { Title = "Choose location to save", Filter = "excel file(*.xlsx)|*.xlsx", Multiselect = false }; if (dialog.ShowDialog() != DialogResult.OK) { return; } var workbook = workbooks.Open(dialog.FileName); Excel.Worksheet worksheet = workbook.Worksheets[1]; var cells = worksheet.Cells; cells.ClearFormats(); var range = worksheet.UsedRange; var columns = range.Columns; var rows = range.Rows; Excel.Range range2 = worksheet.Range[worksheet.Cells[5, 1], worksheet.Cells[rows.Count, columns.Count]]; var values = (object[, ])range2.Value2; string lastRoomNumber = ""; var cleaningRoom = db.CleaningRoom .Where(X => X.EmployeeID == X.Employee.ID) .ToArray(); var cleaningDetail = db.CleaningRoomDetail .ToArray() .Where(X => cleaningRoom.Select(Y => Y.ID) .Contains(X.CleaningRoomID)) .ToArray(); var cleaningItem = db.CleaningRoomItem .ToArray() .Where(X => cleaningDetail.Select(Y => Y.CleaningRoomID) .Contains(X.ID)) .ToArray(); try{ db.CleaningRoomItem.RemoveRange(cleaningItem); db.SaveChanges(); db.CleaningRoomDetail.RemoveRange(cleaningDetail); db.SaveChanges(); db.CleaningRoom.RemoveRange(cleaningRoom); db.SaveChanges(); } catch { MessageBox.Show("Error during deleting"); } string date = cells[1, 2].Value2.ToString(); DateTime date2 = new DateTime(); try { date2 = DateTime.ParseExact(date, "dd MM yyyy hh:mm", null); } catch { MessageBox.Show("date time in wrong Format"); date2 = DateTime.Now.Date; } var cleaning = new CleaningRoom { Date = date2, EmployeeID = employee.ID }; db.CleaningRoom.Add(cleaning); db.SaveChanges(); var detail = new CleaningRoomDetail(); for (int i = 1; i <= values.GetLength(0); i++) { bool addingNew = false; if (values[i, 1] != null && values[i, 1].ToString() != "") { lastRoomNumber = values[i, 1].ToString(); addingNew = true; } if (addingNew) { detail.CleaningRoomID = cleaning.ID; detail.RoomID = db.Room.FirstOrDefault(x => x.RoomNumber == lastRoomNumber).ID; if (values[i, 2] == null) { detail.StartDateTime = null; } else { detail.StartDateTime = DateTime.ParseExact(values[i, 2].ToString(), "dd MMMM yyyy hh:mm", CultureInfo.InvariantCulture); } if (values[i, 3] == null) { detail.FinishDateTime = null; } else { detail.FinishDateTime = DateTime.ParseExact(values[i, 3].ToString(), "dd MMMM yyyy hh:mm", CultureInfo.InvariantCulture); } if (values[i, 4] == null) { detail.Note = null; } else { detail.Note = values[i, 4].ToString(); } if (values[i, 5] == null) { detail.StatusCleaning = null; } else { detail.StatusCleaning = values[i, 5].ToString(); } db.CleaningRoomDetail.Add(detail); db.SaveChanges(); } string name = values[i, 6].ToString(); int qty = int.Parse(values[i, 7].ToString()); string status = values[i, 8].ToString(); var item = db.Item.FirstOrDefault(x => x.Name == name); if (item == null) { MessageBox.Show($"Item not found at row {i}"); } else { var detailItem = new CleaningRoomItem { CleaningRoomDetailID = detail.ID, ItemID = item.ID, Qty = qty, Status = status }; db.CleaningRoomItem.Add(detailItem); db.SaveChanges(); } } Marshal.ReleaseComObject(range2); Marshal.ReleaseComObject(columns); Marshal.ReleaseComObject(rows); Marshal.ReleaseComObject(range); Marshal.ReleaseComObject(cells); Marshal.ReleaseComObject(worksheet); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(excel); }