public List <Evaluation> GetEvaluationsByCandidateId(int candidateId) { var res = new List <Evaluation>(); var evaluationTypeRepository = new EvaluationTypeRepository(); var evaluationTypes = evaluationTypeRepository.GetEvaluationTypes(); var evaluationDetails = getEvaluationDetailsByCandidateId(candidateId); var query = "SELECT [E].[Id], " + " [E].[CandidateId]," + " [E].[EvaluationKey]," + " [E].[EvaluationTypeId]," + " [E].[Date]," + " [E].[Notes] " + "FROM [dbo].[Evaluation] AS [E] " + "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 evaluationId = dr.Db2Int("Id"); var evaluationTypeId = dr.Db2Int("EvaluationTypeId"); var evaluationType = evaluationTypes.FirstOrDefault(du => du.Id == evaluationTypeId); var details = evaluationDetails.Where(ed => ed.EvaluationId == evaluationId).ToList(); res.Add(getEvaluationFromDataRow(dr, evaluationType, details)); } } } res.Sort(); return(res); }
public Evaluation GetEvaluationById(int id) { Evaluation res = null; var evaluationTypeRepository = new EvaluationTypeRepository(); var evaluationDetails = getEvaluationDetailsByEvaluationId(id); var query = "SELECT [E].[Id], " + " [E].[CandidateId]," + " [E].[EvaluationKey]," + " [E].[EvaluationTypeId]," + " [E].[Date]," + " [E].[Notes] " + "FROM [dbo].[Evaluation] AS [E] " + "WHERE [E].[Id] = @id"; using (var conn = new SqlConnection(Config.GetConnectionString())) { using (var cmd = new SqlCommand(query, conn)) { cmd.Parameters.Add("@id", SqlDbType.Int); cmd.Parameters["@id"].Value = id; conn.Open(); var dt = new DataTable(); var da = new SqlDataAdapter(cmd); da.Fill(dt); if (dt.Rows.Count == 1) { DataRow dr = dt.Rows[0]; var evaluationTypeId = dr.Db2Int("EvaluationTypeId"); var evaluationType = evaluationTypeRepository.GetEvaluationTypeById(evaluationTypeId); res = getEvaluationFromDataRow(dr, evaluationType, evaluationDetails); } } } return(res); }