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(); }