public CD GetById(int id) { OpenSqlConnection(); var cd = new CD(); const string query = @"Select Id, Artist, Title, Genre, Year from CD Where Id = @Id"; var command = new SqlCommand(query, SqlConnection); command.Parameters.AddWithValue("@Id", id); var dataReader = command.ExecuteReader(); try { dataReader.Read(); cd.Artist = dataReader["Artist"].ToString(); cd.Id = (int)dataReader["Id"]; cd.Genre = dataReader["Genre"].ToString(); cd.Title = dataReader["Title"].ToString(); cd.Year = (int)dataReader["Year"]; cd.Tracks = cd.Tracks = _trackRepository.GetListOfTracksByCd(id); } finally { dataReader.Close(); } return cd; }
private int InsertCD(CD cd) { const string query = "Insert Into CD(Artist, Title, Genre, Year) Values(@Artist, @Title, @Genre, @Year); SELECT CAST(scope_identity() AS int)"; var command = new SqlCommand(query, SqlConnection); command.Parameters.AddWithValue("@Artist", cd.Artist); command.Parameters.AddWithValue("@Title", cd.Title); command.Parameters.AddWithValue("@Genre", cd.Genre); command.Parameters.AddWithValue("@Year", cd.Year); return (int)command.ExecuteScalar(); }
private void UpdateCD(CD cd) { const string query = "Update CD Set Artist = @Artist, Title = @Title, Genre = @Genre, Year = @Year Where Id = @Id"; var command = new SqlCommand(query, SqlConnection); command.Parameters.AddWithValue("@Id", cd.Id); command.Parameters.AddWithValue("@Artist", cd.Artist); command.Parameters.AddWithValue("@Title", cd.Title); command.Parameters.AddWithValue("@Genre", cd.Genre); command.Parameters.AddWithValue("@Year", cd.Year); command.ExecuteNonQuery(); }
public int Save(CD cd) { OpenSqlConnection(); int id; if (cd.Id == 0) { id = InsertCD(cd); if (cd.Tracks != null) { foreach (var track in cd.Tracks) { _trackRepository.Save(track); } } } else { UpdateCD(cd); if (cd.Tracks != null) { foreach (var track in cd.Tracks) { _trackRepository.Save(track); } } id = cd.Id; } return id; }