public void addToSql(String url, String text)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
DateTime time = DateTime.UtcNow;
DateTime newTime = time.AddDays(3);
// cmd.CommandText = "DELETE FROM webCrawler";
//cmd.ExecuteNonQuery();
// Insert some data
cmd.CommandText = "INSERT INTO webCrawler (link, hash, text, olddate, newdate, average) VALUES (@link, @hash, @text, @olddate, @newdate, @average)";
try
{
cmd.Parameters.AddWithValue("@link", url);
cmd.Parameters.AddWithValue("@hash", HashMD5.MD5Hash(text));
cmd.Parameters.AddWithValue("@text", text);
cmd.Parameters.AddWithValue("@olddate", time);
cmd.Parameters.AddWithValue("@newdate", newTime);
cmd.Parameters.AddWithValue("@average", 72);
//MessageBox.Show(GetWebText("https://vk.com"));
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Retrieve all rows
/* cmd.CommandText = "SELECT link FROM webCrawler";
* using (var reader = cmd.ExecuteReader())
* {
* while (reader.Read())
* {
* Console.WriteLine((reader.GetString(0)));
* }
* }*/
}
// since we only showing the result we don't need connection anymore
conn.Close();
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
// MessageBox.Show(msg.ToString());
}
}
private void Button_Click2(object sender, RoutedEventArgs e)
{
string url = firstNameTxtBox_Copy2.Text;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
DateTime dateTime;
TimeSpan diff;
// List<int> average = new List<int>();
Dictionary <string, string> dic = new Dictionary <string, string>();
cmd.CommandText = "SELECT newdate, link, hash, average FROM webCrawler where link=" + "\'" + url + "\'";
try
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string link = (string)reader["link"];
string hash = (string)reader["hash"];
dic.Add(link, hash);
}
}
foreach (KeyValuePair <string, string> kvp in dic)
{
HtmlDocument doc = new HtmlDocument();
try
{
try
{
try
{
HtmlWeb hw = new HtmlWeb();
doc = hw.Load(kvp.Key);//загрузка html страницы
}
catch
{
using (WebClient wc = new WebClient())
{
byte[] respones = wc.DownloadData(kvp.Key);
String str = UrlClass.Unzip(respones);
doc.LoadHtml(str);
}
}
}
catch (Exception ex)
{
cmd.CommandText = "DELETE FROM webCrawler where link=" + "\'" + kvp.Key + "\'";
cmd.ExecuteNonQuery();
}
UrlClass urlClass = new UrlClass();
String text = urlClass.getTxtFromWebsite(doc);
string hash = HashMD5.MD5Hash(text);
if (kvp.Value != hash)
{
cmd.CommandText = "UPDATE webCrawler set hash=" + "\'" + hash + "\'" + ", text=" + "\'" + text + "\'" + " WHERE link=" + "\'" + kvp.Key + "\'";
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Console.WriteLine(hours.ToString());
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
// since we only showing the result we don't need connection anymore
conn.Close();
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
// MessageBox.Show(msg.ToString());
}
}
