public int AddCandidateSkill(int candidateId, int newSkillId)
{
var result = -1;
databaseHelper.CheckConnectionState();
// Record already exists, nothing to add.
if (IsCandidateSkillExists(candidateId, newSkillId))
{
result = 1; // Success
return(result);
}
using (var cmd = new SQLiteCommand(databaseHelper.Connection))
{
cmd.CommandText = @"INSERT INTO CandidateSkill(CandidateId, SkillID)
VALUES (@CandidateId, @SkillID)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@CandidateId", candidateId);
cmd.Parameters.AddWithValue("@SkillId", newSkillId);
try
{
result = cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
Console.WriteLine(e.ToString());
}
}
return(result);
}
public ObservableCollection <Candidate> AllCandidates()
{
try
{
dbHelper.CheckConnectionState();
using (var cmd = new SQLiteCommand(dbHelper.Connection))
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"SELECT id, FirstName, LastName, EnteredDate FROM Candidate";
var adapter = new SQLiteDataAdapter(cmd);
var dataCandidate = new DataTable();
adapter.Fill(dataCandidate);
cmd.CommandText = @"SELECT CandidateId, Skill.Id AS 'SkillId', name AS 'SkillName'
FROM CandidateSkill inner join Skill
ON Skill.Id = CandidateSkill.SkillID";
var adapter2 = new SQLiteDataAdapter(cmd);
var dataSkills = new DataTable();
adapter2.Fill(dataSkills);
cmd.CommandText = @"SELECT Candidate.Id AS 'CandidateID', Skill.Id AS 'SkillId', name AS 'SkillName'
FROM Candidate LEFT JOIN Skill
WHERE (Candidate.Id, Skill.Id) NOT IN (SELECT CandidateId as 'ID', Skill.Id
FROM CandidateSkill INNER JOIN Skill ON Skill.Id = CandidateSkill.SkillID)";
var adapter3 = new SQLiteDataAdapter(cmd);
var dataNoSkills = new DataTable();
adapter3.Fill(dataNoSkills);
var myCollection = (from DataRow row in dataCandidate.Rows
select new Candidate
{
Id = Convert.ToInt32(row["Id"]),
FirstName = row["FirstName"].ToString(),
LastName = row["LastName"].ToString(),
EnteredDate = Convert.ToDateTime(row["EnteredDate"])
}).ToList();
foreach (DataRow row in dataSkills.Rows)
{
var tmpId = Convert.ToInt32(row["CandidateId"]);
var tmpIndex = myCollection.FindIndex(x => x.Id == tmpId);
if (tmpIndex >= 0)
{
myCollection[tmpIndex].SkillList.Add(new Skill()
{
Id = Convert.ToInt32(row["SkillId"]),
Name = row["SkillName"].ToString()
});
}
}
foreach (DataRow row in dataNoSkills.Rows)
{
var tmpId = Convert.ToInt32(row["CandidateId"]);
var tmpIndex = myCollection.FindIndex(x => x.Id == tmpId);
if (tmpIndex >= 0)
{
myCollection[tmpIndex].NoSkillList.Add(new Skill()
{
Id = Convert.ToInt32(row["SkillId"]),
Name = row["SkillName"].ToString()
});
}
}
var candidatesList = new ObservableCollection <Candidate>(myCollection);
return(candidatesList);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(null);
}
}