public bool InsertLike(AccountBijdrage accountBijdrage) { try { using (SqlConnection connection = Connection.SQLconnection) { string query = "insertLike"; using (SqlCommand command = new SqlCommand(query, connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@accountID", accountBijdrage.AccountID); command.Parameters.AddWithValue("@bijdrageID", accountBijdrage.BijdrageID); command.Parameters.AddWithValue("@like", accountBijdrage.Like); command.Parameters.AddWithValue("@ongewenst", accountBijdrage.Ongenwenst); command.ExecuteNonQuery(); } } return(true); } catch (Exception) { return(false); } }
public List <Bijdrage> GetAllReportedBijdrages() { List <Bijdrage> bijdrageList = new List <Bijdrage>(); using (SqlConnection connection = Connection.SQLconnection) { string query = "SELECT c.bijdrage_id, c.naam, b.*, be.*, br.*, a.*, ab.* FROM ACCOUNT_BIJDRAGE ab " + "LEFT JOIN BIJDRAGE b on ab.bijdrage_id = b.ID LEFT JOIN CATEGORIE c on b.ID = c.bijdrage_id " + "LEFT JOIN BESTAND be on b.ID = be.bijdrage_id LEFT JOIN BERICHT br on b.ID = br.bijdrage_id " + "LEFT JOIN ACCOUNT a on b.account_id = a.ID " + "WHERE ab.ongewenst >= 1"; using (SqlCommand command = new SqlCommand(query, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Bijdrage bijdrage = CreateBijdrageFromReader(reader, connection); bijdrageList.Add(bijdrage); } } } foreach (Bijdrage bijdrage in bijdrageList) { string query2 = "SELECT ab.* FROM ACCOUNT_BIJDRAGE ab LEFT JOIN bijdrage b ON ab.bijdrage_id = b.ID"; using (SqlCommand command = new SqlCommand(query2, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string ab = reader["id"].ToString(); string ab1 = reader["account_id"].ToString(); string ab2 = reader["bijdrage_id"].ToString(); string ab3 = reader["like"].ToString(); string ab4 = reader["ongewenst"].ToString(); if (bijdrage.Id == Convert.ToInt32(ab2)) { AccountBijdrage accountBijdrage1 = new AccountBijdrage(Convert.ToInt32(ab), Convert.ToInt32(ab1), Convert.ToInt32(ab2), Convert.ToInt32(ab3), Convert.ToInt32(ab4)); bijdrage.AccountBijdrage.Add(accountBijdrage1); } else { continue; } } } } } } return(bijdrageList); }
public List <Bijdrage> GetAllBijdragesByUserId(int userid) { List <Bijdrage> bijdrageList = new List <Bijdrage>(); using (SqlConnection connection = Connection.SQLconnection) { string query = "SELECT * FROM BIJDRAGE b " + "LEFT JOIN CATEGORIE c on b.ID = c.bijdrage_id " + "LEFT JOIN BESTAND be on b.ID = be.bijdrage_id " + "LEFT JOIN BERICHT br on b.ID = br.bijdrage_id " + "LEFT JOIN ACCOUNT acc on b.account_id = acc.ID " + "WHERE acc.ID = @userid"; using (SqlCommand command = new SqlCommand(query, connection)) { command.Parameters.AddWithValue("@userid", userid); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Bijdrage bijdrage = CreateBijdrageFromReader(reader, connection); bijdrageList.Add(bijdrage); } } } foreach (Bijdrage bijdrage in bijdrageList) { string query2 = "SELECT ab.* FROM ACCOUNT_BIJDRAGE ab LEFT JOIN bijdrage b ON ab.bijdrage_id = b.ID"; using (SqlCommand command = new SqlCommand(query2, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string ab = reader["id"].ToString(); string ab1 = reader["account_id"].ToString(); string ab2 = reader["bijdrage_id"].ToString(); string ab3 = reader["like"].ToString(); string ab4 = reader["ongewenst"].ToString(); if (bijdrage.Id == Convert.ToInt32(ab2)) { AccountBijdrage accountBijdrage1 = new AccountBijdrage(Convert.ToInt32(ab), Convert.ToInt32(ab1), Convert.ToInt32(ab2), Convert.ToInt32(ab3), Convert.ToInt32(ab4)); bijdrage.AccountBijdrage.Add(accountBijdrage1); } else { continue; } } } } } } return(bijdrageList); }
public List <Bericht> LoadBerichtenByPostId(int id) { List <Bericht> berichtenList = new List <Bericht>(); using (SqlConnection connection = Connection.SQLconnection) { string query = "select * from BERICHT b " + "left join BIJDRAGE_BERICHT bb on bb.bericht_id = b.bijdrage_id left join BIJDRAGE bd on b.bijdrage_id = bd.ID where bb.bijdrage_id = @id"; using (SqlCommand command = new SqlCommand(query, connection)) { command.Parameters.AddWithValue("@id", id); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Bericht bericht = CreateBerichtFromReader(reader); berichtenList.Add(bericht); } } } foreach (Bericht bericht in berichtenList) { string query2 = "SELECT ab.* FROM ACCOUNT_BIJDRAGE ab LEFT JOIN bijdrage b ON ab.bijdrage_id = b.ID"; using (SqlCommand command = new SqlCommand(query2, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string ab = reader["id"].ToString(); string ab1 = reader["account_id"].ToString(); string ab2 = reader["bijdrage_id"].ToString(); string ab3 = reader["like"].ToString(); string ab4 = reader["ongewenst"].ToString(); if (bericht.Id == Convert.ToInt32(ab2)) { AccountBijdrage accountBijdrage1 = new AccountBijdrage(Convert.ToInt32(ab), Convert.ToInt32(ab1), Convert.ToInt32(ab2), Convert.ToInt32(ab3), Convert.ToInt32(ab4)); bericht.AccountBijdrage.Add(accountBijdrage1); } else { continue; } } } } } } return(berichtenList); }
public bool InsertLike(AccountBijdrage accountBijdrage) { return(context.InsertLike(accountBijdrage)); }
private Bijdrage CreateBijdrageFromReader(SqlDataReader reader) { AccountRepository accRepo = new AccountRepository(new AccountSQLContext()); if (reader["soort"].ToString() == "bestand") { { Account account = accRepo.GetAccountById(Convert.ToInt32(reader["acccount_id"])); AccountBijdrage accBijdrage; try { accBijdrage = new AccountBijdrage(Convert.ToInt32(reader["account_id"]), Convert.ToInt32(reader["bijdrage_id"]), Convert.ToInt32(reader["like"]), Convert.ToInt32(reader["ongewenst"])); } catch (Exception e) { accBijdrage = new AccountBijdrage(0, 0, 0, 0, 0); } return(new Bestand( Convert.ToInt32(reader["ID"]), account, Convert.ToDateTime(reader["datum"]), Convert.ToString(reader["soort"]), Convert.ToInt32(reader["categorie_id"] != DBNull.Value ? Convert.ToInt32(reader["categorie_id"]) : 0), Convert.ToString(reader["bestandslocatie"] != DBNull.Value ? Convert.ToString(reader["bestandslocatie"]) : ""), Convert.ToInt32(reader["grootte"] != DBNull.Value ? Convert.ToInt32(reader["grootte"]) : 0), accBijdrage )); } } if (reader["soort"].ToString() == "categorie") { { Account account = accRepo.GetAccountById(Convert.ToInt32(reader["acccount_id"])); AccountBijdrage accBijdrage; try { accBijdrage = new AccountBijdrage(Convert.ToInt32(reader["account_id"]), Convert.ToInt32(reader["bijdrage_id"]), Convert.ToInt32(reader["like"]), Convert.ToInt32(reader["ongewenst"])); } catch (Exception e) { accBijdrage = new AccountBijdrage(0, 0, 0, 0, 0); } return(new Categorie( Convert.ToInt32(reader["ID"]), account, Convert.ToDateTime(reader["datum"]), Convert.ToString(reader["soort"]), Convert.ToInt32(reader["categorie_id"] != DBNull.Value ? Convert.ToInt32(reader["categorie_id"]) : 0), Convert.ToString(reader["naam"]), accBijdrage )); } } if (reader["soort"].ToString() == "bericht") { { Account account = accRepo.GetAccountById(Convert.ToInt32(reader["acccount_id"])); AccountBijdrage accBijdrage; try { accBijdrage = new AccountBijdrage(Convert.ToInt32(reader["account_id"]), Convert.ToInt32(reader["bijdrage_id"]), Convert.ToInt32(reader["like"]), Convert.ToInt32(reader["ongewenst"])); } catch (Exception e) { accBijdrage = new AccountBijdrage(0, 0, 0, 0, 0); } return(new Bericht( Convert.ToInt32(reader["ID"]), account, accBijdrage, Convert.ToDateTime(reader["datum"]), Convert.ToString(reader["soort"]), Convert.ToString(reader["titel"]), Convert.ToString(reader["inhoud"]) )); } } else { return(null); } }