// Crawling top 200 Album in Billboard 200
public static void GetTopAlbum()
{
string connection = "Server = DESKTOP-0GK7VKO; Database = TopBillboard; User Id = mydb; Password = 221198;";
var html = @"https://www.billboard.com/charts/billboard-200";
HtmlWeb web = new HtmlWeb();
var htmlDoc = web.Load(html);
var main = htmlDoc.GetElementbyId("main");
using (SqlConnection cnn = new SqlConnection(connection))
{
cnn.Open();
//clear gia tri da luu trong string va db
//TopAlbum = "";
//TopAlbumArtist = "";
int countTopAlbum = 0;
//lay ten album
var listNames1 = main.SelectNodes("//span[contains(@class, 'chart-element__information__song text--truncate color--primary')]");
foreach (var name in listNames1)
{
string insertalbumName = "INSERT INTO Album(name, Album_rank) VALUES(@name, @rank)";
using (SqlCommand CmdAlbumInsert = new SqlCommand(insertalbumName, cnn))
{
CmdAlbumInsert.Parameters.AddWithValue("@name", StringHandle.RemoveFirstWhiteSpace(name.InnerText));
CmdAlbumInsert.Parameters.AddWithValue("@rank", countTopAlbum + 1);
CmdAlbumInsert.ExecuteNonQuery();
// TopAlbum = TopAlbum + (countTopAlbum + 1).ToString() + " " + name.InnerText + "\r\n";
countTopAlbum++;
}
}
}
using (SqlConnection cnn = new SqlConnection(connection))
{
//dien vao table Album
cnn.Open();
int countrank = 1;
var listNames2 = main.SelectNodes("//span[contains(@class, 'chart-element__information__artist text--truncate color--secondary')]");
foreach (var name in listNames2)
{
string findArtist = "SELECT id from Artist WHERE name = @value";
using (SqlCommand CmdFind = new SqlCommand(findArtist, cnn))
{
CmdFind.Parameters.AddWithValue("@value", StringHandle.RemoveFirstWhiteSpace(name.InnerText));
Int32 IDArtists = (Int32)CmdFind.ExecuteScalar();
//textBoxInfo.Text += countrank.ToString() + name.InnerText + ": " + IDArtists.ToString() + "\r\n";
string insert = "UPDATE Album SET artistID = @value WHERE id = @id";
SqlCommand CmdInsert = new SqlCommand(insert, cnn);
CmdInsert.Parameters.AddWithValue("@value", IDArtists);
CmdInsert.Parameters.AddWithValue("@id", countrank);
CmdInsert.ExecuteNonQuery();
countrank++;
}
}
}
}
public static void GetTopArtist()
{
var html = @"https://www.billboard.com/charts/artist-100";
string connection = "Server = DESKTOP-0GK7VKO; Database = TopBillboard; User Id = mydb; Password = 221198;";
HtmlWeb web = new HtmlWeb();
var htmlDoc = web.Load(html);
using (SqlConnection cnn = new SqlConnection(connection))
{
cnn.Open();
int rank = 1;
var main = htmlDoc.GetElementbyId("main");
var listNames = main.SelectNodes("//span[contains(@class, 'chart-list-item__title-text')]");
foreach (var name in listNames)
{
try
{
string InsertArtist = "INSERT INTO Artist(name) VALUES(@name)";
SqlCommand CmdInsArtist = new SqlCommand(InsertArtist, cnn);
CmdInsArtist.Parameters.AddWithValue("@name", StringHandle.RemoveFirstWhiteSpace(name.InnerText));
//textBoxInfo.Text += name.InnerText + "\r\n";
CmdInsArtist.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.Write(ex.Message);
}
//string Update = "UPDATE TopArtist SET artistID = @id, RankInChart = @val WHERE ";
}
foreach (var name in listNames)
{
string findArtist = "SELECT id from Artist WHERE name = @value";
using (SqlCommand CmdFind = new SqlCommand(findArtist, cnn))
{
CmdFind.Parameters.AddWithValue("@value", StringHandle.RemoveFirstWhiteSpace(name.InnerText));
Int32 ID = (Int32)CmdFind.ExecuteScalar();
string InsertRank = "INSERT INTO TopArtist(RankInChart, artistID) VALUES(@rank, @id)";
SqlCommand CmdInsRank = new SqlCommand(InsertRank, cnn);
CmdInsRank.Parameters.AddWithValue("@rank", rank);
CmdInsRank.Parameters.AddWithValue("@id", ID);
CmdInsRank.ExecuteNonQuery();
// textBoxInfo.Text += rank.ToString() + name.InnerText + ": " + ID.ToString() + "." + "\r\n";
rank++;
}
}
}
//lay ten artist cua top 200 album
using (SqlConnection cnn = new SqlConnection(connection))
{
cnn.Open();
var html1 = @"https://www.billboard.com/charts/billboard-200";
HtmlWeb web1 = new HtmlWeb();
var htmlDoc1 = web1.Load(html1);
var main1 = htmlDoc1.GetElementbyId("main");
var listNames2 = main1.SelectNodes("//span[contains(@class, 'chart-element__information__artist text--truncate color--secondary')]");
foreach (var name in listNames2)
{
try
{
string InsertArtist = "INSERT INTO Artist(name) VALUES(@name)";
SqlCommand CmdInsArtist = new SqlCommand(InsertArtist, cnn);
CmdInsArtist.Parameters.AddWithValue("@name", StringHandle.RemoveFirstWhiteSpace(name.InnerText));
//textBoxInfo.Text += name.InnerText + "\r\n";
CmdInsArtist.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.Write(ex.Message);
}
}
}
}
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();
}