protected (iTunesSongs, iTunesPlayLists) GetiTunesLibXml() { string fileName = ddtbITunesLibraryFile.ItemText; iTunesSongs songs = new iTunesSongs(); songs.FilliTunesSongList(fileName); iTunesPlayLists playLists = new iTunesPlayLists(); playLists.FillPlayLists(songs, fileName); this.dataGridView1.DataSource = songs.iTunesSongList; List <NameValue> listNames = new List <NameValue>(); playLists.ITunesPlayLists.ForEach(x => { listNames.Add(new NameValue(x.Name)); }); this.dataGridView2.DataSource = listNames; this.iTuneSongs = songs; this.iTunesPlayLists = playLists; lblLibrarySongs.Text = $"Songs({songs.iTunesSongList.Count})"; lblLibraryPlaylists.Text = $"Playlists({listNames.Count})"; return(songs, playLists); }
public void InsertITunesData(iTunesSongs iTunesSongs, iTunesPlayLists iTunesPlayLists, ref OperationResult op) { try { SQLiteCommand sqlite_cmd; string sql = ""; foreach (iTunesSong song in iTunesSongs.iTunesSongList) { string location = song.Location.Replace("file://localhost/", "").Replace("/", "\\"); int fileInfoId = (from f in _mp3Context.FileInfo where (f.Path + "\\" + f.File_Name) == location select f.FileInfo_Id).FirstOrDefault(); using (var conn = new SQLiteConnection($"Data Source={_dbFileName}; Version = 3; New = True; Compress = True; Journal Mode=Off;")) { sql = @" INSERT INTO [tbItunesSong] ([FileInfo_Id],[Name],[Artist],[TrackId],[AlbumArtist],[Album],[Genre],[Kind],[Size],[TotalTIme],[DiscNumber],[DiscCount],[TrackNumber], [DateModified],[DateAdded],[BitRate],[SampleRate],[Comments],[PersistentId],[TrackType],[Location],[FileFolderCount],[LibraryFolderCount] ) VALUES ( " + fileInfoId.ToString() + @",'" + song.Name.Replace("'", "''") + @"', '" + song.Artist.Replace("'", "''") + @"'," + song.TrackId + @", '" + song.AlbumArtist.Replace("'", "''") + @"','" + song.Album.Replace("'", "''") + @"', '" + song.Genre.Replace("'", "''") + @"','" + song.Kind.Replace("'", "''") + @"'," + song.Size + @"," + song.TotalTIme + @",'" + song.DiscNumber + @"','" + song.DiscCount + @"', '" + song.TrackNumber + @"','" + song.DateModified.ToString("yyyy-MM-dd HH:mm:ss") + @"','" + song.DateAdded.ToString("yyyy-MM-dd HH:mm:ss") + @"', " + song.BitRate + @"," + song.SampleRate + @",'" + song.Comments.Replace("'", "''") + @"','" + song.PersistentId.Replace("'", "''") + @"','" + song.TrackType.Replace("'", "''") + @"','" + location.Replace("'", "''") + @"', " + song.FileFolderCount + @"," + song.LibraryFolderCount + @")"; conn.Open(); sqlite_cmd = conn.CreateCommand(); sqlite_cmd.CommandText = sql; sqlite_cmd.ExecuteNonQuery(); conn.Close(); } } int playlistId = 0; foreach (var playlist in iTunesPlayLists.ITunesPlayLists) { if (playlist.Name == "Library" || playlist.Name == "Downloaded" || playlist.Name == "Music") { continue; } using (var conn = new SQLiteConnection($"Data Source={_dbFileName}; Version = 3; New = True; Compress = True; Journal Mode=Off;")) { sql = @"INSERT INTO[tbItunesPlaylist] ([Name] ) VALUES( '" + playlist.Name.Replace("'", "''") + @"'); select last_insert_rowid();"; conn.Open(); sqlite_cmd = conn.CreateCommand(); sqlite_cmd.CommandText = sql; playlistId = Convert.ToInt32(sqlite_cmd.ExecuteScalar()); conn.Close(); } foreach (var song in playlist.Songs.iTunesSongList) { int songId = (from s in _mp3Context.ItunesSong where s.TrackId == song.TrackId select s.Song_Id).FirstOrDefault(); using (var conn = new SQLiteConnection($"Data Source={_dbFileName}; Version = 3; New = True; Compress = True; Journal Mode=Off;")) { sql = @" INSERT INTO [tbItunesPlaylistSong] ( [Playlist_Id], [Song_Id], [TrackId], [Name] ) VALUES ( " + playlistId.ToString() + @", " + songId.ToString() + @"," + song.TrackId.ToString() + @", '" + song.Name.Replace("'", "''") + @"' ) "; conn.Open(); sqlite_cmd = conn.CreateCommand(); sqlite_cmd.CommandText = sql; sqlite_cmd.ExecuteNonQuery(); conn.Close(); } } } } catch (Exception ex) { op.AddException(ex); } }
private void InsertITunesDataEF(iTunesSongs iTunesSongs, iTunesPlayLists iTunesPlayLists, ref OperationResult op) { using (DbContextTransaction transaction = _mp3Context.Database.BeginTransaction()) { try { foreach (iTunesSong song in iTunesSongs.iTunesSongList) { string path = (from f in _mp3Context.FileInfo where f.FileInfo_Id == 1 select f.Path + "\\" + f.File_Name).FirstOrDefault(); string location = song.Location.Replace("file://localhost/", "").Replace("/", "\\"); int fileInfoId = (from f in _mp3Context.FileInfo where (f.Path + "\\" + f.File_Name) == location select f.FileInfo_Id).FirstOrDefault(); tbItunesSong itunesSong = new tbItunesSong { Album = song.Album, AlbumArtist = song.AlbumArtist, Artist = song.Artist, BitRate = song.BitRate, Comments = song.Comments, DateAdded = song.DateAdded, DateModified = song.DateModified, DiscCount = song.DiscCount, DiscNumber = song.DiscNumber, FileFolderCount = song.FileFolderCount, FileInfo_Id = fileInfoId, Genre = song.Genre, Kind = song.Kind, LibraryFolderCount = song.LibraryFolderCount, Location = location, Name = song.Name, PersistentId = song.PersistentId, SampleRate = song.SampleRate, Size = song.Size, TotalTIme = song.TotalTIme, TrackId = song.TrackId, TrackNumber = song.TrackNumber, TrackType = song.TrackType }; _mp3Context.ItunesSong.Add(itunesSong); _mp3Context.SaveChanges(); } foreach (var playlist in iTunesPlayLists.ITunesPlayLists) { tbItunesPlaylist tbItunesPlaylist = new tbItunesPlaylist { Name = playlist.Name }; _mp3Context.ItunesPlaylist.Add(tbItunesPlaylist); _mp3Context.SaveChanges(); int id = tbItunesPlaylist.Playlist_Id; foreach (var song in playlist.Songs.iTunesSongList) { int songId = (from s in _mp3Context.ItunesSong where s.TrackId == song.TrackId select s.TrackId).FirstOrDefault().Value; tbItunesPlaylistSong tbItunesPlaylistSong = new tbItunesPlaylistSong { Name = song.Name, Playlist_Id = id, TrackId = song.TrackId, Song_Id = songId }; _mp3Context.ItunesPlaylistSong.Add(tbItunesPlaylistSong); _mp3Context.SaveChanges(); } } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); op.AddException(ex); } } }