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
*/