public static List <ClinkerInterests> GetAllClinkerInterests()
{
_clinkerInterests = new List <ClinkerInterests>();
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var getAllClinkerInterestsCommand = connection.CreateCommand();
getAllClinkerInterestsCommand.CommandText = @"select ci.Id, ci.ClinkerId, ci.InterestId
from clinkerInterests ci";
var reader = getAllClinkerInterestsCommand.ExecuteReader();
while (reader.Read())
{
var id = (int)reader["Id"];
var clinkerId = (int)reader["ClinkerId"];
var interestId = (int)reader["InterestId"];
var newClinkerInterest = new ClinkerInterests()
{
Id = id, ClinkerId = clinkerId, InterestId = interestId
};
_clinkerInterests.Add(newClinkerInterest);
}
}
return(_clinkerInterests);
}
public IEnumerable <ClinkerInterests> GetAllInterestsByClinkerId(int clinkerId)
{
var clinkerSql = @"SELECT * FROM Clinker WHERE Id = @ClinkerId";
var interestsSql = @"SELECT * FROM Interests";
using (var db = new SqlConnection(ConnectionString))
{
var parameters = new { ClinkerId = clinkerId };
var clinkers = db.Query <Clinker>(clinkerSql, parameters);
var clinkerInterests = db.Query <Interests>(interestsSql);
var clinkersWithInterests = new List <ClinkerInterests>();
foreach (var clinker in clinkers)
{
var clinkerWithInterests = new ClinkerInterests
{
ClinkerId = clinker.Id,
Name = $"{clinker.FirstName} {clinker.LastName}",
Interests = clinkerInterests.Where(il => il.ClinkerId == clinker.Id).Select(il => il.Name)
};
clinkersWithInterests.Add(clinkerWithInterests);
}
return(clinkersWithInterests);
}
}
public static ClinkerInterests AddClinkerInterest(ClinkerInterests clinkerInterestObject)
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var insertClinkerInterestCommand = connection.CreateCommand();
insertClinkerInterestCommand.CommandText = @"Insert into clinkerinterests (interestId, clinkerId)
Output inserted.*
Values(@interestId, @clinkerId)";
insertClinkerInterestCommand.Parameters.AddWithValue("interestId", clinkerInterestObject.InterestId);
insertClinkerInterestCommand.Parameters.AddWithValue("clinkerId", clinkerInterestObject.ClinkerId);
var reader = insertClinkerInterestCommand.ExecuteReader();
if (reader.Read())
{
var InsertedId = (int)reader["Id"];
var InsertedInterestId = (int)reader["InterestId"];
var InsertedClinkerId = (int)reader["ClinkerId"];
var newClinkerInterest = new ClinkerInterests()
{
Id = InsertedId, InterestId = InsertedInterestId, ClinkerId = InsertedClinkerId
};
return(newClinkerInterest);
}
}
throw new Exception("No clinker interest found");
}
public static ClinkerInterests GetClinkerInterestById(int clinkerInterestId)
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var getClinkerInterestCommand = connection.CreateCommand();
getClinkerInterestCommand.CommandText = @"select ci.Id, ci.ClinkerId, ci.InterestId
from ClinkerInterests ci
where ci.Id = @clinkerInterestId";
getClinkerInterestCommand.Parameters.AddWithValue("clinkerInterestId", clinkerInterestId);
var reader = getClinkerInterestCommand.ExecuteReader();
while (reader.Read())
{
var id = (int)reader["Id"];
var interestId = (int)reader["InterestId"];
var clinkerId = (int)reader["ClinkerId"];
var newClinkerInterest = new ClinkerInterests()
{
Id = id, ClinkerId = clinkerId, InterestId = interestId
};
return(newClinkerInterest);
}
throw new Exception("Interest not found");
}
}
public static ClinkerInterests UpdateClinkerInterest(ClinkerInterests newClinkerInterest)
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var updateClinkerInterestCommand = connection.CreateCommand();
updateClinkerInterestCommand.CommandText = @"Update ClinkerInterests
set clinkerId = @clinkerId, interestId = @interestId
where id = @clinkerInterestId";
updateClinkerInterestCommand.Parameters.AddWithValue("interestId", newClinkerInterest.InterestId);
updateClinkerInterestCommand.Parameters.AddWithValue("clinkerId", newClinkerInterest.ClinkerId);
updateClinkerInterestCommand.Parameters.AddWithValue("clinkerInterestId", newClinkerInterest.Id);
updateClinkerInterestCommand.ExecuteNonQuery();
var updatedClinkerInterestObject = GetClinkerInterestById(newClinkerInterest.Id);
return(updatedClinkerInterestObject);
}
throw new Exception("No clinker interest found");
}
public ActionResult updateClinkerInterest(ClinkerInterests newClinkerInterest)
{
var updatedClinkerInterestObject = InterestRepository.UpdateClinkerInterest(newClinkerInterest);
return(Created("api/updatedClinkerInterest", updatedClinkerInterestObject));
}
public ActionResult CreateClinkerInterest(ClinkerInterests clinkerInterestObject)
{
var newClinkerInterest = InterestRepository.AddClinkerInterest(clinkerInterestObject);
return(Created($"api/createdInterest/{newClinkerInterest.Id}", newClinkerInterest));
}
