public int SaveManagerSkill(PersonModel person, SkillsModel skill) { int ret = 0; using (MySqlConnection conn = GetConnection()) { conn.Open(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT process_manager_skill(@ManagerName,@FullName,@Email,@PersonnelNo,@SkillName,@GroupName,@ParentName,@ReqLevel,@HeldLevel)"; cmd.Parameters.Add("@ManagerName", MySqlDbType.VarChar, 45).Value = person.ManagerName; cmd.Parameters.Add("@FullName", MySqlDbType.VarChar, 45).Value = person.Name; cmd.Parameters.Add("@Email", MySqlDbType.VarChar, 320).Value = person.Email; cmd.Parameters.Add("@PersonnelNo", MySqlDbType.Int32).Value = person.PersonnelNo; cmd.Parameters.Add("@SkillName", MySqlDbType.VarChar, 120).Value = skill.SkillName; cmd.Parameters.Add("@GroupName", MySqlDbType.VarChar, 120).Value = skill.GroupName; cmd.Parameters.Add("@ParentName", MySqlDbType.VarChar, 120).Value = skill.GroupParentName; cmd.Parameters.Add("@ReqLevel", MySqlDbType.VarChar, 45).Value = skill.RequiredSkillLevel; cmd.Parameters.Add("@HeldLevel", MySqlDbType.VarChar, 45).Value = skill.HeldSkillLevel; ret = (int)cmd.ExecuteScalar(); conn.Close(); } return(ret); }
public FileReaderResults AddManagerSkillsReport(SkillsMatrixDB database, string filePath) { FileReaderResults ret = FileReaderResults.FileReaderUnknownError; Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath); Excel._Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; int currentRow = 1; int matches = 0; //iterate over the rows and columns //excel is not zero based!! for (int col = 1; col <= colCount; col++) { Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col]; if (cell.Value2 != null) { if (ManagerColumns.Contains(cell.Value2)) { matches++; } } Marshal.FinalReleaseComObject(cell); } if (matches == ManagerColumns.Count) { ret = FileReaderResults.FileReaderSuccess; } else { ret = FileReaderResults.FileReaderWrongFormat; } try { if (ret == FileReaderResults.FileReaderSuccess) { for (currentRow++; currentRow <= rowCount; currentRow++) { SkillsModel skill = new SkillsModel(); PersonModel person = new PersonModel(); for (int col = 1; col <= colCount; col++) { Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col]; ManagerItem colIdx = (ManagerItem)(col - 1); switch (colIdx) { case ManagerItem.ManagerName: person.ManagerName = cell.Value2.ToString(); break; case ManagerItem.FullName: person.Name = cell.Value2.ToString(); break; case ManagerItem.PersonnelNo: person.PersonnelNo = Int32.Parse(cell.Value2.ToString()); break; case ManagerItem.Email: person.Email = cell.Value2.ToString(); break; case ManagerItem.SkillName: skill.SkillName = cell.Value2.ToString(); break; case ManagerItem.SkillGroupName: skill.GroupName = cell.Value2.ToString(); break; case ManagerItem.SkillGroupParentName: skill.GroupParentName = cell.Value2.ToString(); break; case ManagerItem.RequiredSkill: if (cell.Value2 != null) { skill.RequiredSkillLevel = cell.Value2.ToString(); } else { skill.RequiredSkillLevel = "Not Set"; } break; case ManagerItem.HeldSkill: if (cell.Value2 != null) { skill.HeldSkillLevel = cell.Value2.ToString(); } else { skill.HeldSkillLevel = "Not Set"; } break; } Marshal.FinalReleaseComObject(cell); } if (database.SaveManagerSkill(person, skill) != 1) { ret = FileReaderResults.FileReaderErrorProcessingFile; } } } } catch (Exception ex) { ret = FileReaderResults.FileReaderUnknownError; } finally { //close and release Marshal.FinalReleaseComObject(xlRange); Marshal.FinalReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.FinalReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.FinalReleaseComObject(xlApp); // take the trash out CleanupExcel(); } return(ret); }