public async Task SaveToMysql(MySqlConnection conn, Res res) { if (await ExistsOnMysql(conn, res)) return; byte[] image = null; if (res.Oekaki != null) { var imagePath = cacheDir + ryu_s.MyCommon.Tool.SanitizeForFilename(res.Oekaki); using (var fs = new FileStream(imagePath, FileMode.Open, FileAccess.Read)) { using (var br = new BinaryReader(fs)) { image = br.ReadBytes((int)fs.Length); } } } var query = $"INSERT INTO {communityId} ({col_resnum},{col_name},{col_trip},{col_id},{col_date},{col_body},{col_oekaki_url},{col_oekaki_image})" + $"values(@{col_resnum},@{col_name},@{col_trip},@{col_id},@{col_date},@{col_body},@{col_oekaki_url},@{col_oekaki_image})"; var cmd = new MySqlCommand(query, conn); cmd.Parameters.Add($"@{col_resnum}", resnum_type); cmd.Parameters.Add($"@{col_name}", name_type, name_size); cmd.Parameters.Add($"@{col_trip}", trip_type, trip_size); cmd.Parameters.Add($"@{col_id}", id_type, id_size); cmd.Parameters.Add($"@{col_date}", date_type); cmd.Parameters.Add($"@{col_body}", body_type); cmd.Parameters.Add($"@{col_oekaki_url}", oekaki_url_type, oekaki_url_size); cmd.Parameters.Add($"@{col_oekaki_image}", oekaki_image_type); cmd.Parameters[$"@{col_resnum}"].Value = res.Resnum; cmd.Parameters[$"@{col_name}"].Value = (res.Name != null) ? MySqlHelper.EscapeString(res.Name) : res.Name; cmd.Parameters[$"@{col_trip}"].Value = res.Trip; cmd.Parameters[$"@{col_id}"].Value = res.Id; cmd.Parameters[$"@{col_date}"].Value = res.Date; cmd.Parameters[$"@{col_body}"].Value = (res.Body != null) ? MySqlHelper.EscapeString(res.Body) : res.Body; cmd.Parameters[$"@{col_oekaki_url}"].Value = res.Oekaki; cmd.Parameters[$"@{col_oekaki_image}"].Value = image; try { var rowsAffected = await cmd.ExecuteNonQueryAsync(); } catch (MySqlException ex) { ryu_s.MyCommon.Logging.LogException(ryu_s.MyCommon.LogLevel.error, ex); } return; }
public List<Res> ParseHtml(string html) { var list = new List<Res>(); // var pattern = "<dt class=\"reshead\">\\s+<a name=\"\\d+\" class=\"resnumhead\"></a>(?<resnum>\\d+)\\s+:\\s+<span class=\"name\">(?<name>[^<]*?)</span>\\s+:(?<predate>.+?)\\s+ID: (?<id>[^\\s]+)\\s+</dt>\\s+<dd class=\"resbody\">\\n (?<body>.+?)\\n \\n(?<footer>.+?)</dd>"; var pattern = "<dt class=\"reshead\">\\s+<a name=\"\\d+\" class=\"resnumhead\"></a>(?<resnum>\\d+)\\s+:\\s+(?<nametrip>.+?)\\s+:(?<predate>.+?)\\s+ID: (?<id>[^\\s]+)\\s+</dt>\\s+<dd class=\"resbody\">\\n (?<body>.+?)\\n \\n(?<footer>.+?)</dd>"; //var pattern = "<dt class=\"reshead\">(?<res>.+?)</dd>"; var matches = Regex.Matches(html, pattern, RegexOptions.Singleline | RegexOptions.Compiled); foreach (Match match1 in matches) { var res = new Res(); var resnum = int.Parse(match1.Groups["resnum"].Value); res.Resnum = resnum; var nametrip = match1.Groups["nametrip"].Value; var namePattern = "<span class=\"name\">(?<name>.*?)</span>"; var nameMatch = Regex.Match(nametrip, namePattern, RegexOptions.Compiled); if (nameMatch.Success) { res.Name = nameMatch.Groups["name"].Value; } var tripPattern = "<span class=\"trip\">(?<trip>.*?)</span>"; var tripMatch = Regex.Match(nametrip, tripPattern, RegexOptions.Compiled); if (tripMatch.Success) { res.Trip = tripMatch.Groups["trip"].Value; } var predate = match1.Groups["predate"].Value;//"削除しました"の場合があるため一旦文字列として抽出し、再度正規表現にかける var datePattern = "(?<year>\\d+)/(?<month>\\d+)/(?<day>\\d+)\\(.\\) (?<hour>\\d+):(?<minute>\\d+):(?<second>\\d+)"; var match = Regex.Match(predate, datePattern, RegexOptions.Compiled); if (match.Success) { var year = int.Parse(match.Groups["year"].Value); var month = int.Parse(match.Groups["month"].Value); var day = int.Parse(match.Groups["day"].Value); var hour = int.Parse(match.Groups["hour"].Value); var minute = int.Parse(match.Groups["minute"].Value); var second = int.Parse(match.Groups["second"].Value); res.Date = new DateTime(year, month, day, hour, minute, second); } var id = match1.Groups["id"].Value; res.Id = id; var preBody = match1.Groups["body"].Value; res.Body = RemoveLinkFromBody(preBody); var footer = match1.Groups["footer"].Value;//お絵かきの画像のURLが含まれている。 var oekakiPattern = "(http://dic\\.nicovideo\\.jp/.+\\.png)"; var matchOekaki = Regex.Match(footer, oekakiPattern, RegexOptions.Singleline | RegexOptions.Compiled); if (matchOekaki.Success) { res.Oekaki = matchOekaki.Groups[1].Value; } list.Add(res); } return list; }
public async Task<bool> ExistsOnMysql(MySqlConnection conn, Res res) { var query = $"select * from {communityId} where resnum={res.Resnum}"; var reader = await ryu_s.Db.MySQL.ExecuteReaderAsync(conn, query); var dt = ryu_s.Db.MySQL.ConvertDataReader(reader); return (dt.Rows.Count > 0); }