public Track[] GetTrackSearchResults(Query query)
{
Track[] tracks = null;
if (query != null && string.IsNullOrEmpty(query.SearchPhrase) == false)
{
query.PageSize = query.PageSize == 0 ? 1 : query.PageSize;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT al.titelid AS AlbumId, a.interpretid AS ArtistId, a.interpret AS ArtistName, al.titel AS AlbumName, al.Guid, t.liedid as TrackId, t.lied AS Track");
stringBuilder.Append(" FROM titel al");
stringBuilder.Append(" JOIN interpreten a ON al.interpretid = a.interpretid");
stringBuilder.Append(" JOIN lieder t ON al.titelid = t.titelid AND t.liedpfad IS NOT NULL");
stringBuilder.Append(" WHERE MATCH (t.lied) AGAINST (?querystring IN BOOLEAN MODE)");
stringBuilder.Append(" ORDER BY t.lied, a.interpret ,al.titel");
stringBuilder.Append(" LIMIT ?limit OFFSET ?offset");
using (TunesEntities tunesEntity = new TunesEntities(this.ConnectionString))
{
if (tunesEntity != null)
{
MySqlParameter paramQueryString = new MySqlParameter("querystring", MySqlDbType.VarChar, 60);
paramQueryString.Direction = ParameterDirection.Input;
paramQueryString.Value = query.SearchPhrase;
MySqlParameter paramLimit = new MySqlParameter("limit", MySqlDbType.Int32, 0);
paramLimit.Direction = ParameterDirection.Input;
paramLimit.Value = query.PageSize;
MySqlParameter paramOffset = new MySqlParameter("offset", MySqlDbType.Int32, 0);
paramOffset.Direction = ParameterDirection.Input;
paramOffset.Value = query.PageIndex;
List<Track> trackCollection = null;
using (System.Data.Objects.ObjectContext objectContext = tunesEntity.ObjectContext())
{
var results = objectContext.ExecuteStoreQuery<SearchResult>(stringBuilder.ToString(), paramQueryString, paramLimit, paramOffset);
if (results != null)
{
if (trackCollection == null)
{
trackCollection = new List<Track>();
}
foreach (var result in results)
{
if (result != null)
{
trackCollection.Add(new Track
{
Id = result.TrackId,
Name = result.Track,
Album = new Album
{
Id = result.AlbumId,
Title = result.AlbumName,
AlbumId = string.IsNullOrEmpty(result.Guid) ? Guid.Empty : new Guid(result.Guid),
Artist = new Artist
{
Id = result.ArtistId,
Name = result.ArtistName
}
}
});
}
}
}
}
if (trackCollection != null)
{
tracks = trackCollection.ToArray();
}
}
}
}
return tracks;
}
public BSE.Tunes.Data.SearchResult GetSearchResults(Query query)
{
BSE.Tunes.Data.SearchResult searchResult = new Data.SearchResult();
if (query!= null && string.IsNullOrEmpty(query.SearchPhrase) == false)
{
searchResult.Albums = this.GetAlbumSearchResults(query);
searchResult.Tracks = this.GetTrackSearchResults(query);
}
return searchResult;
}
public void OnNavigatedTo(object navigationParameter, NavigationMode navigationMode)
{
string queryString = navigationParameter as string;
if (string.IsNullOrEmpty(queryString) == false)
{
this.PageTitle = string.Format(CultureInfo.CurrentCulture,
this.m_resourceService.GetString("IDS_SearchAlbumsPage_PageTitle", "Results for {0}"), queryString);
int maximumItems = 100;
int pageIndex = 0;
this.Items = new IncrementalObservableCollection<DataItemViewModel>(
(uint)maximumItems,
(uint count) =>
{
Func<Task<Windows.UI.Xaml.Data.LoadMoreItemsResult>> taskFunc = async () =>
{
int pageSize = (int)count;
Query query = new Query
{
SearchPhrase = queryString,
PageIndex = pageIndex,
PageSize = pageSize
};
ObservableCollection<Album> albumsResults = await this.m_dataService.GetAlbumSearchResults(query);
if (albumsResults != null)
{
foreach (var album in albumsResults)
{
this.Items.Add(new DataItemViewModel
{
Title = album.Title,
Subtitle = album.Artist.Name,
Image = album.Thumbnail,
Data = album
});
}
pageIndex += pageSize;
}
return new Windows.UI.Xaml.Data.LoadMoreItemsResult()
{
Count = (uint)count
};
};
Task<Windows.UI.Xaml.Data.LoadMoreItemsResult> loadMoreItemsTask = taskFunc();
return loadMoreItemsTask.AsAsyncOperation<Windows.UI.Xaml.Data.LoadMoreItemsResult>();
}
);
}
}
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;
}
public async void OnNavigatedTo(object navigationParameter, NavigationMode navigationMode)
{
string queryString = navigationParameter as string;
if (string.IsNullOrEmpty(queryString) == false)
{
this.PageTitle = string.Format(CultureInfo.CurrentCulture,
this.m_resourceService.GetString("IDS_SearchPane_PageTitle", "Results for {0}"), queryString);
try
{
Query query = new Query
{
SearchPhrase = queryString,
PageSize = 9
};
var searchResult = await this.m_dataService.GetSearchResults(query);
if (searchResult != null)
{
Album[] albums = searchResult.Albums;
if (albums != null && albums.Count() > 0)
{
DataGroupViewModel albumGroup = new DataGroupViewModel
{
Title = this.m_resourceService.GetString("IDS_SearchPane_GroupTitleAlbums", "Albums"),
Data = new SearchCategory
{
Mode = DataModel.FilterMode.Albbums,
Query = queryString
}
};
foreach (var album in albums)
{
if (album != null)
{
DataItemViewModel dataItem = new DataItemViewModel();
dataItem.Title = album.Title;
dataItem.ImageSource = this.m_dataService.GetImage(album.AlbumId, true);
dataItem.Subtitle = album.Artist.Name;
dataItem.Data = album;
albumGroup.Items.Add(dataItem);
}
}
this.SearchResultGroups.Add(albumGroup);
}
Track[] tracks = searchResult.Tracks;
if (tracks != null && tracks.Count() > 0)
{
DataGroupViewModel trackGroup = new DataGroupViewModel
{
Title = this.m_resourceService.GetString("IDS_SearchPane_GroupTitleTracks", "Tracks"),
Data = new SearchCategory
{
Mode = DataModel.FilterMode.Tracks,
Query = queryString
}
};
foreach (var track in tracks)
{
if (track != null)
{
DataItemViewModel dataItem = new DataItemViewModel();
dataItem.Title = track.Name;
dataItem.ImageSource = this.m_dataService.GetImage(track.Album.AlbumId, true);
dataItem.Subtitle = track.Album.Artist.Name;
dataItem.Data = track;
trackGroup.Items.Add(dataItem);
}
}
this.SearchResultGroups.Add(trackGroup);
}
}
}
catch (AggregateException aggregateException)
{
string errorMessage = string.Empty;
foreach (var innerException in aggregateException.Flatten().InnerExceptions)
{
if (innerException != null && !string.IsNullOrEmpty(innerException.Message))
{
errorMessage += innerException.Message + Environment.NewLine;
}
}
if (!string.IsNullOrEmpty(errorMessage))
{
this.m_dialogservice.ShowDialog(errorMessage);
}
}
}
}