public async Task <UserSD> CreateASD(UserSD usd)
{
var sql = @"
insert into UserHasSkills
(UserId, DisciplineId, SkillId)
values
((select Id from Users where Username = @Username),
(select Id from Disciplines where Disciplines.Name = @Discipline),
(select Id from Skills where Skills.Name = @Skill))
;
if not exists (select * FROM UserWorksDiscipline where
UserId = (select Id from Users where Username = @Username)
and DisciplineId = (select Id from Disciplines where Disciplines.Name = @Discipline))
insert into UserWorksDiscipline
(UserId, DisciplineId, Year)
values
((select Id from Users where Username = @Username),
(select Id from Disciplines where Name = @Discipline),
RTRIM(LTRIM(@Year)));
";
using var connection = new SqlConnection(connectionString);
connection.Open();
await connection.ExecuteAsync(sql, new
{
Username = usd.Username,
Discipline = usd.Discipline,
Skill = usd.Skill,
Year = usd.yoe
});
return(usd);
}
public async Task <ActionResult <IEnumerable <UserSD> > > UpdateASD([FromBody] UserSD usd)
{
var response = await sdRepository.UpdateASD(usd);
var viewModel = mapper.Map <IEnumerable <UserSD> >(response);
return(Accepted("GetASD", viewModel));
}
public async Task <ActionResult <UserSD> > CreateASD([FromBody] UserSD usd)
{
var response = await sdRepository.CreateASD(usd);
var viewModel = mapper.Map <UserSD>(response);
return(Created("GetASD", viewModel));
}
public async Task <IEnumerable <UserSD> > UpdateASD(UserSD usd)
{
var sql = @"
update UserHasSkills
set SkillId = (select Id from Skills where Name = @Skill),
DisciplineId = (select Id from Disciplines where Name = @Discipline)
where UserId = (select Id from Users where Username = @Username)
;
update UserWorksDiscipline
set DisciplineId = (select Id from Disciplines where Name = @Discipline),
Year = RTRIM(LTRIM(@Year))
where UserId = (select Id from Users where Username = @Username);";
using var connection = new SqlConnection(connectionString);
connection.Open();
await connection.ExecuteAsync(sql, new { Skill = usd.Skill, Discipline = usd.Discipline,
Username = usd.Username, Year = usd.yoe });
return(await GetASD(usd.Username));
}
