public List <Nummer> ViewNummerOpAlbum(int albumid)
{
SqlConnection con = new SqlConnection(Connectionstring);
string query = "SELECT NummerID FROM Nummeropalbum WHERE AlbumID = @albumid";
List <int> nummerid = new List <int>();
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@albumid", albumid);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
nummerid.Add(rdr.GetInt32(0));
}
con.Close();
var model = new List <Nummer>();
foreach (int i in nummerid)
{
con.Open();
string querynummer = "SELECT * FROM Nummer WHERE NummerID = @nummerid";
SqlCommand cmd2 = new SqlCommand(querynummer, con);
cmd2.Parameters.AddWithValue("@nummerid", i);
SqlDataReader rdr2 = cmd2.ExecuteReader();
while (rdr2.Read())
{
model.Add(Lijsten.GetNummerData(rdr2));
}
con.Close();
}
return(model);
}
public List <Playlist> ViewVolgend(int gebruikerid)
{
string sql = "SELECT PlaylistID FROM Volgersperlijst WHERE GebruikerID = @id";
List <int> Playlistid = new List <int>();
SqlConnection con = new SqlConnection(Connectionstring);
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
cmd.Parameters.AddWithValue("@id", gebruikerid);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Playlistid.Add(rdr.GetInt32(0));
}
var model = new List <Playlist>();
con.Close();
foreach (int i in Playlistid)
{
string sql2 = "SELECT * FROM Playlist WHERE PlaylistID = @playlistid";
SqlCommand cmd2 = new SqlCommand(sql2, con);
con.Open();
cmd2.Parameters.AddWithValue("@playlistid", i);
SqlDataReader rdr2 = cmd2.ExecuteReader();
while (rdr2.Read())
{
model.Add(Lijsten.GetPlaylistData(rdr2));
}
con.Close();
}
return(model);
}
public List <Artiest> AllArtiest()
{
string sql = "SELECT * FROM Artiest";
SqlConnection con = new SqlConnection(Connectionstring);
var model = new List <Artiest>();
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
model.Add(Lijsten.GetArtiestData(rdr));
}
return(model);
}
public List <Album> AllAlbums()
{
List <Album> model = new List <Album>();
SqlConnection con = new SqlConnection(Connectionstring);
con.Open();
string query = "SELECT * FROM Album";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
model.Add(Lijsten.GetAlbumData(rdr));
}
con.Close();
return(model);
}
public void GetByID(int id, Gebruiker g)
{
SqlConnection con = new SqlConnection(Connectionstring);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT GebruikerID, Username, Email FROM [Gebruiker] WHERE GebruikerID = @id", con);
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Lijsten.GetUserData(rdr);
}
}
con.Close();
}
public List <Nummer> ViewNummer()
{
string sql = "SELECT * FROM Nummer";
var model = new List <Nummer>();
using (SqlConnection con = new SqlConnection(Connectionstring))
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
model.Add(Lijsten.GetNummerData(rdr));
}
con.Close();
}
return(model);
}
public List <Playlist> ViewOpenbaarPlaylist()
{
string sql = "SELECT * FROM Playlist WHERE Openbaar = 1";
var model = new List <Playlist>();
using (SqlConnection con = new SqlConnection(Connectionstring))
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
model.Add(Lijsten.GetPlaylistData(rdr));
}
con.Close();
}
return(model);
}
public List <Nummer> SearchNummer(string searchinput)
{
string query = "SELECT * FROM Nummer WHERE Naam = @naam";
SqlConnection con = new SqlConnection(Connectionstring);
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@naam", searchinput);
cmd.ExecuteScalar();
var model = new List <Nummer>();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
model.Add(Lijsten.GetNummerData(rdr));
}
con.Close();
return(model);
}
public List <Playlist> ViewMyPlayList(int id)
{
string sql = "SELECT * FROM Playlist WHERE (GemaaktdoorID = @gebruikersid)";
var model = new List <Playlist>();
SqlConnection con = new SqlConnection(Connectionstring);
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@gebruikersid", id);
cmd.ExecuteNonQuery();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
model.Add(Lijsten.GetPlaylistData(rdr));
}
con.Close();
return(model);
}
public List <Nummer> ViewNummersInLijst(int playlistid)
{
DeleteNull();
string sql = "SELECT NummerID FROM Nummerperlijst WHERE PlaylistID = @playlistid";
List <int> nummerid = new List <int>();
SqlConnection con = new SqlConnection(Connectionstring);
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
cmd.Parameters.AddWithValue("@playlistid", playlistid);
cmd.ExecuteNonQuery();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
nummerid.Add(rdr.GetInt32(0));
}
con.Close();
var model = new List <Nummer>();
foreach (int i in nummerid)
{
con.Open();
string sql2 = "SELECT * FROM Nummer WHERE NummerID = @nummerid";
SqlCommand cmd2 = new SqlCommand(sql2, con);
cmd2.Parameters.AddWithValue("@nummerid", i);
cmd2.ExecuteNonQuery();
SqlDataReader rdr2 = cmd2.ExecuteReader();
while (rdr2.Read())
{
model.Add(Lijsten.GetNummerData(rdr2));
}
con.Close();
}
return(model);
}
