//public static void EditSong(int song_id, int artist_id, string title, string year, string extra_info)
//{
// SqlConnection connection = Connection.GetConnection();
// SqlCommand cmd = new SqlCommand();
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Connection = connection;
// try
// {
// connection.Open();
// cmd.CommandText = "spEditSong";
// cmd.Parameters.AddWithValue("@song_id", song_id);
// cmd.Parameters.AddWithValue("@artist_id", artist_id);
// cmd.Parameters.AddWithValue("@title", title);
// cmd.Parameters.AddWithValue("@year", year);
// cmd.Parameters.AddWithValue("@extra_info", extra_info);
// }
// catch (SqlException ex)
// {
// throw ex;
// }
// finally
// {
// connection.Close();
// }
//}
public static bool EditSong(int song_id, int artist_id, string title, int year)
{
SqlConnection connection = Connection.GetConnection();
string updateStatement =
"UPDATE Song \r\nSET artiestid = @artist_id, titel = @title, jaar = @year\r\nWHERE songid = @song_id";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@song_id", song_id);
updateCommand.Parameters.AddWithValue(
"@artist_id", artist_id);
updateCommand.Parameters.AddWithValue(
"@title", title);
updateCommand.Parameters.AddWithValue(
"@year", year);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Lijst GetCustomer(int _id)
{
SqlConnection connection = Connection.GetConnection();
string selectStatement
= "SELECT id, Name, price "
+ "FROM Client "
+ "WHERE id = @id";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@id", _id);
try
{
connection.Open();
SqlDataReader custReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (custReader.Read())
{
Customer customer = new Customer();
customer.ID = custReader["id"].ToString();
customer.Name = custReader["Name"].ToString();
customer.Price = custReader["Price"].ToString();
return(customer);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
//public static void EditArtist(int artist_id, string artist_name, string extra_info)
//{
// SqlConnection connection = Connection.GetConnection();
// SqlCommand cmd = new SqlCommand();
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Connection = connection;
// try
// {
// connection.Open();
// cmd.CommandText = "spEditArtist";
// cmd.Parameters.AddWithValue("@artist_id", artist_id);
// cmd.Parameters.AddWithValue("@artiest_naam", artist_name);
// cmd.Parameters.AddWithValue("@extra_info", extra_info);
// }
// catch (SqlException ex)
// {
// throw ex;
// }
// finally
// {
// connection.Close();
// }
//}
public static bool EditArtist(int artist_id, string artist_name, string extra_info)
{
SqlConnection connection = Connection.GetConnection();
string updateStatement =
"UPDATE Artiest \r\nSET naam = @artist_naam, biografie = @extra_info\r\nWHERE artiestid = @artist_id";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@artist_id", artist_id);
updateCommand.Parameters.AddWithValue(
"@artist_naam", artist_name);
updateCommand.Parameters.AddWithValue(
"@extra_info", extra_info);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <Song> GetSongs()
{
List <Song> lijst = new List <Song>();
SqlConnection connection = Connection.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spAllSongs";
try
{
connection.Open();
SqlDataReader itemReader =
cmd.ExecuteReader(CommandBehavior.SingleResult);
while (itemReader.Read())
{
Song song = new Song();
song.Song_ID = itemReader.GetInt32(0);
song.Title = itemReader.GetString(1);
song.Artist_id = itemReader.GetInt32(2);
song.Artist_name = itemReader.GetString(3);
song.Year = itemReader.GetInt32(4);
lijst.Add(song);
}
return(lijst);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <Lijst> SelectListJaar(int jaar)
{
List <Lijst> lijst = new List <Lijst>();
SqlConnection connection = Connection.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spTop2000Jaar";
cmd.Parameters.AddWithValue("@jaar", jaar);
try
{
connection.Open();
SqlDataReader itemReader =
cmd.ExecuteReader(CommandBehavior.SingleResult);
while (itemReader.Read())
{
Lijst lijstitem = new Lijst();
lijstitem.Positie = itemReader.GetInt32(0);
lijstitem.Lied = itemReader.GetString(1);
lijstitem.Artiest = itemReader.GetString(2);
lijstitem.Jaar = itemReader.GetInt32(3);
lijst.Add(lijstitem);
}
return(lijst);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static void DeleteGenre(string s1)
{
SqlConnection connection = Connection.GetConnection();
try
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spDeleteGenre";
cmd.Parameters.AddWithValue("@Genre", s1);
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
