public int InsertWebScrape(WebScrapedPost model)
{
int id = 0;
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("dbo.ScrapedNews_Insert", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
command.Parameters.Add(new SqlParameter("@Title", model.Title));
command.Parameters.Add(new SqlParameter("@Url", model.Url));
command.Parameters.Add(new SqlParameter("@CreatedBy", model.CreatedBy));
SqlParameter outputParamter = new SqlParameter();
outputParamter.ParameterName = "@Id";
outputParamter.SqlDbType = SqlDbType.Int;
outputParamter.Direction = ParameterDirection.Output;
command.Parameters.Add(outputParamter);
command.ExecuteNonQuery();
connection.Close();
id = (int)outputParamter.Value;
}
}
catch (Exception exception)
{
throw exception;
}
return(id);
}
public ActionResult<SuccessResponse> AddWebScrape()
{
ActionResult result = null;
var webClient = new WebClient();
var html = webClient.DownloadString("https://www.techmeme.com/river");
var parser = new HtmlParser();
var document = parser.ParseDocument(html);
var table = document.QuerySelector("#river_timeline");
var rows = table.QuerySelectorAll(".ritem");
foreach (var row in rows)
{
var newPost = new WebScrapedPost();
newPost.Title = row.QuerySelectorAll("a")[1].TextContent;
newPost.Url = row.QuerySelectorAll("a")[1].GetAttribute("href");
newPost.CreatedBy = row.QuerySelector("cite").TextContent.Split('/')[0].Trim();
int addpost = webScraperService.InsertWebScrape(newPost);
}
return Ok(result);
}
public List <WebScrapedPost> GetAll()
{
List <WebScrapedPost> postList = null;
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("dbo.ScrapedNews_GetAll", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
WebScrapedPost post = new WebScrapedPost();
post.Id = Convert.ToInt32(reader["Id"]);
post.Title = reader["Title"].ToString();
post.Url = reader["Url"].ToString();
post.DateCreated = Convert.ToDateTime(reader["DateCreated"]);
post.CreatedBy = reader["CreatedBy"].ToString();
if (postList == null)
{
postList = new List <WebScrapedPost>();
}
postList.Add(post);
}
connection.Close();
}
}
catch (Exception ex)
{
throw ex;
}
return(postList);
}