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;
}