Click here to Skip to main content
Click here to Skip to main content
 
Add your own
alternative version

BSEtunes

, 24 Apr 2010
BSEtunes is a MySQL based, full manageable, networkable single or multiuser jukebox application
BSE.Platten.zip
BSE.Platten
BSE.Platten.vssscc
mssccprj.scc
vssver2.scc
BSE.CoverFlow.WPFLib
BSE.CoverFlow.WPFLib.csproj.user
BSE.CoverFlow.WPFLib.csproj.vspscc
mssccprj.scc
vssver2.scc
Images
close.png
exit.png
next.png
next_disabled.png
Note.jpg
pause.png
PlayButton.png
previous.png
previous_disabled.png
stop.png
vssver2.scc
Properties
Settings.settings
vssver2.scc
Resources
vssver2.scc
BSE.Platten.Admin
BSE.Platten.Admin.csproj.vspscc
mssccprj.scc
vssver2.scc
Properties
Settings.settings
vssver2.scc
Resources
Album16.gif
BSEadmin.ico
cdimport.png
cdrip.png
cd_hoeren.png
clearsearch.png
DataContainer_MoveFirsHS.png
DataContainer_MoveLastHS.png
DataContainer_MoveNextHS.png
DataContainer_MovePreviousHS.png
DataContainer_NewRecordHS.png
diskinfo.png
DRIVEDSC.png
DRIVENET.png
Edit_UndoHS.png
exportimage.png
findfile.png
FindHS.png
freedb.png
Genre16.png
Image.png
InsertPictureHS.png
Interpret16.png
OptionsHS.png
PieChart3DHS.png
PieChartHS.png
ProtectFormHS.png
RefreshDocViewHS.png
saveHS.png
search.png
song.png
system.png
tagger.png
tools_16.png
vssver2.scc
BSE.Platten.Admin.WinApp
BSE.Platten.Admin.WinApp.csproj.user
BSE.Platten.Admin.WinApp.csproj.vspscc
mssccprj.scc
vssver2.scc
Properties
Settings.settings
vssver2.scc
Resources
BSEadmin.ico
vssver2.scc
BSE.Platten.Audio
bse.ico
BSE.Platten.Audio.csproj.vspscc
mssccprj.scc
vssver2.scc
Options
vssver2.scc
Player
vssver2.scc
Properties
vssver2.scc
Resources
FillDownHS.png
FillLeftHS.png
Folder256.png
FolderFind.png
FolderOpen256.png
GoLtrHS.png
MoveNext.png
MovePrevious.png
OptionsHS.png
PauseHS.png
PlayHS.png
Slider.gif
Start.png
StopHS.png
vssver2.scc
WinControls
vssver2.scc
WMFSDK
vssver2.scc
BSE.Platten.BO
BSE.Platten.BO.csproj.vspscc
mssccprj.scc
vssver2.scc
Properties
vssver2.scc
BSE.Platten.Common
BSE.Platten.Common.csproj.vspscc
mssccprj.scc
vssver2.scc
Properties
vssver2.scc
Resources
BSEsplash.png
DRIVEDSC.png
DRIVENET.png
Network.png
splash.png
vssver2.scc
BSE.Platten.Covers
BSE.Platten.Covers.csproj.vspscc
mssccprj.scc
vssver2.scc
Properties
Settings.settings
vssver2.scc
BSE.Platten.FreeDb
BSE.Platten.FreeDb.csproj.vspscc
mssccprj.scc
vssver2.scc
Properties
vssver2.scc
Resources
OpenCD.png
vssver2.scc
BSE.Platten.Ripper
BSE.Platten.Ripper.csproj.vspscc
mssccprj.scc
vssver2.scc
Properties
vssver2.scc
Resources
OpenCD.png
OptionsHS.png
vssver2.scc
BSE.Platten.Statistik
BSE.Platten.Statistik.csproj.vspscc
mssccprj.scc
vssver2.scc
Properties
vssver2.scc
BSE.Platten.Tunes
BSE.Platten.Tunes.csproj.user
BSE.Platten.Tunes.csproj.vspscc
mssccprj.scc
vssver2.scc
Filters
vssver2.scc
Properties
Settings.settings
vssver2.scc
Resources
About.png
Album16.gif
AudioCD.png
AudioFile.png
AutoList.png
broadcast.png
BSE.png
BSEAbout.png
BSEsplash.png
BSEtunes.ico
BSE_16.png
cd_hoeren.png
coverflow.png
DataContainer_MoveNextHS.png
delete.gif
diskinfo.png
Filter2HS.png
FindHS.png
Folder256.png
FolderOpen256.png
Genre16.gif
Interpret16.gif
keinBild.gif
NewPlaylist16.gif
OptionsHS.png
PauseHS.png
RefreshDocViewHS.png
Removabledrive.png
saveHS.png
search.png
shuffle.png
Shuffle_icon.png
song.png
splash1.png
StopHS.png
TaskHS.png
TrackInfo.gif
vssver2.scc
wiedergabe16.gif
BSEadminSetup
BSEadminSetup.vdproj
BSEadminSetup.vdproj.vspscc
mssccprj.scc
vssver2.scc
Debug
Release
BSEtunesSetup
BSEtunesSetup.vdproj
BSEtunesSetup.vdproj.vspscc
mssccprj.scc
vssver2.scc
Debug
Release
dll
BSE.CDDrives.DLL
BSE.Charts.DLL
BSE.Configuration.DLL
BSE.RemovableDrives.DLL
BSE.Shell.DLL
BSE.Windows.Forms.DLL
Lame
lame_enc.dll
vssver2.scc
lame-3.97
BSE.Platten_en.zip
BSE.CoverFlow.WPFLib.csproj.user
close.png
exit.png
next.png
next_disabled.png
Note.jpg
pause.png
PlayButton.png
previous.png
previous_disabled.png
stop.png
vssver2.scc
Settings.settings
vssver2.scc
vssver2.scc
BSE.Platten.Admin.csproj.user
Settings.settings
vssver2.scc
Album16.gif
BSEadmin.ico
cdimport.png
cdrip.png
cd_hoeren.png
clearsearch.png
DataContainer_MoveFirsHS.png
DataContainer_MoveLastHS.png
DataContainer_MoveNextHS.png
DataContainer_MovePreviousHS.png
DataContainer_NewRecordHS.png
diskinfo.png
DRIVEDSC.png
DRIVENET.png
Edit_UndoHS.png
exportimage.png
findfile.png
FindHS.png
freedb.png
Genre16.png
Image.png
InsertPictureHS.png
Interpret16.png
OptionsHS.png
PieChart3DHS.png
PieChartHS.png
ProtectFormHS.png
RefreshDocViewHS.png
saveHS.png
search.png
song.png
system.png
tagger.png
tools_16.png
vssver2.scc
BSE.Platten.Admin.WinApp.csproj.user
Settings.settings
vssver2.scc
BSEadmin.ico
vssver2.scc
bse.ico
BSE.Platten.Audio.csproj.user
vssver2.scc
vssver2.scc
vssver2.scc
FillDownHS.png
FillLeftHS.png
Folder256.png
FolderFind.png
FolderOpen256.png
GoLtrHS.png
MoveNext.png
MovePrevious.png
OptionsHS.png
PauseHS.png
PlayHS.png
Slider.gif
Start.png
StopHS.png
vssver2.scc
vssver2.scc
vssver2.scc
BSE.Platten.BO.csproj.user
vssver2.scc
BSE.Platten.Common.csproj.user
vssver2.scc
BSEsplash.png
DRIVEDSC.png
DRIVENET.png
Network.png
splash.png
vssver2.scc
BSE.Platten.Covers.csproj.user
Settings.settings
vssver2.scc
BSE.Platten.FreeDb.csproj.user
vssver2.scc
OpenCD.png
vssver2.scc
AudioWriter
vssver2.scc
BSE.Platten.Ripper.csproj.user
Lame
vssver2.scc
vssver2.scc
OpenCD.png
OptionsHS.png
vssver2.scc
BSE.Platten.Statistik.csproj.user
vssver2.scc
BSE.Platten.Tunes.csproj.user
vssver2.scc
Settings.settings
vssver2.scc
About.png
Album16.gif
AudioCD.png
AudioFile.png
AutoList.png
broadcast.png
BSE.png
BSEAbout.png
BSEsplash.png
BSEtunes.ico
BSE_16.png
cd_hoeren.png
coverflow.png
DataContainer_MoveNextHS.png
delete.gif
diskinfo.png
Filter2HS.png
FindHS.png
Folder256.png
FolderOpen256.png
Genre16.gif
Interpret16.gif
keinBild.gif
NewPlaylist16.gif
OptionsHS.png
PauseHS.png
RefreshDocViewHS.png
Removabledrive.png
saveHS.png
search.png
shuffle.png
Shuffle_icon.png
song.png
splash1.png
StopHS.png
TaskHS.png
TrackInfo.gif
vssver2.scc
wiedergabe16.gif
BSEadminSetup.vdproj
BSEtunesSetup.vdproj
BSE.CDDrives.DLL
BSE.Charts.DLL
BSE.Configuration.DLL
BSE.RemovableDrives.DLL
BSE.Shell.DLL
BSE.ThreadedShell.DLL
BSE.Windows.Forms.DLL
lame_enc.dll
MySQL.chm.zip
MySqlCreateDataBase5x.sql.zip
using System;
using System.Data;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Globalization;
using BSE.Platten.BO.Properties;

