public void GetAll()
{
Sqlhandle.ClearData();
string Top100SongNames = "";
string Top100SongsMainAritst = "";
string Top100SongsFeaturedAritst = "";
List <string> Top100SongArtists = new List <string>();
string connection = "Server = DESKTOP-0GK7VKO; Database = TopBillboard; User Id = mydb; Password = 221198;";
//textBoxInfo.Clear();
Top100SongNames = "";
Top100SongArtists.Clear();
int countSong = 0;
HtmlElementCollection AllSpansSongs = webBrowserTopSong.Document.GetElementsByTagName("span");
using (SqlConnection cnn = new SqlConnection(connection))
{
cnn.Open();
foreach (HtmlElement el in AllSpansSongs)
{
if (el.GetAttribute("className") == "chart-element__information__song text--truncate color--primary")
{
Top100SongNames = Top100SongNames + " " + el.InnerText + "\r\n";
string commandInsert = "INSERT INTO Song(name,RankInChart) VALUES (@name, @rank) ";
SqlCommand cmdinsert = new SqlCommand(commandInsert, cnn);
cmdinsert.Parameters.AddWithValue("@name", StringHandle.RemoveFirstWhiteSpace(el.InnerText));
cmdinsert.Parameters.AddWithValue("@rank", (countSong + 1));
cmdinsert.ExecuteNonQuery();
countSong++;
}
}
// Lay ten artist
int countArtist = 0;
HtmlElementCollection AllSpansArtist = webBrowserTopSong.Document.GetElementsByTagName("span");
int rank = 1;
foreach (HtmlElement el in AllSpansArtist)
{
Top100SongsMainAritst = "";
Top100SongsFeaturedAritst = "";
if (el.GetAttribute("className") == "chart-element__information__artist text--truncate color--secondary")
{
//Top100SongArtists.Add(el.InnerText);
string[] NameArtist = el.InnerText.Split(' ');
int IndexOfFt = StringHandle.GetKeyWords(NameArtist, "featuring");
//int IndexofAnd = StringHandle.GetKeyWords(NameArtist, "&");
//int IndexofX = StringHandle.GetKeyWords(NameArtist, "x");
//neu co featuring
if (IndexOfFt != 0)
{
// cut string lam 2 voi index of string("featuring") lam moc
//doan dau se la main artist
// ta luu vao db
for (int i = 0; i < IndexOfFt; i++)
{
Top100SongsMainAritst += NameArtist[i].ToString() + " ";
}
try
{
string CommandInsertArtist = "INSERT INTO Artist(name) VALUES(@name)";
SqlCommand cmdInsertArtist = new SqlCommand(CommandInsertArtist, cnn);
cmdInsertArtist.Parameters.AddWithValue("@name", StringHandle.RemoveFirstWhiteSpace(Top100SongsMainAritst));
cmdInsertArtist.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.Write(ex.Message);
}
//luu vao bang main artist
string FindMainSongID = "SELECT id from Song WHERE RankInChart = @val";
using (SqlCommand CmdFindSongID = new SqlCommand(FindMainSongID, cnn))
{
CmdFindSongID.Parameters.AddWithValue("@val", rank);
Int32 ID = (Int32)CmdFindSongID.ExecuteScalar();
string AddID = "INSERT INTO MainArtistInSong(SongID) VALUES(@val)";
using (SqlCommand CmdAddSongID = new SqlCommand(AddID, cnn))
{
CmdAddSongID.Parameters.AddWithValue("@val", ID);
CmdAddSongID.ExecuteNonQuery();
string FindMainArtID = "SELECT id from Artist WHERE name = @val";
using (SqlCommand CmdFindArtID = new SqlCommand(FindMainArtID, cnn))
{
CmdFindArtID.Parameters.AddWithValue("@val", StringHandle.RemoveFirstWhiteSpace(Top100SongsMainAritst));
Int32 ArtID = (Int32)CmdFindArtID.ExecuteScalar();
string AddArtID = "UPDATE MainArtistInSong SET main_artistID = @val WHERE SongID = @val1";
SqlCommand CmdAddArtID = new SqlCommand(AddArtID, cnn);
CmdAddArtID.Parameters.AddWithValue("@val", ArtID);
CmdAddArtID.Parameters.AddWithValue("@val1", ID);
CmdAddArtID.ExecuteNonQuery();
}
}
}
//doan sau se la featured artist
for (int i = IndexOfFt + 1; i < NameArtist.Length; i++)
{
Top100SongsFeaturedAritst += NameArtist[i].ToString() + " ";
}
try
{
string CommandInsertArtist = "INSERT INTO Artist(name) VALUES(@name)";
SqlCommand cmdInsertArtist = new SqlCommand(CommandInsertArtist, cnn);
cmdInsertArtist.Parameters.AddWithValue("@name", StringHandle.RemoveFirstWhiteSpace(Top100SongsFeaturedAritst));
cmdInsertArtist.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.Write(ex.Message);
}
string FindFTSongID = "SELECT id from Song WHERE RankInChart = @val";
using (SqlCommand CmdFindSongID = new SqlCommand(FindFTSongID, cnn))
{
CmdFindSongID.Parameters.AddWithValue("@val", rank);
Int32 ID = (Int32)CmdFindSongID.ExecuteScalar();
string AddID = "INSERT INTO FeaturedArtistInSong(SongID) VALUES(@val)";
using (SqlCommand CmdAddSongID = new SqlCommand(AddID, cnn))
{
CmdAddSongID.Parameters.AddWithValue("@val", ID);
CmdAddSongID.ExecuteNonQuery();
string FindFTArtID = "SELECT id from Artist WHERE name = @val";
using (SqlCommand CmdFindArtID = new SqlCommand(FindFTArtID, cnn))
{
CmdFindArtID.Parameters.AddWithValue("@val", StringHandle.RemoveFirstWhiteSpace(Top100SongsFeaturedAritst));
Int32 ArtID = (Int32)CmdFindArtID.ExecuteScalar();
string AddArtID = "UPDATE FeaturedArtistInSong SET featured_artistID = @val WHERE SongID = @val1";
SqlCommand CmdAddArtID = new SqlCommand(AddArtID, cnn);
CmdAddArtID.Parameters.AddWithValue("@val", ArtID);
CmdAddArtID.Parameters.AddWithValue("@val1", ID);
CmdAddArtID.ExecuteNonQuery();
}
}
}
}
//khi khong co
else
{
try
{
string CommandInsertArtist = "INSERT INTO Artist(name) VALUES(@name)";
SqlCommand cmdInsertArtist = new SqlCommand(CommandInsertArtist, cnn);
cmdInsertArtist.Parameters.AddWithValue("@name", StringHandle.RemoveFirstWhiteSpace(el.InnerText));
cmdInsertArtist.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.Write(ex.Message);
}
string FindMainSongID = "SELECT id from Song WHERE RankInChart = @val";
using (SqlCommand CmdFindSongID = new SqlCommand(FindMainSongID, cnn))
{
CmdFindSongID.Parameters.AddWithValue("@val", rank);
Int32 ID = (Int32)CmdFindSongID.ExecuteScalar();
string AddID = "INSERT INTO MainArtistInSong(SongID) VALUES(@val)";
using (SqlCommand CmdAddSongID = new SqlCommand(AddID, cnn))
{
CmdAddSongID.Parameters.AddWithValue("@val", ID);
CmdAddSongID.ExecuteNonQuery();
string FindMainArtID = "SELECT id from Artist WHERE name = @val";
using (SqlCommand CmdFindArtID = new SqlCommand(FindMainArtID, cnn))
{
CmdFindArtID.Parameters.AddWithValue("@val", StringHandle.RemoveFirstWhiteSpace(el.InnerText));
Int32 ArtID = (Int32)CmdFindArtID.ExecuteScalar();
string AddArtID = "UPDATE MainArtistInSong SET main_artistID = @val WHERE SongID = @val1";
SqlCommand CmdAddArtID = new SqlCommand(AddArtID, cnn);
CmdAddArtID.Parameters.AddWithValue("@val", ArtID);
CmdAddArtID.Parameters.AddWithValue("@val1", ID);
CmdAddArtID.ExecuteNonQuery();
}
}
}
}
rank++;
countArtist++;
}
}
}
Sqlhandle.GetTopArtist();
Sqlhandle.GetTopAlbum();
}