/// <summary> /// Method to Insert Record in the Excel /// S1. If the EmpNo =0, then the Operation is Skipped. /// S2. If the Employee is already exist, then it is taken for Update /// </summary> /// <param name="Emp"></param> public async Task<bool> InsertOrUpdateRowInExcelAsync(Employee Emp) { bool IsSave = false; //S1 if (Emp.EmpNo != 0) { await Conn.OpenAsync(); Cmd = new OleDbCommand(); Cmd.Connection = Conn; Cmd.Parameters.AddWithValue("@EmpNo", Emp.EmpNo); Cmd.Parameters.AddWithValue("@EmpName", Emp.EmpName); Cmd.Parameters.AddWithValue("@Salary", Emp.Salary); Cmd.Parameters.AddWithValue("@DeptName", Emp.DeptName); //S2 if (!CheckIfRecordExistAsync(Emp).Result) { Cmd.CommandText = "Insert into [Sheet1$] values (@EmpNo,@EmpName,@Salary,@DeptName)"; } else { if (Emp.EmpName != String.Empty || Emp.DeptName != String.Empty) { Cmd.CommandText = "Update [Sheet1$] set EmpNo=@EmpNo,EmpName=@EmpName,Salary=@Salary,DeptName=@DeptName where EmpNo=@EmpNo"; } } int result = await Cmd.ExecuteNonQueryAsync(); if (result > 0) { IsSave = true; } Conn.Close(); } return IsSave; }
/// <summary> /// Select the Recod for the Update /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void dgEmp_SelectionChanged(object sender, SelectionChangedEventArgs e) { emp = dgEmp.SelectedItem as Employee; }
/// <summary> /// The method to check if the record is already available /// in the workgroup /// </summary> /// <param name="emp"></param> /// <returns></returns> private async Task<bool> CheckIfRecordExistAsync(Employee emp) { bool IsRecordExist = false; Cmd.CommandText = "Select * from [Sheet1$] where EmpNo=@EmpNo"; var Reader = await Cmd.ExecuteReaderAsync(); if (Reader.HasRows) { IsRecordExist = true; } Reader.Close(); return IsRecordExist; }