C# - Find hot threads - Printable Version

Code:
using System;

using System.Configuration;

using System.Data.SqlClient;

using System.Linq;

using System.Text.RegularExpressions;



SqlConnection cn = new(ConfigurationManager.ConnectionStrings["db"].ConnectionString);

cn.Open();

RestSharp.RestClient client = new();

client.BaseUrl = new("https://sinister.ly");

RestSharp.RestRequest request;

for (int i = 1; i < 50; ++i)

{

    request = new($"/online.php{(i == 1 ? string.Empty : $"?page={i}")}");

    RestSharp.IRestResponse response = client.Execute(request);

    Regex rx = new("<tr>\n<td.*><a.*User-.*\">(.*)</span></a></td>\\n<td.*</td>\\n<td.*>.*Thread <a.*\"Thread-(.*)\">(.*)</a>.*\\n</tr>");

    MatchCollection matches = rx.Matches(response.Content);

    if (!matches.Any())

        break;

    foreach (Match match in matches)

    {

        string thread = match.Groups[3].Value

            .Replace("<strong>", "[")

            .Replace("</strong>", "]")

            .Replace("</span>", string.Empty)

            .Replace("&nbsp;", " ")

            .Replace("&quot;", "\"");

        if (thread.Contains("\">"))

            thread = thread[..thread.IndexOf("\">")];

        using SqlCommand cmd = new("ThreadActivityIns", cn)

        {

            CommandType = System.Data.CommandType.StoredProcedure

        };

        cmd.Parameters.AddWithValue("@ThreadId", match.Groups[2].Value);

        cmd.Parameters.AddWithValue("@ThreadName", thread);

        cmd.Parameters.AddWithValue("@Username", match.Groups[1].Value);

cmd.ExecuteNonQuery();

    }

}



/*

CREATE TABLE Threads(

ThreadId VARCHAR(50) NOT NULL PRIMARY KEY,

Name NVARCHAR(150) NOT NULL)

CREATE TABLE ThreadActivity(

Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),

ThreadId VARCHAR(50) NOT NULL,

Username NVARCHAR(50) NOT NULL,

Accessed DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)

GO

CREATE PROCEDURE ThreadActivityIns

@ThreadId VARCHAR(50),

@ThreadName NVARCHAR(150),

@Username NVARCHAR(50)

AS

SET NOCOUNT ON

IF NOT EXISTS(SELECT ThreadId FROM Threads WHERE ThreadId = @ThreadId)

INSERT INTO Threads (ThreadId, Name) VALUES(@ThreadId, @ThreadName)

SET NOCOUNT OFF

IF NOT EXISTS(SELECT Id

FROM ThreadActivity

WHERE Username = @Username

AND ThreadId = @ThreadId

AND DATEDIFF(DAY, Accessed, CURRENT_TIMESTAMP) = 0)

INSERT INTO ThreadActivity

(ThreadId, Username)

VALUES (@ThreadId, @Username)

VALUES (@ThreadId, @Username)

GO

CREATE PROCEDURE PopularThreads

AS

SELECT ThreadId, CNT

INTO #tmp

FROM (

  SELECT distinct

ThreadId

,ROW_NUMBER() OVER(PARTITION BY ThreadId ORDER BY ThreadId) CNT

FROM ThreadActivity

WHERE DATEDIFF(DAY, Accessed, CURRENT_TIMESTAMP) = 0) A

WHERE CNT > 1

ORDER BY CNT DESC

SELECT DISTINCT ThreadId FROM  #tmp

DROP TABLE #tmp

GO

*/