private string GetSongs(iTunesSongs songs, string plName = "", string delim = "\t")
{
StringBuilder sb = new StringBuilder();
int i = 0;
if (plName.Trim().Length > 0)
{
sb.Append(plName + "\n");
}
foreach (var song in songs.iTunesSongList)
{
Dictionary <string, string> dict = BCHUtilities.GetObjectPropertyValueList(song);
if (i == 0)
{
foreach (var pair in dict)
{
sb.Append(pair.Key + delim);
}
sb.Append("\n");
}
i++;
foreach (var pair in dict)
{
sb.Append(pair.Value + delim);
}
sb.Append("\n");
}
return(sb.ToString());
}
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);
}
}
}
