private List <EvaluationDetail> getEvaluationDetailsByCandidateId(int candidateId) { var res = new List <EvaluationDetail>(); var skillRepository = new SkillRepository(); var skills = skillRepository.GetAllSkills(); var query = "SELECT [ED].[Id], " + " [ED].[EvaluationId]," + " [ED].[SkillId]," + " [ED].[Expertise] " + "FROM [dbo].[EvaluationDetail] AS [ED] " + "INNER JOIN [dbo].[Evaluation] AS [E] ON [E].[Id] = [ED].[EvaluationId] " + "WHERE [E].[CandidateId] = @candidateId"; using (var conn = new SqlConnection(Config.GetConnectionString())) { using (var cmd = new SqlCommand(query, conn)) { cmd.Parameters.Add("@candidateId", SqlDbType.Int); cmd.Parameters["@candidateId"].Value = candidateId; conn.Open(); var dt = new DataTable(); var da = new SqlDataAdapter(cmd); da.Fill(dt); foreach (DataRow dr in dt.Rows) { var skillId = dr.Db2Int("SkillId"); var skill = skills.FirstOrDefault(du => du.Id == skillId); res.Add(getEvaluationDetailFromDataRow(dr, skill)); } } } res.Sort(); return(res); }
private List <EvaluationDetail> getEvaluationDetails() { var res = new List <EvaluationDetail>(); var skillRepository = new SkillRepository(); var skills = skillRepository.GetAllSkills(); var query = "SELECT [ED].[Id], " + " [ED].[EvaluationId]," + " [ED].[SkillId]," + " [ED].[Expertise] " + "FROM [dbo].[EvaluationDetail] AS [ED]"; using (var conn = new SqlConnection(Config.GetConnectionString())) { using (var cmd = new SqlCommand(query, conn)) { conn.Open(); var dt = new DataTable(); var da = new SqlDataAdapter(cmd); da.Fill(dt); foreach (DataRow dr in dt.Rows) { var skillId = dr.Db2Int("SkillId"); var skill = skills.FirstOrDefault(du => du.Id == skillId); res.Add(getEvaluationDetailFromDataRow(dr, skill)); } } } res.Sort(); return(res); }