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);
}
