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