private List <Candidate> getCandidates(SqlConnection conn, SqlCommand cmd)
{
var res = new List <Candidate>();
var deliveryUnitsRepository = new DeliveryUnitRepository();
var deliveryUnits = deliveryUnitsRepository.GetDeliveryUnits();
var candidateRoleRepository = new CandidateRoleRepository();
var candidateRoles = candidateRoleRepository.GetCandidateRoles();
var projectRepository = new ProjectRepository();
var projects = projectRepository.GetProjects();
var candidateEvaluations = getCandidatesEvaluations();
conn.Open();
var dt = new DataTable();
var da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
var candidateId = dr.Db2Int("Id");
var evaluations = new List <Evaluation>();
if (candidateEvaluations.Any(ev => ev.CandidateId == candidateId))
{
evaluations = candidateEvaluations.Where(ev => ev.CandidateId == candidateId).ToList();
}
var deliveryUnit = deliveryUnits.FirstOrDefault(du => du.Id == dr.Db2Int("DeliveryUnitId"));
var candidateRole = candidateRoles.FirstOrDefault(du => du.Id == dr.Db2Int("CandidateRoleId"));
Project project = null;
var projectId = dr.Db2NullableInt("CurrentProjectId");
if (projectId.HasValue)
{
project = projects.FirstOrDefault(du => du.Id == projectId.Value);
}
res.Add(getCandidateFromDataRow(dr, deliveryUnit, candidateRole, project, evaluations));
}
res.Sort();
return(res);
}
public Candidate GetCandidateById(int id)
{
Candidate res = null;
var deliveryUnitsRepository = new DeliveryUnitRepository();
var deliveryUnits = deliveryUnitsRepository.GetDeliveryUnits();
var evaluations = getCandidatesEvaluationsByCandidateId(id);
var query = "SELECT [C].[Id], " +
" [C].[DeliveryUnitId]," +
" [C].[RelationType]," +
" [C].[FirstName]," +
" [C].[LastName]," +
" [C].[CandidateRoleId]," +
" [C].[DocType]," +
" [C].[DocNumber]," +
" [C].[EmployeeNumber]," +
" [C].[InBench]," +
" [C].[Picture]," +
" [C].[IsActive]," +
" [C].[Grade]," +
" [C].[CurrentProjectId]," +
" [C].[CurrentProjectJoin] " +
"FROM [dbo].[Candidate] AS [C] " +
"WHERE [C].[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 deliveryUnit = deliveryUnits.FirstOrDefault(du => du.Id == dr.Db2Int("DeliveryUnitId"));
var candidateRoleRepository = new CandidateRoleRepository();
var candidateRole = candidateRoleRepository.GetCandidateRoleById(dr.Db2Int("CandidateRoleId"));
Project project = null;
var projectId = dr.Db2NullableInt("CurrentProjectId");
if (projectId.HasValue)
{
var projectRepository = new ProjectRepository();
project = projectRepository.GetProjectById(projectId.Value);
}
res = getCandidateFromDataRow(dr, deliveryUnit, candidateRole, project, evaluations);
}
}
}
return(res);
}