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