namespace BSE.Platten.BO
{
    public class CTunesModel : ModelSql
    {
        #region MethodsPublic
        #region Albums

        public static CAlbum GetAlbumById(string strConnection, int iTitelId)
        {
            CAlbum album = null;

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                album = GetAlbumDetailByTitelId(iTitelId, mySqlConnection);
                album = GetAlbumTracksByTitelId(album, mySqlConnection);
            }
            return album;
        }

        public static CTrack GetTrackWithThumbNailById(string strConnection, CTrack track)
        {
            if (track == null)
            {
                throw new ArgumentNullException(
                    string.Format(
                    CultureInfo.CurrentUICulture,
                    Resources.IDS_ArgumentException,
                    "track"));
            }
            
            string strSelectSQL = "SELECT t.thumbnail FROM titel t" +
                " WHERE t.titelid = ?TitelId";

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSQL, mySqlConnection))
                {
                    MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("TitelId", MySqlDbType.Int32, 0));
                    mySqlParameter.Direction = ParameterDirection.Input;
                    mySqlParameter.SourceColumn = "titelid";
                    mySqlParameter.Value = track.TitelId;

                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        if (mySqlDataReader.Read())
                        {
                            track.ThumbNail = CCoverData.GetImageFromDbReader(mySqlDataReader, "thumbnail");
                        }
                    }
                }
            }
            return track;
        }

        #endregion

        #region Trees

        public static CInterpretData[] GetInterpretsAndAlbums(string strConnection)
        {
            string strDataRelationName = "dataRelationInterpretAlbums";
            CInterpretData[] interprets = null;

            DataSet dataSetInterpretsAndAlbums = GetInterpretsAndAlbumsAsDataSet(strDataRelationName, strConnection);
            int iInterpretsCount = dataSetInterpretsAndAlbums.Tables["Interpret"].Rows.Count;
            interprets = new BSE.Platten.BO.CInterpretData[iInterpretsCount];

            for (int i = 0; i < iInterpretsCount; i++)
            {
                DataRow dataRowInterprets = dataSetInterpretsAndAlbums.Tables["Interpret"].Rows[i];
                interprets[i] = new CInterpretData();
                interprets[i].InterpretId = (int)dataRowInterprets["interpretid"];
                interprets[i].Interpret = dataRowInterprets["interpret"].ToString();

                DataRow[] dataRowsAlbums = dataRowInterprets.GetChildRows(strDataRelationName);
                int iAlbumsCount = dataRowsAlbums.Length;
                if (iAlbumsCount > 0)
                {
                    CAlbum[] albums = new CAlbum[iAlbumsCount];
                    for (int j = 0; j < iAlbumsCount; j++)
                    {
                        DataRow dataRowAlbums = dataRowsAlbums[j];
                        albums[j] = new CAlbum();
                        albums[j].AlbumId = (int)dataRowAlbums["titelid"];
                        albums[j].Title = dataRowAlbums["titel"].ToString();
                        albums[j].InterpretId = (int)dataRowAlbums["interpretid"];
                    }
                    interprets[i].Albums = albums;
                }
            }
            return interprets;
        }

        public static BSE.Platten.BO.CGenreData[] GetGenresWithAlbums(string strConnection)
        {
            string strDataRelationName = "dataRelationGenreAlbums";
            BSE.Platten.BO.CGenreData[] genre = null;

            DataSet dataSetGenresWithAlbums = GetGenresWithAlbumsAsDataSet(strDataRelationName, strConnection);
            int iGenresCount = dataSetGenresWithAlbums.Tables["Genre"].Rows.Count;
            genre = new BSE.Platten.BO.CGenreData[iGenresCount];

            for (int i = 0; i < iGenresCount; i++)
            {
                DataRow dataRowGenre = dataSetGenresWithAlbums.Tables["Genre"].Rows[i];
                genre[i] = new CGenreData();
                genre[i].GenreId = Convert.ToInt32(dataRowGenre["genreid"],CultureInfo.InvariantCulture);
                genre[i].Genre = dataRowGenre["genre"].ToString();

                DataRow[] dataRowsAlbums = dataRowGenre.GetChildRows(strDataRelationName);
                int iAlbumsCount = dataRowsAlbums.Length;
                if (iAlbumsCount > 0)
                {
                    CAlbum[] albums = new CAlbum[iAlbumsCount];
                    for (int j = 0; j < iAlbumsCount; j++)
                    {
                        DataRow dataRowAlbums = dataRowsAlbums[j];
                        albums[j] = new CAlbum();
                        albums[j].AlbumId = (int)dataRowAlbums["titelid"];
                        albums[j].Interpret = dataRowAlbums["interpret"].ToString();
                        albums[j].Title = dataRowAlbums["titel"].ToString();
                    }
                    genre[i].Albums = albums;
                }
            }
            return genre;
        }

        public static BSE.Platten.BO.CYearData[] GetYearsWithAlbums(string strConnection)
        {
            string strDataRelationName = "dataRelationYearAlbums";
            BSE.Platten.BO.CYearData[] years = null;

            DataSet dataSetYearsWithAlbums = GetYearsWithAlbumsAsDataSet(strDataRelationName, strConnection);
            int iYearsCount = dataSetYearsWithAlbums.Tables["Year"].Rows.Count;
            years = new BSE.Platten.BO.CYearData[iYearsCount];

            for (int i = 0; i < iYearsCount; i++)
            {
                DataRow dataRowYear = dataSetYearsWithAlbums.Tables["Year"].Rows[i];
                years[i] = new CYearData();
                years[i].Year = Convert.ToInt32(dataRowYear["erschdatum"],CultureInfo.InvariantCulture);

                DataRow[] dataRowsAlbums = dataRowYear.GetChildRows(strDataRelationName);
                int iAlbumsCount = dataRowsAlbums.Length;
                if (iAlbumsCount > 0)
                {
                    CAlbum[] albums = new CAlbum[iAlbumsCount];
                    for (int j = 0; j < iAlbumsCount; j++)
                    {
                        DataRow dataRowAlbums = dataRowsAlbums[j];
                        albums[j] = new CAlbum();
                        albums[j].AlbumId = (int)dataRowAlbums["titelid"];
                        albums[j].Interpret = dataRowAlbums["interpret"].ToString();
                        albums[j].Title = dataRowAlbums["titel"].ToString();
                    }
                    years[i].Albums = albums;
                }
            }
            return years;
        }

        #endregion

        #region Favorites

        public static CFavorite[] GetFavoritesByFavoritId(int iLimit, int iFavoriteId, string strBenutzer, string strConnection)
        {
            CFavorite[] favorites = null;
            ArrayList aFavorites = new ArrayList();
            string strSelectSql = string.Empty;
            switch (iFavoriteId)
            {
                case 1:
                    //Lieder
                    strSelectSql = "SELECT Count(*) AS anzahl,h.titelid,h.liedid,i.interpret,t.titel,l.lied" +
                        " FROM history h" +
                        " JOIN titel t ON h.titelid = t.titelid" +
                        " JOIN interpreten i ON t.interpretid = i.interpretid" +
                        " LEFT JOIN lieder l ON h.liedid = l.liedid" +
                        " WHERE h.appid = ?AppId" +
                        " AND h.benutzer = ?Benutzer" +
                        " GROUP BY h.titelid,h.liedid" +
                        " ORDER BY anzahl desc" +
                        " LIMIT " + iLimit;
                    break;
                case 2:
                    //Alben
                    strSelectSql = "SELECT Count(*) AS anzahl,h.titelid,i.interpret,t.titel,l.lied" +
                        " FROM history h" +
                        " JOIN titel t ON h.titelid = t.titelid" +
                        " JOIN interpreten i ON t.interpretid = i.interpretid" +
                        " LEFT JOIN lieder l ON h.liedid = l.liedid" +
                        " WHERE h.appid = ?AppId" +
                        " AND h.benutzer = ?Benutzer" +
                        " GROUP BY h.titelid" +
                        " ORDER BY anzahl desc" +
                        " LIMIT " + iLimit;
                    break;
            }

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSql, mySqlConnection))
                {
                    MySqlParameter mySqlParameterPlayerApp = mySqlCommand.Parameters.Add(new MySqlParameter("AppId", MySqlDbType.Int32, 0));
                    mySqlParameterPlayerApp.Direction = ParameterDirection.Input;
                    mySqlParameterPlayerApp.SourceColumn = "AppID";
                    mySqlParameterPlayerApp.Value = iFavoriteId;

                    MySqlParameter mySqlParameterUser = mySqlCommand.Parameters.Add(new MySqlParameter("Benutzer", MySqlDbType.VarChar, 0));
                    mySqlParameterUser.Direction = ParameterDirection.Input;
                    mySqlParameterUser.SourceColumn = "Benutzer";
                    mySqlParameterUser.Value = strBenutzer;

                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        while (mySqlDataReader.Read())
                        {
                            CFavorite favorite = new CFavorite();
                            //MySQL returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement.
                            //The result is a BIGINT value. 
                            favorite.Count = Convert.ToInt32(GetInt64(mySqlDataReader, "Anzahl", true, favorite.Count));
                            favorite.TitelId = GetInt32(mySqlDataReader, "TitelId", false, favorite.TitelId);
                            favorite.Interpret = GetString(mySqlDataReader, "Interpret", false, favorite.Interpret);
                            favorite.Album = GetString(mySqlDataReader, "Titel", false, favorite.Album);
                            favorite.Title = GetString(mySqlDataReader, "Lied", true, favorite.Title);
                            aFavorites.Add(favorite);
                        }
                    }
                    favorites = new CFavorite[aFavorites.Count];
                    aFavorites.CopyTo(favorites);
                }
            }
            return favorites;
        }

        #endregion

        #region FullTextSearch

        public static SortableCollection<CTrack> GetFullTextSearchCollection(string strSearchstring, string strConnection)
        {
            SortableCollection<CTrack> trackCollection = null;
            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                trackCollection = new SortableCollection<CTrack>();
                trackCollection = GetFullTextSearchForTitleAndInterpretsCollection(strSearchstring, trackCollection, mySqlConnection);
                trackCollection = GetFullTextSearchForTitlesCollection(strSearchstring, trackCollection, mySqlConnection);
            }
            return trackCollection;
        }

        #endregion

        #region PlayList

        public static BSE.Platten.BO.CTrack[] GetAlbumTracksForPlayListByTitelId(string strConnection, int iTitelId)
        {
            BSE.Platten.BO.CTrack[] tracks = null;
            ArrayList albumTracks = new ArrayList();
            string strSelectSQL = "Select i.interpret,t.titelid,t.titel,t.erschdatum," +
                " g.genre,l.liedid,l.titelid,l.track,l.lied," +
                " l.dauer,l.liedpfad FROM lieder l" +
                " JOIN titel t ON l.titelid = t.titelid AND t.titelid = ?TitelID" +
                " JOIN interpreten i ON t.interpretid = i.interpretid" +
                " LEFT JOIN genre g ON t.genreid = g.genreid" +
                " WHERE l.liedpfad IS NOT NULL" +
                " ORDER BY l.track";

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSQL, mySqlConnection))
                {
                    MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("TitelID", MySqlDbType.Int32, 0));
                    mySqlParameter.Direction = ParameterDirection.Input;
                    mySqlParameter.SourceColumn = "TitelID";
                    mySqlParameter.Value = iTitelId;

                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        while (mySqlDataReader.Read())
                        {
                            BSE.Platten.BO.CTrack track = new BSE.Platten.BO.CTrack();

                            track.TitelId = GetInt32(mySqlDataReader, "titelid", false, track.TitelId);
                            track.Interpret = GetString(mySqlDataReader, "interpret", false, track.Interpret);
                            track.Album = GetString(mySqlDataReader, "titel", false, track.Album);
                            track.Genre = GetString(mySqlDataReader, "genre", true, track.Genre);
                            track.Year = GetInt32(mySqlDataReader, "erschdatum", true, track.Year);
                            track.LiedId = GetInt32(mySqlDataReader, "liedid", false, track.LiedId);
                            track.TrackNumber = GetInt32(mySqlDataReader, "track", false, track.TrackNumber);
                            track.Title = GetString(mySqlDataReader, "lied", false, track.Title);
                            track.Duration = GetDateTime(mySqlDataReader, "dauer", false, track.Duration);
                            track.FileName = GetString(mySqlDataReader, "liedpfad", false, track.FileName);
                            track.FileFullName = GetString(mySqlDataReader, "liedpfad", false, track.FileName);

                            albumTracks.Add(track);
                        }
                    }
                    tracks = new BSE.Platten.BO.CTrack[albumTracks.Count];
                    albumTracks.CopyTo(tracks);
                }
            }
            return tracks;
        }

        public static BSE.Platten.BO.CTrack GetTrackForPlayListByLiedId(string strConnection, int iLiedId)
        {
            BSE.Platten.BO.CTrack track = null;
            string strSelectSQL = "Select i.interpret,t.titelid,t.titel,t.erschdatum," +
                " g.genre,l.liedid,l.titelid,l.track,l.lied," +
                " l.dauer,l.liedpfad FROM lieder l" +
                " JOIN titel t ON l.titelid = t.titelid" +
                " JOIN interpreten i ON t.interpretid = i.interpretid" +
                " LEFT JOIN genre g ON t.genreid = g.genreid" +
                " WHERE l.liedpfad IS NOT NULL" +
                " AND l.liedid = ?LiedId";

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSQL, mySqlConnection))
                {
                    MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("LiedId", MySqlDbType.Int32, 0));
                    mySqlParameter.Direction = ParameterDirection.Input;
                    mySqlParameter.SourceColumn = "LiedId";
                    mySqlParameter.Value = iLiedId;

                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        if (mySqlDataReader.Read())
                        {
                            track = new CTrack();
                            track.TitelId = GetInt32(mySqlDataReader, "titelid", false, track.TitelId);
                            track.Interpret = GetString(mySqlDataReader, "interpret", false, track.Interpret);
                            track.Album = GetString(mySqlDataReader, "titel", false, track.Album);
                            track.Genre = GetString(mySqlDataReader, "genre", true, track.Genre);
                            track.Year = GetInt32(mySqlDataReader, "erschdatum", true, track.Year);
                            track.LiedId = GetInt32(mySqlDataReader, "liedid", false, track.LiedId);
                            track.TrackNumber = GetInt32(mySqlDataReader, "track", false, track.TrackNumber);
                            track.Title = GetString(mySqlDataReader, "lied", false, track.Title);
                            track.Duration = GetDateTime(mySqlDataReader, "dauer", false, track.Duration);
                            track.FileName = GetString(mySqlDataReader, "liedpfad", false, track.FileName);
                            track.FileFullName = GetString(mySqlDataReader, "liedpfad", false, track.FileName);
                        }
                    }
                }
            }
            return track;
        }

        public static CPlaylist[] GetPlaylistsByUserName(string strConnection, string userName)
        {
            CPlaylist[] playLists = null;
            string strSqlPlaylists = "SELECT listid,listname FROM playlist" +
                " WHERE user = ?User" +
                " ORDER BY listname";
            ArrayList aPlayLists = new ArrayList();

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlCommand mySqlCommand = new MySqlCommand(strSqlPlaylists, mySqlConnection))
                {
                    MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("User", MySqlDbType.VarChar, 50));
                    mySqlParameter.Direction = ParameterDirection.Input;
                    mySqlParameter.SourceColumn = "User";
                    mySqlParameter.Value = userName;

                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        while (mySqlDataReader.Read())
                        {
                            BSE.Platten.BO.CPlaylist playList = new BSE.Platten.BO.CPlaylist();

                            playList.Id = GetInt32(mySqlDataReader, "ListId", false, playList.Id);
                            playList.Name = GetString(mySqlDataReader, "ListName", false, playList.Name);

                            aPlayLists.Add(playList);
                        }
                    }
                    playLists = new CPlaylist[aPlayLists.Count];
                    aPlayLists.CopyTo(playLists);
                }
            }
            return playLists;
        }

        public static CPlaylist InsertPlaylist(string strConnection, CPlaylist playList)
        {
            if (playList == null)
            {
                throw new ArgumentNullException(
                    string.Format(
                    CultureInfo.CurrentUICulture,
                    Resources.IDS_ArgumentException,
                    "playList"));
            }
            
            string strInsertPlaylistSQL = "INSERT INTO playlist " +
                " (listid,listname,user,Guid)" +
                " VALUES(" +
                " 0,?ListName,?User,?Guid)";

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlCommand mySqlCommand = new MySqlCommand(strInsertPlaylistSQL, mySqlConnection))
                {
                    MySqlParameter mySqlParameterName = mySqlCommand.Parameters.Add(new MySqlParameter("ListName", MySqlDbType.VarChar, 100));
                    mySqlParameterName.Direction = ParameterDirection.Input;
                    mySqlParameterName.SourceColumn = "ListName";
                    mySqlParameterName.Value = playList.Name;

                    MySqlParameter mySqlParameterUser = mySqlCommand.Parameters.Add(new MySqlParameter("User", MySqlDbType.VarChar, 50));
                    mySqlParameterUser.Direction = ParameterDirection.Input;
                    mySqlParameterUser.SourceColumn = "User";
                    mySqlParameterUser.Value = playList.User;

                    MySqlParameter mySqlParameterGuid = mySqlCommand.Parameters.Add(new MySqlParameter("Guid", MySqlDbType.VarChar, 36));
                    mySqlParameterGuid.Direction = ParameterDirection.Input;
                    mySqlParameterGuid.SourceColumn = "guid";
                    mySqlParameterGuid.Value = playList.Guid.ToString();

                    if (mySqlCommand.ExecuteNonQuery() > 0)
                    {
                        playList = GetPlayListByGuid(playList, mySqlConnection);
                    }
                }
            }
            return playList;
        }

        public static void SavePlayList(string strConnection, CPlaylist playList)
        {
            if (playList == null)
            {
                throw new ArgumentNullException(
                    string.Format(
                    CultureInfo.CurrentUICulture,
                    Resources.IDS_ArgumentException,
                    "playList"));
            }
            
            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();
                using (MySqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction())
                {
                    try
                    {
                        if (DeletePlayListEntries(playList.Id, mySqlConnection))
                        {
                            InsertPlayListEntries(playList, mySqlConnection);
                            mySqlTransaction.Commit();
                        }
                    }
                    catch (MySqlException)
                    {
                        mySqlTransaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public static void DeletePlayList(string strConnection, int iPlayListId)
        {
            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();
                try
                {
                    using (MySqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction())
                    {
                        try
                        {
                            if (DeletePlayListEntries(iPlayListId, mySqlConnection))
                            {
                                DeletePlayList(iPlayListId, mySqlConnection);
                                mySqlTransaction.Commit();
                            }
                        }
                        catch (MySqlException)
                        {
                            mySqlTransaction.Rollback();
                            throw;
                        }
                    }
                }
                catch (MySqlException)
                {
                    throw;
                }
            }
        }

        public static CPlaylist GetPlayListByPlayListId(string strConnection, int iPlayListId)
        {
            CPlaylist playList = null;
            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                playList = GetPlayListByPlayListId(iPlayListId, mySqlConnection);
                if (playList != null)
                {
                    playList = GetPlayListEntriesByPlayListId(playList, mySqlConnection);
                }
            }
            return playList;
        }

        #endregion

        #region Filters

        public static CFilter[] GetFilterByFilterMode(string strConnection, FilterSettings.FilterMode filterMode)
        {
            CFilter[] filters = null;

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                switch (filterMode)
                {
                    case FilterSettings.FilterMode.Genre:
                        filters = GetFilterByFilterModeGenre(mySqlConnection);
                        break;
                    case FilterSettings.FilterMode.Year:
                        filters = GetFilterByFilterModeYear(mySqlConnection);
                        break;
                }
            }
            return filters;
        }

        public static CTrackCollection GetTracksByFilterSettings(string strConnection, FilterSettings filterSettings)
        {
            if (filterSettings == null)
            {
                throw new ArgumentNullException(
                    string.Format(
                    CultureInfo.CurrentUICulture,
                    Resources.IDS_ArgumentException,
                    "filterSettings"));
            }
            
            string strSelectSql = string.Empty;
            CTrackCollection trackCollection = new CTrackCollection();
            switch (filterSettings.UsedFilterMode)
            {
                case FilterSettings.FilterMode.None:
                    strSelectSql = "SELECT l.liedid,t.titelid,i.interpret,t.titel,l.lied, l.liedpfad" +
                        " FROM lieder l" +
                        " JOIN titel t ON l.titelid = t.titelid" +
                        " JOIN interpreten i ON t.interpretid = i.interpretid" +
                        " WHERE l.liedpfad IS NOT NULL" +
                        " ORDER BY l.liedid";
                    break;
                case FilterSettings.FilterMode.Year:
                    strSelectSql = "SELECT l.liedId,t.titelid,i.interpret,t.titel,l.lied, l.liedpfad" +
                        " FROM lieder l" +
                        " JOIN titel t ON l.titelid = t.titelid" +
                        " JOIN interpreten i ON t.interpretid = i.interpretid" +
                        " WHERE t.erschdatum IN (" + filterSettings.Value + ")" +
                        " AND l.liedpfad IS NOT NULL";
                    break;
                case FilterSettings.FilterMode.Genre:
                    strSelectSql = "SELECT l.liedId,t.titelid,i.interpret,t.titel,l.lied, l.liedpfad" +
                        " FROM lieder l" +
                        " JOIN titel t ON l.titelid = t.titelid" +
                        " JOIN interpreten i ON t.interpretid = i.interpretid" +
                        " WHERE t.genreid IN (" + filterSettings.Value + ")" +
                        " AND l.liedpfad IS NOT NULL";
                    break;
            }

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSql, mySqlConnection))
                {
                    using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                    {
                        while (mySqlDataReader.Read())
                        {
                            CTrack track = new CTrack();

                            track.LiedId = GetInt32(mySqlDataReader,"liedId",false,track.LiedId);
                            track.TitelId = GetInt32(mySqlDataReader, "titelid", false, track.TitelId);
                            track.Interpret = GetString(mySqlDataReader, "interpret", false, track.Interpret);
                            track.Album = GetString(mySqlDataReader, "titel", false, track.Album);
                            track.Title = GetString(mySqlDataReader, "lied", false, track.Title);
                            track.FileName = GetString(mySqlDataReader, "liedpfad", false, track.FileName);

                            trackCollection.Add(track);
                        }
                    }
                }
            }
            return trackCollection;
        }

        public static FilterSettings GetFilterSettings(string strConnection, FilterSettings.FilterMode filterMode, string strBenutzer)
        {
            FilterSettings filterSettings = null;

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                filterSettings = GetFilterSettings(mySqlConnection, filterMode, strBenutzer);
                if (filterSettings == null)
                {
                    InsertFilterSettings(mySqlConnection, filterMode, strBenutzer);
                }
            }
            return filterSettings;
        }

        public static void SaveFilterSettings(string strConnection, FilterSettings filterSettings)
        {
            if (filterSettings == null)
            {
                throw new ArgumentNullException(
                    string.Format(
                    CultureInfo.CurrentUICulture,
                    Resources.IDS_ArgumentException,
                    "filterSettings"));
            }
            
            string strUpdateSql = "UPDATE filtersettings" +
                " SET value = ?Value," +
                " isused = ?IsUsed" +
                " WHERE mode = ?Mode" +
                " AND benutzer = ?Benutzer";

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlCommand mySqlCommand = new MySqlCommand(strUpdateSql, mySqlConnection))
                {
                    MySqlParameter mySqlParameterFilterMode = mySqlCommand.Parameters.Add(new MySqlParameter("Mode", MySqlDbType.Int32));
                    mySqlParameterFilterMode.Direction = ParameterDirection.Input;
                    mySqlParameterFilterMode.SourceColumn = "mode";
                    mySqlParameterFilterMode.Value = filterSettings.UsedFilterMode;

                    MySqlParameter mySqlParameterValue = mySqlCommand.Parameters.Add(new MySqlParameter("Value", MySqlDbType.VarChar, 255));
                    mySqlParameterValue.Direction = ParameterDirection.Input;
                    mySqlParameterValue.SourceColumn = "value";
                    mySqlParameterValue.Value = filterSettings.Value;

                    MySqlParameter mySqlParameterUsed = mySqlCommand.Parameters.Add(new MySqlParameter("IsUsed", MySqlDbType.Bit));
                    mySqlParameterUsed.Direction = ParameterDirection.Input;
                    mySqlParameterUsed.SourceColumn = "isused";
                    mySqlParameterUsed.Value = filterSettings.IsUsed;

                    MySqlParameter mySqlParameterBenutzer = mySqlCommand.Parameters.Add(new MySqlParameter("Benutzer", MySqlDbType.VarChar, 50));
                    mySqlParameterBenutzer.Direction = ParameterDirection.Input;
                    mySqlParameterBenutzer.SourceColumn = "benutzer";
                    mySqlParameterBenutzer.Value = filterSettings.Benutzer;

                    mySqlCommand.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #region History

        public static SortableCollection<CTrack> GetHistoryTrackCollection(string strConnection, string strUser)
        {
            SortableCollection<CTrack> trackCollection = new SortableCollection<CTrack>();

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                trackCollection = GetHistoryTrackCollection(mySqlConnection, trackCollection, strUser);
            }
            return trackCollection;
        }

        public static SortableCollection<CTrack> GetHistoryTrackCollection(string strConnection, HistoryData historyData)
        {
            SortableCollection<CTrack> trackCollection = new SortableCollection<CTrack>();
            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                if (InsertHistoryData(mySqlConnection, historyData))
                {
                    trackCollection = GetHistoryTrackCollection(mySqlConnection, trackCollection, historyData.UserName);
                }
            }
            return trackCollection;
        }

        public static void DeleteTracksFromHistory(string strConnection, string strUser)
        {
            int iAppId = 0; //Radio
            int iCountHistoryTracks = 0;
            int iPlayListLimit = 40;

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                int iLastId = SelectLastIdFromHistory(mySqlConnection, strUser, iPlayListLimit, out iCountHistoryTracks);
                if (iLastId > 0 && iCountHistoryTracks == iPlayListLimit)
                {
                    DeleteTracksFromHistory(mySqlConnection, strUser, iAppId, iLastId); //Radio
                    DeleteTracksFromHistory(mySqlConnection, strUser);//Lieder,Cds
                }
            }
        }

        #endregion

        #endregion

        #region MethodsPrivate

        #region Albums

        private static CAlbum GetAlbumDetailByTitelId(int iTitelId, MySqlConnection mySqlConnection)
        {
            CAlbum album = null;
            string strSelectSQL = "SELECT t.titelid, i.interpret, t.titel," +
                " g.genre, t.cover, t.erschdatum, t.erstelldatum, m.beschreibung" +
                " FROM titel t" +
                " JOIN interpreten i ON t.interpretid = i.interpretid" +
                " LEFT JOIN genre g ON t.genreid = g.genreid" +
                " LEFT JOIN medium m ON t.mediumid = m.mediumid" +
                " WHERE t.titelid = ?TitelID";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSQL, mySqlConnection))
            {
                MySqlParameter mySqlParamter = mySqlCommand.Parameters.Add(new MySqlParameter("TitelID", MySqlDbType.Int32, 0));
                mySqlParamter.Direction = ParameterDirection.Input;
                mySqlParamter.SourceColumn = "TitelID";
                mySqlParamter.Value = iTitelId;
                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    if (mySqlDataReader.Read())
                    {
                        album = new CAlbum();

                        album.AlbumId = GetInt32(mySqlDataReader, "titelid", false, album.AlbumId);
                        album.Interpret = GetString(mySqlDataReader, "interpret", false, album.Interpret);
                        album.Title = GetString(mySqlDataReader, "titel", false, album.Title);
                        album.Medium = GetString(mySqlDataReader, "beschreibung", true, album.Medium);
                        album.Year = GetInt32(mySqlDataReader, "erschdatum", true, album.Year);
                        album.Genre = GetString(mySqlDataReader, "genre", true, album.Genre);
                        album.Cover = CCoverData.GetImageFromDbReader(mySqlDataReader, "cover");
                    }
                }
            }
            return album;
        }

        private static CAlbum GetAlbumTracksByTitelId(CAlbum album, MySqlConnection mySqlConnection)
        {
            string strSelectSQL = "Select l.liedid,l.titelid,l.track,l.lied," +
                " l.dauer,l.liedpfad FROM platten.lieder l" +
                " JOIN platten.titel t ON l.titelid = t.titelid" +
                " WHERE t.titelid = ?TitelID" +
                " ORDER BY l.track";
            ArrayList aTracks = new ArrayList();

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSQL, mySqlConnection))
            {
                MySqlParameter mySqlParamter = mySqlCommand.Parameters.Add(new MySqlParameter("TitelID", MySqlDbType.Int32, 0));
                mySqlParamter.Direction = ParameterDirection.Input;
                mySqlParamter.SourceColumn = "TitelID";
                mySqlParamter.Value = album.AlbumId;

                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        CTrack track = new CTrack();
                        track.TitelId = album.AlbumId;
                        track.LiedId = GetInt32(mySqlDataReader, "liedid", false, track.LiedId);
                        track.TrackNumber = GetInt32(mySqlDataReader, "track", false, track.TrackNumber);
                        track.Title = GetString(mySqlDataReader, "lied", true, track.Title);
                        track.Duration = GetDateTime(mySqlDataReader, "dauer", true, track.Duration);
                        track.FileName = GetString(mySqlDataReader, "liedpfad", true, track.FileName);
                        track.FileFullName = GetString(mySqlDataReader, "liedpfad", true, track.FileName);
                        aTracks.Add(track);
                    }
                }
                CTrack[] tracks = new CTrack[aTracks.Count];
                aTracks.CopyTo(tracks);
                album.Tracks = tracks;
            }
            return album;
        }
        #endregion

        #region Trees

        private static DataSet GetInterpretsAndAlbumsAsDataSet(string strDataRelationName, string strConnection)
        {
            DataSet dataSetInterpretsAndAlbums = null;
            string strSelectInterpretSQL = "SELECT DISTINCT i.interpretid,i.interpret" +
                " FROM interpreten i" +
                " INNER JOIN titel ti ON i.interpretid = ti.interpretid" +
                " INNER JOIN lieder l ON ti.titelid = l.titelid" +
                " WHERE l.liedpfad IS NOT NULL" +
                " ORDER by i.Interpret";

            string strSelectTitelSQL = "Select DISTINCT ti.titelid,ti.titel,ti.interpretid" +
                " FROM titel ti" +
                " INNER JOIN lieder l ON ti.titelid = l.titelid" +
                " WHERE l.liedpfad IS NOT NULL" +
                " ORDER By ti.titel";

            dataSetInterpretsAndAlbums = new DataSet("Interpreten");
            dataSetInterpretsAndAlbums.Locale = CultureInfo.InvariantCulture;
            dataSetInterpretsAndAlbums.Relations.Clear();

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlDataAdapter mySqlDataAdapterInterpret = new MySqlDataAdapter())
                {
                    mySqlDataAdapterInterpret.SelectCommand = new MySqlCommand(strSelectInterpretSQL, mySqlConnection);
                    mySqlDataAdapterInterpret.Fill(dataSetInterpretsAndAlbums, "Interpret");
                }

                using (MySqlDataAdapter mySqlDataAdapterTitel = new MySqlDataAdapter())
                {
                    mySqlDataAdapterTitel.SelectCommand = new MySqlCommand(strSelectTitelSQL, mySqlConnection);
                    mySqlDataAdapterTitel.Fill(dataSetInterpretsAndAlbums, "Titel");
                }
                dataSetInterpretsAndAlbums.Relations.Add(
                    strDataRelationName,
                    dataSetInterpretsAndAlbums.Tables["Interpret"].Columns["InterpretID"],
                    dataSetInterpretsAndAlbums.Tables["Titel"].Columns["InterpretID"]);
            }
            return dataSetInterpretsAndAlbums;
        }

        private static DataSet GetGenresWithAlbumsAsDataSet(string strDataRelationName, string strConnection)
        {
            DataSet dataSetGenresWithAlbums = null;
            string strSelectGenreSQL = "SELECT DISTINCT g.genreid,g.genre" +
                " FROM genre g" +
                " JOIN titel t ON g.genreid = t.genreid" +
                " JOIN lieder l ON t.titelid = l.titelid AND l.liedpfad IS NOT NULL" +
                " JOIN interpreten i ON t.interpretid = i.interpretid" +
                " WHERE t.genreid is not null" +
                " ORDER by g.genre,i.Interpret,t.titel";

            string strSelectAlbumSQL = "SELECT DISTINCT t.genreid,t.titelid,i.interpret,t.titel" +
                " FROM interpreten i" +
                " JOIN titel t ON i.interpretid = t.interpretid" +
                " JOIN lieder l ON t.titelid = l.titelid AND l.liedpfad IS NOT NULL" +
                " WHERE t.genreid is not null" +
                " ORDER by i.Interpret";


            dataSetGenresWithAlbums = new DataSet("Genres");
            dataSetGenresWithAlbums.Locale = CultureInfo.InvariantCulture;
            dataSetGenresWithAlbums.Relations.Clear();

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlDataAdapter mySqlDataAdapterInterpret = new MySqlDataAdapter())
                {
                    mySqlDataAdapterInterpret.SelectCommand = new MySqlCommand(strSelectGenreSQL, mySqlConnection);
                    mySqlDataAdapterInterpret.Fill(dataSetGenresWithAlbums, "Genre");
                }

                using (MySqlDataAdapter mySqlDataAdapterTitel = new MySqlDataAdapter())
                {
                    mySqlDataAdapterTitel.SelectCommand = new MySqlCommand(strSelectAlbumSQL, mySqlConnection);
                    mySqlDataAdapterTitel.Fill(dataSetGenresWithAlbums, "Album");
                }

                dataSetGenresWithAlbums.Relations.Add(
                    strDataRelationName,
                    dataSetGenresWithAlbums.Tables["Genre"].Columns["GenreId"],
                    dataSetGenresWithAlbums.Tables["Album"].Columns["GenreId"]);
            }
            return dataSetGenresWithAlbums;
        }

        private static DataSet GetYearsWithAlbumsAsDataSet(string strDataRelationName, string strConnection)
        {
            DataSet dataSetYearsWithAlbums = null;
            string strSelectYearsSQL = "SELECT DISTINCT t.erschdatum" +
                " FROM titel t" +
                " JOIN lieder l ON t.titelid = l.titelid AND l.liedpfad IS NOT NULL" +
                " WHERE t.erschdatum IS NOT NULL" +
                " ORDER by t.erschdatum DESC";

            string strSelectAlbumSQL = "SELECT DISTINCT t.erschdatum,t.titelid,i.interpret,t.titel" +
                " FROM interpreten i" +
                " JOIN titel t ON i.interpretid = t.interpretid" +
                " JOIN lieder l ON t.titelid = l.titelid AND l.liedpfad IS NOT NULL" +
                " WHERE t.erschdatum IS NOT NULL" +
                " ORDER by i.Interpret, t.titel";

            dataSetYearsWithAlbums = new DataSet("Years");
            dataSetYearsWithAlbums.Locale = CultureInfo.InvariantCulture;
            dataSetYearsWithAlbums.Relations.Clear();

            using (MySqlConnection mySqlConnection = new MySqlConnection(strConnection))
            {
                mySqlConnection.Open();

                using (MySqlDataAdapter mySqlDataAdapterInterpret = new MySqlDataAdapter())
                {
                    mySqlDataAdapterInterpret.SelectCommand = new MySqlCommand(strSelectYearsSQL, mySqlConnection);
                    mySqlDataAdapterInterpret.Fill(dataSetYearsWithAlbums, "Year");
                }

                using (MySqlDataAdapter mySqlDataAdapterTitel = new MySqlDataAdapter())
                {
                    mySqlDataAdapterTitel.SelectCommand = new MySqlCommand(strSelectAlbumSQL, mySqlConnection);
                    mySqlDataAdapterTitel.Fill(dataSetYearsWithAlbums, "Album");
                }

                dataSetYearsWithAlbums.Relations.Add(
                    strDataRelationName,
                    dataSetYearsWithAlbums.Tables["Year"].Columns["erschdatum"],
                    dataSetYearsWithAlbums.Tables["Album"].Columns["erschdatum"]);
            }
            return dataSetYearsWithAlbums;
        }

        #endregion

        #region FullTextSearch

        private static SortableCollection<CTrack> GetFullTextSearchForTitleAndInterpretsCollection(string strSearchstring,
            SortableCollection<CTrack> trackCollection,
            MySqlConnection mySqlConnection)
        {
            string strSelectSql = "SELECT DISTINCT t.titelid,i.interpret,t.titel,'' as lied" +
                " FROM titel t" +
                " JOIN interpreten i ON t.interpretid = i.interpretid" +
                " JOIN lieder l ON t.titelid = l.titelid AND l.liedpfad IS NOT NULL" +
                " WHERE MATCH (i.interpret,t.titel) AGAINST (?InterpretTitel IN BOOLEAN MODE)" +
                " ORDER BY i.interpret ,t.titel";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSql, mySqlConnection))
            {
                MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("InterpretTitel", MySqlDbType.VarChar, 60));
                mySqlParameter.Direction = ParameterDirection.Input;
                mySqlParameter.SourceColumn = "interpret,titel";
                mySqlParameter.Value = strSearchstring;

                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        CTrack track = new CTrack();

                        track.TitelId = GetInt32(mySqlDataReader, "titelid", false, track.TitelId);
                        track.Interpret = GetString(mySqlDataReader, "interpret", false, track.Interpret);
                        track.Album = GetString(mySqlDataReader, "titel", false, track.Album);

                        trackCollection.Add(track);
                    }
                }
            }
            return trackCollection;
        }

        private static SortableCollection<CTrack> GetFullTextSearchForTitlesCollection(string strSearchstring,
            SortableCollection<CTrack> trackCollection,
            MySqlConnection mySqlConnection)
        {
            string strSelectSql = "SELECT t.titelid, i.interpret,t.titel, l.liedid,l.lied" +
                " FROM lieder l" +
                " JOIN titel t ON l.titelId = t.titelid" +
                " JOIN interpreten i ON t.interpretid = i.interpretid" +
                " WHERE MATCH (l.lied) AGAINST (?Lied IN BOOLEAN MODE)" +
                " AND l.liedpfad IS NOT NULL" +
                " ORDER BY i.interpret ,t.titel,l.lied";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSql, mySqlConnection))
            {
                MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("Lied", MySqlDbType.VarChar, 100));
                mySqlParameter.Direction = ParameterDirection.Input;
                mySqlParameter.SourceColumn = "lied";
                mySqlParameter.Value = strSearchstring;

                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        CTrack track = new CTrack();

                        track.TitelId = GetInt32(mySqlDataReader, "titelid", false, track.TitelId);
                        track.LiedId = GetInt32(mySqlDataReader, "liedid", false, track.LiedId);
                        track.Interpret = GetString(mySqlDataReader, "interpret", false, track.Interpret);
                        track.Album = GetString(mySqlDataReader, "titel", false, track.Album);
                        track.Title = GetString(mySqlDataReader, "lied", false, track.Title);

                        trackCollection.Add(track);
                    }
                }
            }
            return trackCollection;
        }

        #endregion

        #region PlayList

        private static CPlaylist GetPlayListByPlayListId(int iPlayListId, MySqlConnection mySqlConnection)
        {
            CPlaylist playList = null;
            string strSelectPlaylistSQL = "SELECT * FROM playlist" +
                " WHERE listid = ?ListId";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectPlaylistSQL, mySqlConnection))
            {
                MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("ListId", MySqlDbType.Int32));
                mySqlParameter.Direction = ParameterDirection.Input;
                mySqlParameter.SourceColumn = "ListId";
                mySqlParameter.Value = iPlayListId;

                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    if (mySqlDataReader.Read())
                    {
                        playList = new CPlaylist();
                        playList.Id = GetInt32(mySqlDataReader, "ListId", false, playList.Id);
                        playList.Name = GetString(mySqlDataReader, "ListName", false, playList.Name);
                        playList.User = GetString(mySqlDataReader, "User", false, playList.User);
                        playList.Guid = GetGuid(mySqlDataReader, "Guid", false, Guid.NewGuid());
                        playList.TimeStamp = GetDateTime(mySqlDataReader, "Timestamp", false, playList.TimeStamp);
                    }
                }
            }
            return playList;
        }

        private static CPlaylist GetPlayListEntriesByPlayListId(CPlaylist playList, MySqlConnection mySqlConnection)
        {
            string strSelectPlaylistEntriesSQL = @"Select pe.entryid,pe.playlistid,pe.liedid," +
                " pe.guid,i.interpret,t.titelid,t.titel,l.liedid,l.track,l.lied,l.dauer,l.liedpfad,g.genre,t.erschdatum,pe.timestamp" +
                " FROM playlistentries pe" +
                " JOIN playlist p ON pe.playlistid = p.listid" +
                " JOIN lieder l ON pe.liedid = l.liedid" +
                " JOIN titel t ON l.titelid = t.titelid" +
                " JOIN interpreten i ON t.interpretid = i.interpretid" +
                " LEFT JOIN genre g ON t.genreid = g.genreid" +
                " WHERE pe.playlistid = ?PlaylistId" +
                " ORDER BY pe.entryid";

            ArrayList aTracks = new ArrayList();

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectPlaylistEntriesSQL, mySqlConnection))
            {
                MySqlParameter mySqlParameterGuid = mySqlCommand.Parameters.Add(new MySqlParameter("PlaylistId", MySqlDbType.Int32, 0));
                mySqlParameterGuid.Direction = ParameterDirection.Input;
                mySqlParameterGuid.SourceColumn = "PlaylistId";
                mySqlParameterGuid.Value = playList.Id;

                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        CTrack track = new CTrack();

                        track.TitelId = GetInt32(mySqlDataReader, "titelId", false, track.TitelId);
                        track.LiedId = GetInt32(mySqlDataReader, "LiedId", false, track.LiedId);
                        track.Interpret = GetString(mySqlDataReader, "interpret", false, track.Interpret);
                        track.Album = GetString(mySqlDataReader, "titel", false, track.Album);
                        track.TrackNumber = GetInt32(mySqlDataReader, "track", false, track.TrackNumber);
                        track.Title = GetString(mySqlDataReader, "lied", false, track.Title);
                        track.Duration = GetDateTime(mySqlDataReader, "dauer", false, track.Duration);
                        track.Genre = GetString(mySqlDataReader, "genre", true, track.Genre);
                        track.Year = GetInt32(mySqlDataReader, "erschdatum", true, track.Year);
                        track.FileName = GetString(mySqlDataReader, "liedpfad", false, track.FileName);
                        track.FileFullName = GetString(mySqlDataReader, "liedpfad", false, track.FileName);

                        aTracks.Add(track);
                    }
                    CTrack[] tracks = new CTrack[aTracks.Count];
                    aTracks.CopyTo(tracks);
                    playList.Tracks = tracks;
                }
            }
            return playList;
        }

        private static CPlaylist GetPlayListByGuid(CPlaylist playList, MySqlConnection mySqlConnection)
        {
            string strSelectSQL = "SELECT listId, timestamp FROM playlist" +
                " WHERE guid = ?Guid";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSQL, mySqlConnection))
            {
                MySqlParameter mySqlParameterGuid = mySqlCommand.Parameters.Add(new MySqlParameter("Guid", MySqlDbType.VarChar, 36));
                mySqlParameterGuid.Direction = ParameterDirection.Input;
                mySqlParameterGuid.SourceColumn = "guid";
                mySqlParameterGuid.Value = playList.Guid.ToString();

                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    if (mySqlDataReader.Read())
                    {
                        playList.Id = GetInt32(mySqlDataReader, "listId", false, playList.Id);
                        playList.TimeStamp = GetDateTime(mySqlDataReader, "timestamp", false, playList.TimeStamp);
                    }
                }
            }
            return playList;
        }

        private static void InsertPlayListEntries(CPlaylist playList, MySqlConnection mySqlConnection)
        {
            string strInsertEntriesSQL = "INSERT INTO playlistentries (entryid,playlistid,liedid,guid)" +
                " VALUES (Null,?PlaylistId,?LiedId,?Guid)";

            foreach (CPlayListEntries playListEntries in playList.PlayListEntries)
            {
                using (MySqlCommand mySqlCommand = new MySqlCommand(strInsertEntriesSQL, mySqlConnection))
                {

                    MySqlParameter mySqlParameterPlayListId = mySqlCommand.Parameters.Add(new MySqlParameter("PlayListId", MySqlDbType.Int32));
                    mySqlParameterPlayListId.Direction = ParameterDirection.Input;
                    mySqlParameterPlayListId.SourceColumn = "PlaylistId";
                    mySqlParameterPlayListId.Value = playList.Id;

                    MySqlParameter mySqlParameterLiedId = mySqlCommand.Parameters.Add(new MySqlParameter("LiedId", MySqlDbType.Int32));
                    mySqlParameterLiedId.Direction = ParameterDirection.Input;
                    mySqlParameterLiedId.SourceColumn = "LiedId";
                    mySqlParameterLiedId.Value = playListEntries.LiedId;

                    MySqlParameter mySqlParameterGuid = mySqlCommand.Parameters.Add(new MySqlParameter("Guid", MySqlDbType.VarChar, 36));
                    mySqlParameterGuid.Direction = ParameterDirection.Input;
                    mySqlParameterGuid.SourceColumn = "Guid";
                    mySqlParameterGuid.Value = playListEntries.Guid.ToString();

                    mySqlCommand.ExecuteNonQuery();
                }
            }
        }

        private static void DeletePlayList(int iPlayListId, MySqlConnection mySqlConnection)
        {
            string strDeleteEntriesSQL = "DELETE FROM playlist WHERE listid = ?ListId";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strDeleteEntriesSQL, mySqlConnection))
            {
                MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("ListId", MySqlDbType.Int32, 0));
                mySqlParameter.Direction = ParameterDirection.Input;
                mySqlParameter.SourceColumn = "ListId";
                mySqlParameter.Value = iPlayListId;

                mySqlCommand.ExecuteNonQuery();
            }
        }

        private static bool DeletePlayListEntries(int iPlayListId, MySqlConnection mySqlConnection)
        {
            bool bDeleteOk = false;
            string strDeleteEntriesSQL = "DELETE FROM playlistentries WHERE playlistid = ?PlayListId";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strDeleteEntriesSQL, mySqlConnection))
            {
                MySqlParameter mySqlParameter = mySqlCommand.Parameters.Add(new MySqlParameter("PlayListId", MySqlDbType.Int32));
                mySqlParameter.Direction = ParameterDirection.Input;
                mySqlParameter.SourceColumn = "PlaylistId";
                mySqlParameter.Value = iPlayListId;

                mySqlCommand.ExecuteNonQuery();
                bDeleteOk = true;
            }
            return bDeleteOk;
        }

        #endregion

        #region Filters

        private static CFilter[] GetFilterByFilterModeGenre(MySqlConnection mySqlConnection)
        {
            CFilter[] filters = null;
            ArrayList aFilters = new ArrayList();
            string strSelectSQL = "SELECT COUNT(l.lied) AS anzahl,g.genreid,g.genre" +
                " FROM lieder l" +
                " JOIN titel t ON l.titelid = t.titelid AND t.genreid IS NOT NULL" +
                " JOIN genre g ON t.genreid = g.genreid" +
                " AND l.liedpfad IS NOT Null" +
                " GROUP BY g.genre" +
                " ORDER BY g.genre";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSQL, mySqlConnection))
            {
                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        CFilter filter = new CFilter();

                        //MySQL returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement.
                        //The result is a BIGINT value. 
                        filter.Number = Convert.ToInt32(GetInt64(mySqlDataReader, "anzahl", true, filter.Number));
                        filter.Id = GetInt32(mySqlDataReader, "genreid", true, filter.Id);
                        filter.Name = GetString(mySqlDataReader, "genre", false, filter.Name);

                        aFilters.Add(filter);
                    }
                    filters = new CFilter[aFilters.Count];
                    aFilters.CopyTo(filters);
                }
            }
            return filters;
        }

        private static CFilter[] GetFilterByFilterModeYear(MySqlConnection mySqlConnection)
        {
            CFilter[] filters = null;
            ArrayList aFilters = new ArrayList();
            string strSelectSQL = "SELECT COUNT(l.lied) AS anzahl,t.erschdatum" +
                " FROM lieder l" +
                " INNER JOIN titel t ON l.titelid = t.titelid AND (t.erschdatum <> 0 OR t.erschdatum IS NOT NULL)" +
                " WHERE l.liedpfad IS NOT Null" +
                " GROUP BY t.erschdatum" +
                " ORDER BY t.erschdatum DESC";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSQL, mySqlConnection))
            {
                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        BSE.Platten.BO.CFilter filter = new BSE.Platten.BO.CFilter();

                        //MySQL returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement.
                        //The result is a BIGINT value. 
                        filter.Number = Convert.ToInt32(GetInt64(mySqlDataReader, "anzahl", true, filter.Number));
                        filter.Id = GetInt32(mySqlDataReader, "erschdatum", true, filter.Id);
                        filter.Name = GetInt32(mySqlDataReader, "erschdatum", true, 0).ToString(CultureInfo.InvariantCulture);

                        aFilters.Add(filter);
                    }
                    filters = new CFilter[aFilters.Count];
                    aFilters.CopyTo(filters);
                }
            }
            return filters;
        }

        private static FilterSettings GetFilterSettings(MySqlConnection mySqlConnection, FilterSettings.FilterMode filterMode, string strBenutzer)
        {
            FilterSettings filterSettings = null;
            string strSelectSql = "SELECT f.filterid, f.mode," +
                " f.value, f.isused, f.benutzer" +
                " FROM filtersettings f" +
                " WHERE f.mode = ?Mode" +
                " AND f.benutzer = ?Benutzer";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSql, mySqlConnection))
            {
                MySqlParameter mySqlParameterFilterMode = mySqlCommand.Parameters.Add(new MySqlParameter("Mode", MySqlDbType.Int32));
                mySqlParameterFilterMode.Direction = ParameterDirection.Input;
                mySqlParameterFilterMode.SourceColumn = "mode";
                mySqlParameterFilterMode.Value = (int)filterMode;
                MySqlParameter mySqlParameterUser = mySqlCommand.Parameters.Add(new MySqlParameter("Benutzer", MySqlDbType.VarChar, 50));
                mySqlParameterUser.Direction = ParameterDirection.Input;
                mySqlParameterUser.SourceColumn = "benutzer";
                mySqlParameterUser.Value = strBenutzer;

                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    if (mySqlDataReader.Read())
                    {
                        filterSettings = new FilterSettings();

                        filterSettings.FilterId = GetInt32(mySqlDataReader,"filterid",false,filterSettings.FilterId);
                        int iIndexUsedFilterMode = mySqlDataReader.GetOrdinal("mode");
                        filterSettings.UsedFilterMode = (FilterSettings.FilterMode)Enum.Parse(
                            typeof(FilterSettings.FilterMode),
                            mySqlDataReader.GetString(iIndexUsedFilterMode));
                        filterSettings.Value = GetString(mySqlDataReader, "value", true, filterSettings.Value);
                        filterSettings.IsUsed = GetBoolean(mySqlDataReader, "isused", true, filterSettings.IsUsed);
                        filterSettings.Benutzer = GetString(mySqlDataReader, "benutzer", false, filterSettings.Benutzer);
                    }
                }
            }
            return filterSettings;
        }

        private static void InsertFilterSettings(MySqlConnection mySqlConnection, FilterSettings.FilterMode filterMode, string strBenutzer)
        {
            string strInsertSql = "INSERT INTO filtersettings" +
                " (mode, value, isused, benutzer)" +
                " VALUES(?Mode, ?Value, false, ?Benutzer)";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strInsertSql, mySqlConnection))
            {
                MySqlParameter mySqlParameterFilterMode = mySqlCommand.Parameters.Add(new MySqlParameter("Mode", MySqlDbType.Int32));
                mySqlParameterFilterMode.Direction = ParameterDirection.Input;
                mySqlParameterFilterMode.SourceColumn = "mode";
                mySqlParameterFilterMode.Value = (int)filterMode;

                MySqlParameter mySqlParameterValue = mySqlCommand.Parameters.Add(new MySqlParameter("Value", MySqlDbType.VarChar, 255));
                mySqlParameterValue.Direction = ParameterDirection.Input;
                mySqlParameterValue.SourceColumn = "value";
                mySqlParameterValue.Value = System.DBNull.Value;

                MySqlParameter mySqlParameterBenutzer = mySqlCommand.Parameters.Add(new MySqlParameter("Benutzer", MySqlDbType.VarChar, 50));
                mySqlParameterBenutzer.Direction = ParameterDirection.Input;
                mySqlParameterBenutzer.SourceColumn = "benutzer";
                mySqlParameterBenutzer.Value = strBenutzer;

                mySqlCommand.ExecuteNonQuery();
            }
        }

        #endregion

        #region History
        private static bool InsertHistoryData(MySqlConnection mySqlConnection, HistoryData historyData)
        {
            string strInsertSqlInfo = "INSERT INTO history" +
                " (appid,titelid,liedid,zeit,benutzer)" +
                " VALUES (?AppID,?TitelID,?LiedID,?Zeit,?Benutzer)";

            using (MySqlCommand mySqlCommand = new MySqlCommand(strInsertSqlInfo, mySqlConnection))
            {
                MySqlParameter mySqlParameterApId = mySqlCommand.Parameters.Add(new MySqlParameter("AppID", MySqlDbType.Int32, 0));
                mySqlParameterApId.Direction = ParameterDirection.Input;
                mySqlParameterApId.SourceColumn = "AppID";
                mySqlParameterApId.Value = historyData.AppId;

                MySqlParameter mySqlParameterTitelId = mySqlCommand.Parameters.Add(new MySqlParameter("TitelID", MySqlDbType.Int32, 0));
                mySqlParameterTitelId.Direction = ParameterDirection.Input;
                mySqlParameterTitelId.SourceColumn = "TitelID";
                mySqlParameterTitelId.Value = historyData.TitelId;

                MySqlParameter mySqlParameterLiedId = mySqlCommand.Parameters.Add(new MySqlParameter("LiedID", MySqlDbType.Int32, 0));
                mySqlParameterLiedId.Direction = ParameterDirection.Input;
                mySqlParameterLiedId.SourceColumn = "LiedID";
                mySqlParameterLiedId.Value = historyData.LiedId;

                MySqlParameter mySqlParameterPlayedAt = mySqlCommand.Parameters.Add(new MySqlParameter("Zeit", MySqlDbType.DateTime, 0));
                mySqlParameterPlayedAt.Direction = ParameterDirection.Input;
                mySqlParameterPlayedAt.SourceColumn = "Zeit";
                mySqlParameterPlayedAt.Value = historyData.PlayedAt;

                MySqlParameter mySqlParameterUser = mySqlCommand.Parameters.Add(new MySqlParameter("Benutzer", MySqlDbType.VarChar, 50));
                mySqlParameterUser.Direction = ParameterDirection.Input;
                mySqlParameterUser.SourceColumn = "Benutzer";
                mySqlParameterUser.Value = historyData.UserName;

                if (mySqlCommand.ExecuteNonQuery() > 0)
                {
                    return true;
                }
            }
            return false;
        }

        private static SortableCollection<CTrack> GetHistoryTrackCollection(
            MySqlConnection mySqlConnection,
            SortableCollection<CTrack> trackCollection,
            string strUser)
        {
            int iPlayListLimit = 40;
            string strSelectSql = "SELECT h.titelid, h.liedid, h.zeit, i.interpret, t.titel, l.lied" +
                " FROM history h" +
                " JOIN titel t ON h.titelid = t.titelid" +
                " JOIN interpreten i ON t.interpretid = i.interpretid" +
                " LEFT JOIN lieder l ON h.liedid = l.liedid" +
                " WHERE h.benutzer = ?User" +
                " ORDER BY h.zeit DESC LIMIT " + iPlayListLimit;

            using (MySqlCommand mySqlCommand = new MySqlCommand(strSelectSql, mySqlConnection))
            {
                MySqlParameter mySqlParameterUser = mySqlCommand.Parameters.Add(new MySqlParameter("User", MySqlDbType.VarChar, 50));
                mySqlParameterUser.Direction = ParameterDirection.Input;
                mySqlParameterUser.SourceColumn = "Benutzer";
                mySqlParameterUser.Value = strUser;

                using (MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
                {
                    while (mySqlDataReader.Read())
                    {
                        CTrack track = new CTrack();

                        track.TitelId = GetInt32(mySqlDataReader, "titelid", false, track.TitelId);
                        track.LiedId = GetInt32(mySqlDataReader, "liedid", true, track.LiedId);
                        track.Interpret = GetString(mySqlDataReader, "interpret", false, track.Interpret);
                        track.Album = GetString(mySqlDataReader, "titel", false, track.Album);
                        track.Title = GetString(mySqlDataReader, "lied", true, track.Title);
                        track.Duration = GetDateTime(mySqlDataReader, "zeit", false, track.Duration);

                        trackCollection.Add(track);
                    }
                }
            }