private static SongDbItems GetSongInfo(string filepath)
{
SongDbItems songInfo = new SongDbItems();
songInfo.path = filepath;
Shell shell = new Shell();
string filedir = Path.GetDirectoryName(filepath);
string filename = Path.GetFileName(filepath);
Folder objFolder = shell.NameSpace(filedir);
if (objFolder != null)
{
FolderItem fi = objFolder.ParseName(filename);
songInfo.name = objFolder.GetDetailsOf(fi, 21);
songInfo.album = objFolder.GetDetailsOf(fi, 14);
songInfo.artist = objFolder.GetDetailsOf(fi, 20);
string year_string = objFolder.GetDetailsOf(fi, 15);
if (!String.IsNullOrEmpty(year_string))
{
songInfo.year = int.Parse(year_string);
}
else
{
songInfo.year = 0;
}
if (songInfo.name.Contains("'"))
{
songInfo.name = songInfo.name.Replace("'", "`");
}
if (songInfo.album.Contains("'"))
{
songInfo.album = songInfo.album.Replace("'", "`");
}
if (songInfo.artist.Contains("'"))
{
songInfo.artist = songInfo.artist.Replace("'", "`");
}
if (songInfo.path.Contains("'"))
{
songInfo.path = songInfo.path.Replace("'", "`");
}
}
return songInfo;
}
public static List<SongDbItems> GetFilesFromDB(string dbFilePath, string filter="", string order="Name",
string orderType = "ASC")
{
List<SongDbItems> dblist = new List<SongDbItems>();
string OrderStr = "Songs.Name";
switch(order)
{
case "Name":
case "name":
OrderStr = "Songs.name";
break;
case "Artist":
case "artist":
OrderStr = "Artists.name";
break;
case "Album":
case "album":
OrderStr = "Albums.name";
break;
case "Year":
case "year":
OrderStr = "Year.year";
break;
}
using (SqlCeConnection con = CreateConnection(dbFilePath))
{
con.Open();
SqlCeDataAdapter da = new SqlCeDataAdapter(
"Select Songs.id, " +
"Songs.name, Folders.name " +
"AS rootdir, " +
"Artists.name AS artist, " +
"Albums.name AS album, " +
"Year.year AS year, " +
"Songs.path " +
"FROM Songs " +
"INNER JOIN Folders ON Folders.id = Songs.folder_id " +
"INNER JOIN Artists ON Artists.id = Songs.artist_id " +
"INNER JOIN Albums ON Albums.id = Songs.album_id " +
"INNER JOIN Year ON Year.id = Songs.year_id " +
"WHERE Songs.name LIKE '%" + filter + "%'" +
"OR Artists.name LIKE '%" + filter + "%'" +
"OR Albums.name LIKE '%" + filter + "%'" +
"ORDER BY " + OrderStr + " " + orderType, con);
DataSet ds = new DataSet("Song");
DataTable dt = new DataTable("Songs");
dt.Columns.Add(new DataColumn("id", typeof(int)));
dt.Columns.Add(new DataColumn("name", typeof(string)));
dt.Columns.Add(new DataColumn("rootdir", typeof(string)));
dt.Columns.Add(new DataColumn("artist", typeof(string)));
dt.Columns.Add(new DataColumn("album", typeof(string)));
dt.Columns.Add(new DataColumn("year", typeof(int)));
dt.Columns.Add(new DataColumn("path", typeof(string)));
ds.Tables.Add(dt);
da.Fill(ds, "Songs");
foreach (DataRow dr in ds.Tables["Songs"].Rows)
{
SongDbItems item = new SongDbItems();
item.id = (int) dr["id"];
item.name = dr["name"].ToString();
item.rootdir = dr["rootdir"].ToString();
item.artist = dr["artist"].ToString();
item.album = dr["album"].ToString();
if (dr["year"] != null)
{
item.year = (int)dr["year"];
}
else
{
item.year = 0;
}
item.path = dr["path"].ToString();
dblist.Add(item);
}
}
return dblist;
}
public static bool SetSongParams(string filepath, SongDbItems parameters)
{
try
{
SongDbItems song = GetSongInfo(filepath);
if (String.IsNullOrEmpty(song.path))
{
return false;
}
song.name = parameters.name.Replace("'", ".");
song.album = parameters.album.Replace("'", ".");
song.artist = parameters.artist.Replace("'", ".");
song.year = parameters.year;
song.rootdir = parameters.rootdir;
SongQueue.queueMutex.WaitOne();
SongQueue.items.Enqueue(song);
SongQueue.queueMutex.ReleaseMutex();
}
catch
{
try
{
SongQueue.queueMutex.ReleaseMutex();
}
catch
{
}
return false;
}
return true;
}
private static void UpdateSongInfoOnWork(object filepath)
{
int sleeptime = 1;
while (true)
{
try
{
SongQueue.queueMutex.WaitOne();
SongDbItems song = new SongDbItems();
if (SongQueue.items.Count > 0)
{
song = SongQueue.items.Dequeue();
using (SqlCeConnection con = CreateConnection(Convert.ToString(filepath)))
{
con.Open();
string filedir = Path.GetDirectoryName(song.path);
string sql = "SELECT id FROM Folders WHERE name='" +
song.rootdir + "'";
SqlCeCommand com = new SqlCeCommand(sql, con);
int folder_id = Convert.ToInt32(com.ExecuteScalar());
if (folder_id == 0)
{
return;
}
if (String.IsNullOrEmpty(song.album))
{
song.album = "Unknown album";
}
sql = "SELECT id FROM Albums WHERE name='" +
song.album + "'";
SqlCeCommand com2 = new SqlCeCommand(sql, con);
int album_id = Convert.ToInt32(com2.ExecuteScalar());
if (album_id == 0)
{
sql = "INSERT INTO Albums (name) values(@Name); ";
SqlCeCommand cmd = new SqlCeCommand(sql, con);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
cmd.Parameters["@Name"].Value = song.album;
cmd.ExecuteScalar();
sql = "SELECT id FROM Albums WHERE name='" +
song.album + "'";
album_id = Convert.ToInt32(com2.ExecuteScalar());
}
if (String.IsNullOrEmpty(song.artist))
{
song.artist = "Unknown artist";
}
sql = "SELECT id FROM Artists WHERE name='" +
song.artist + "'";
SqlCeCommand com3 = new SqlCeCommand(sql, con);
int artist_id = Convert.ToInt32(com3.ExecuteScalar());
if (artist_id == 0)
{
sql = "INSERT INTO Artists (name) values(@Name); ";
SqlCeCommand cmd = new SqlCeCommand(sql, con);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
cmd.Parameters["@Name"].Value = song.artist;
cmd.ExecuteScalar();
sql = "SELECT id FROM Artists WHERE name='" +
song.album + "'";
artist_id = Convert.ToInt32(com3.ExecuteScalar());
}
sql = "SELECT id FROM Year WHERE year='" +
song.year.ToString() + "'";
SqlCeCommand com4 = new SqlCeCommand(sql, con);
int year_id = Convert.ToInt32(com4.ExecuteScalar());
if (year_id == 0)
{
sql = "INSERT INTO Year (year) values(@Year); ";
SqlCeCommand cmd = new SqlCeCommand(sql, con);
cmd.Parameters.Add("@Year", SqlDbType.Int);
cmd.Parameters["@Year"].Value = song.year;
cmd.ExecuteScalar();
sql = "SELECT id FROM Year WHERE year='" +
song.year.ToString() + "'";
year_id = Convert.ToInt32(com4.ExecuteScalar());
}
sql = "SELECT id FROM Songs WHERE path='" +
song.path + "'";
SqlCeCommand com5 = new SqlCeCommand(sql, con);
int song_id = Convert.ToInt32(com5.ExecuteScalar());
if (String.IsNullOrEmpty(song.name))
{
song.name = "Unknown Track";
}
if (song_id == 0)
{
sql = "INSERT INTO Songs (name, folder_id, artist_id, album_id, year_id, path)" +
"values (@Name, @Folder_id, @Artist_id, @Album_id, @Year_id, @Path)";
SqlCeCommand cmd = new SqlCeCommand(sql, con);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
cmd.Parameters["@Name"].Value = song.name;
cmd.Parameters.Add("@Folder_id", SqlDbType.Int);
cmd.Parameters["@Folder_id"].Value = folder_id;
cmd.Parameters.Add("@Artist_id", SqlDbType.Int);
cmd.Parameters["@Artist_id"].Value = artist_id;
cmd.Parameters.Add("@Album_id", SqlDbType.Int);
cmd.Parameters["@Album_id"].Value = album_id;
cmd.Parameters.Add("@Year_id", SqlDbType.Int);
cmd.Parameters["@Year_id"].Value = year_id;
cmd.Parameters.Add("@Path", SqlDbType.NVarChar);
cmd.Parameters["@Path"].Value = song.path;
cmd.ExecuteScalar();
}
else
{
sql = "UPDATE Songs SET name = @Name, folder_id = @Folder_id, " +
"artist_id = @Artist_id, album_id = @Album_id, year_id = @Year_id, path = @Path " +
"WHERE id = @Song_id";
SqlCeCommand cmd = new SqlCeCommand(sql, con);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
cmd.Parameters["@Name"].Value = song.name;
cmd.Parameters.Add("@Folder_id", SqlDbType.Int);
cmd.Parameters["@Folder_id"].Value = folder_id;
cmd.Parameters.Add("@Artist_id", SqlDbType.Int);
cmd.Parameters["@Artist_id"].Value = artist_id;
cmd.Parameters.Add("@Album_id", SqlDbType.Int);
cmd.Parameters["@Album_id"].Value = album_id;
cmd.Parameters.Add("@Year_id", SqlDbType.Int);
cmd.Parameters["@Year_id"].Value = year_id;
cmd.Parameters.Add("@Path", SqlDbType.NVarChar);
cmd.Parameters["@Path"].Value = song.path;
cmd.Parameters.Add("@Song_id", SqlDbType.Int);
cmd.Parameters["@Song_id"].Value = song_id;
cmd.ExecuteScalar();
}
}
}
SongQueue.queueMutex.ReleaseMutex();
}
catch
{
try
{
SongQueue.queueMutex.ReleaseMutex();
}
catch
{
}
}
if (SongQueue.items.Count == 0)
{
Thread.Sleep(sleeptime);
sleeptime = Math.Min(1000, sleeptime * 2);
}
else
{
sleeptime = Math.Max(1, sleeptime / 2);
}
}
}