public Album GetAlbumById(int albumId) { Album album = null; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("SELECT i.InterpretID, i.Interpret, i.Interpret_Lang ,a.TitelID, a.Titel1, a.Guid as AlbumId, a.ErschDatum, g.genreid, g.genre1"); stringBuilder.Append(" FROM tunesEntities.titel AS a"); stringBuilder.Append(" INNER JOIN tunesEntities.interpreten AS i ON a.InterpretID = i.InterpretID"); stringBuilder.Append(" LEFT JOIN tunesEntities.genre AS g ON a.genreid = g.genreid"); stringBuilder.Append(" WHERE a.titelid = @albumId"); string sql = stringBuilder.ToString(); using (System.Data.EntityClient.EntityConnection entityConnection = new System.Data.EntityClient.EntityConnection(this.ConnectionString)) { try { entityConnection.Open(); using (EntityCommand entityCommand = entityConnection.CreateCommand()) { EntityParameter id = new EntityParameter(); id.ParameterName = "albumid"; id.Value = albumId; entityCommand.Parameters.Add(id); entityCommand.CommandText = sql; using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) { if (dataReader.Read()) { album = new Album { Artist = new Artist { Id = dataReader.GetInt32("InterpretID", false, 0), Name = dataReader.GetString("Interpret", false, string.Empty), SortName = dataReader.GetString("Interpret_Lang", true, string.Empty) }, Id = dataReader.GetInt32("TitelID", false, 0), Title = dataReader.GetString("Titel1", false, string.Empty), AlbumId = dataReader.GetGuid("AlbumId", false, Guid.Empty), Year = dataReader.GetInt32("ErschDatum", true, 0), Genre = new Genre { Id = dataReader.GetInt32("genreid", true, 0), Name = dataReader.GetString("genre1", true, string.Empty) } }; } } GetAlbumTracksByTitelId(album, entityConnection); } } finally { entityConnection.Close(); } } return album; }
public Album[] GetNewestAlbums(int limit) { Album[] albums = null; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("SELECT i.InterpretID, i.Interpret, i.Interpret_Lang ,a.TitelID, a.Titel1, a.Guid as AlbumId FROM tunesEntities.titel AS a"); stringBuilder.Append(" INNER JOIN tunesEntities.interpreten AS i ON a.InterpretID = i.InterpretID"); stringBuilder.Append(" ORDER BY a.TitelID DESC"); stringBuilder.Append(" LIMIT @limit "); string sql = stringBuilder.ToString(); using (System.Data.EntityClient.EntityConnection entityConnection = new System.Data.EntityClient.EntityConnection(this.ConnectionString)) { try { entityConnection.Open(); using (EntityCommand entityCommand = entityConnection.CreateCommand()) { EntityParameter limitParam = new EntityParameter(); limitParam.ParameterName = "limit"; limitParam.Value = limit; entityCommand.Parameters.Add(limitParam); List<Album> albumCollection = null; entityCommand.CommandText = sql; // Execute the command. using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) { // Start reading results. while (dataReader.Read()) { if (albumCollection == null) { albumCollection = new List<Album>(); } Album album = new Album { Artist = new Artist { Id = dataReader.GetInt32("InterpretID", false, 0), Name = dataReader.GetString("Interpret", false, string.Empty), SortName = dataReader.GetString("Interpret_Lang", true, string.Empty) }, Id = dataReader.GetInt32("TitelID", false, 0), Title = dataReader.GetString("Titel1", false, string.Empty), AlbumId = dataReader.GetGuid("AlbumId", false, Guid.Empty) }; albumCollection.Add(album); } } if (albumCollection != null) { albums = albumCollection.ToArray(); } } } finally { entityConnection.Close(); } } return albums; }
public Album[] GetAlbums(Query query) { Album[] albums = null; if (query == null) { query = new Query { PageIndex = 0, PageSize = 1 }; } query.PageSize = query.PageSize == 0 ? 1 : query.PageSize; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("SELECT i.InterpretID, i.Interpret, i.Interpret_Lang ,a.TitelID, a.Titel1, a.Guid as AlbumId FROM tunesEntities.titel AS a"); stringBuilder.Append(" INNER JOIN tunesEntities.interpreten AS i ON a.InterpretID = i.InterpretID"); stringBuilder.Append(" INNER JOIN tunesEntities.lieder AS t ON a.TitelID = t.TitelID"); stringBuilder.Append(" WHERE t.Liedpfad IS NOT NULL"); stringBuilder.Append(" GROUP BY i.InterpretID, i.Interpret, i.Interpret_Lang ,a.TitelID, a.Titel1, a.Guid"); if (query.SortByCondition != null && query.SortByCondition.Id == 1) { stringBuilder.Append(" ORDER BY a.Titel1"); } else { stringBuilder.Append(" ORDER BY i.Interpret, a.Titel1"); } stringBuilder.Append(" SKIP @skip LIMIT @limit "); string sql = stringBuilder.ToString(); using (System.Data.EntityClient.EntityConnection entityConnection = new System.Data.EntityClient.EntityConnection(this.ConnectionString)) { try { entityConnection.Open(); using (EntityCommand entityCommand = entityConnection.CreateCommand()) { EntityParameter skip = new EntityParameter(); skip.ParameterName = "skip"; skip.Value = query.PageIndex; entityCommand.Parameters.Add(skip); EntityParameter limit = new EntityParameter(); limit.ParameterName = "limit"; limit.Value = query.PageSize; entityCommand.Parameters.Add(limit); List<Album> albumCollection = null; entityCommand.CommandText = sql; // Execute the command. using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) { // Start reading results. while (dataReader.Read()) { if (albumCollection == null) { albumCollection = new List<Album>(); } Album album = new Album { Artist = new Artist { Id = dataReader.GetInt32("InterpretID", false, 0), Name = dataReader.GetString("Interpret", false, string.Empty), SortName = dataReader.GetString("Interpret_Lang", true, string.Empty) }, Id = dataReader.GetInt32("TitelID", false, 0), Title = dataReader.GetString("Titel1", false, string.Empty), AlbumId = dataReader.GetGuid("AlbumId", false, Guid.Empty) }; albumCollection.Add(album); } } if (albumCollection != null) { albums = albumCollection.ToArray(); } } } finally { entityConnection.Close(); } } return albums; }
private void GetAlbumTracksByTitelId(Album album, System.Data.EntityClient.EntityConnection entityConnection) { if (album != null) { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("SELECT t.LiedID, t.Track, t.Lied ,t.Dauer"); stringBuilder.Append(" FROM tunesEntities.lieder AS t"); stringBuilder.Append(" WHERE t.titelid = @albumId"); stringBuilder.Append(" AND t.Liedpfad IS NOT NULL"); stringBuilder.Append(" ORDER BY t.Track"); string sql = stringBuilder.ToString(); using (EntityCommand entityCommand = entityConnection.CreateCommand()) { EntityParameter id = new EntityParameter(); id.ParameterName = "albumid"; id.Value = album.Id; entityCommand.Parameters.Add(id); entityCommand.CommandText = sql; List<Track> tracks = null; using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) { while (dataReader.Read()) { if (tracks == null) { tracks = new List<Track>(); } Track track = new Track { Id = dataReader.GetInt32("LiedID", false, 0), TrackNumber = dataReader.GetInt32("Track", false, 0), Name = dataReader.GetString("Lied", false, string.Empty), Duration = dataReader.GetTimeSpan("Dauer", true, TimeSpan.MinValue) }; tracks.Add(track); } if (tracks != null) { album.Tracks = tracks.ToArray(); } } } } }
public AlbumViewModel(Album album) { this.Album = album; }
public AlbumViewModel(IDataService dataService, Album album) { this.m_dataService = dataService; this.Album = album; }