protected int insert(IvnetValues values) { string q = @"SELECT ID FROM dbo." + Table + @" WHERE Country=@Country AND League=@League AND Season=@Season AND Round IN (@Round,@RoundP1,@RoundP2) AND HomeTeam=@HomeTeam AND AwayTeam=@AwayTeam"; SqlCommand selectCmd = new SqlCommand(q, Connection); selectCmd.Parameters.AddWithValue("@Country", values.Country); selectCmd.Parameters.AddWithValue("@League", values.League); selectCmd.Parameters.AddWithValue("@Season", values.Season); byte round; byte.TryParse(values.Round, out round); selectCmd.Parameters.AddWithValue("@Round", round); selectCmd.Parameters.AddWithValue("@RoundP1", round + 1); selectCmd.Parameters.AddWithValue("@RoundP2", round + 2); selectCmd.Parameters.AddWithValue("@HomeTeam", values.HomeTeam); selectCmd.Parameters.AddWithValue("@AwayTeam", values.AwayTeam); SqlCommand modifyCmd; using (SqlDataReader reader = selectCmd.ExecuteReader()) { if (reader.HasRows) { q = @"UPDATE dbo." + Table + @" SET Country=@Country, League=@League, Season=@Season, Round=@Round, Date=@Date, HomeTeam=@HomeTeam, AwayTeam=@AwayTeam, ForecastH=@ForecastH, ForecastD=@ForecastD, ForecastA=@ForecastA, TipGoalsH=@TipGoalsH, TipGoalsA=@TipGoalsA, ScoreH=@ScoreH, ScoreA=@ScoreA, Guessed=@Guessed WHERE ID=@ID"; modifyCmd = new SqlCommand(q, Connection); reader.Read(); modifyCmd.Parameters.AddWithValue("@ID", reader[0]); } else { q = @"INSERT INTO dbo." + Table + @"( Country,League,Season,Round,Date,HomeTeam,AwayTeam,ForecastH,ForecastD,ForecastA,TipGoalsH,TipGoalsA,ScoreH,ScoreA,Guessed ) VALUES(@Country,@League,@Season,@Round,@Date,@HomeTeam,@AwayTeam,@ForecastH,@ForecastD,@ForecastA,@TipGoalsH,@TipGoalsA,@ScoreH,@ScoreA,@Guessed)"; modifyCmd = new SqlCommand(q, Connection); } } modifyCmd.Parameters.AddWithValue("@Country", values.Country); modifyCmd.Parameters.AddWithValue("@League", values.League); modifyCmd.Parameters.AddWithValue("@Season", values.Season); modifyCmd.Parameters.AddWithValue("@Round", values.Round); modifyCmd.Parameters.AddWithValue("@Date", values.Date); modifyCmd.Parameters.AddWithValue("@HomeTeam", values.HomeTeam); modifyCmd.Parameters.AddWithValue("@AwayTeam", values.AwayTeam); modifyCmd.Parameters.AddWithValue("@ForecastH", values.ForecastH); modifyCmd.Parameters.AddWithValue("@ForecastD", values.ForecastD); modifyCmd.Parameters.AddWithValue("@ForecastA", values.ForecastA); modifyCmd.Parameters.AddWithValue("@TipGoalsH", (object)values.TipGoalsH ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@TipGoalsA", (object)values.TipGoalsA ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@ScoreH", (object)values.ScoreH ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@ScoreA", (object)values.ScoreA ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@Guessed", (object)values.Guessed ?? DBNull.Value); return(modifyCmd.ExecuteNonQuery()); }
public override string ProcessLink() { KeyValuePair<string, string[]> kvpLinks = enrLinks.Current; HtmlDocument document = new HtmlDocument(); document.LoadHtml2(_wc.DownloadStringUsingResponseEncoding(kvpLinks.Value[0])); HtmlNode rootNode = document.DocumentNode; if (isNew) rootNode = rootNode.QuerySelector("td.ratc"); ReadOnlyCollection<HtmlNode> nodes = new ReadOnlyCollection<HtmlNode>(rootNode.QuerySelectorAll("tr[id='resultsc'] pre").ToArray()); string tableTxt, date; string[] tableLines, tip, score; IvnetValues values; Match m; foreach (HtmlNode node in nodes) { tableTxt = node.InnerText; tableLines = tableTxt.Split('\n'); values = new IvnetValues(); m = _rxHeader.Match(tableLines[0]); values.Country = m.Groups["Country"].Value; values.League = m.Groups["League"].Value.Trim(" -".ToCharArray()); values.Round = m.Groups["Round"].Value; values.Round = Convert.ToInt32(values.Round) == 0 ? "01" : values.Round; for (int i = 1; i < tableLines.Length - 1; i++) { m = _rx.Match(tableLines[i]); date = m.Groups["Date"].Value.Trim(); if (date != "") values.Date = date; //DateTime.Parse(date + " " + _thisYear).ToString("yyyy-MM-dd"); values.HomeTeam = m.Groups["HTeam"].Value.Trim(); values.AwayTeam = m.Groups["ATeam"].Value.Trim(); values.ForecastH = m.Groups["FH"].Value; values.ForecastD = m.Groups["FD"].Value; values.ForecastA = m.Groups["FA"].Value; tip = m.Groups["Tip"].Value.Split('-'); values.TipGoalsH = values.TipGoalsA = null; if (tip.Length==2) { values.TipGoalsH = tip[0]; values.TipGoalsA = tip[1]; } score = m.Groups["Score"].Value.Split('-'); values.ScoreH = values.ScoreA = null; if (score.Length == 2) { values.ScoreH = score[0]; values.ScoreA = score[1]; } /* Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7} {8} {9}-{10} {11}:{12}", values.Country, values.League, values.Round, values.Date, values.HomeTeam, values.AwayTeam, values.ForecastH, values.ForecastD, values.ForecastA, values.TipGoalsH, values.TipGoalsA, values.ScoreH, values.ScoreA); */ if (insert(values) == 0) Console.WriteLine("something is wrong [Ivnet.ProcessLink]"); } } return kvpLinks.Key; }
public override string ProcessLink() { KeyValuePair <string, string[]> kvpLinks = enrLinks.Current; HtmlDocument document = new HtmlDocument(); document.LoadHtml2(_wc.DownloadStringUsingResponseEncoding(kvpLinks.Value[0])); HtmlNode rootNode = document.DocumentNode; if (isNew) { rootNode = rootNode.QuerySelector("td.ratc"); } ReadOnlyCollection <HtmlNode> nodes = new ReadOnlyCollection <HtmlNode>(rootNode.QuerySelectorAll("tr[id='resultsc'] pre").ToArray()); string tableTxt, date; string[] tableLines, tip, score; IvnetValues values; Match m; foreach (HtmlNode node in nodes) { tableTxt = node.InnerText; tableLines = tableTxt.Split('\n'); values = new IvnetValues(); m = _rxHeader.Match(tableLines[0]); values.Country = m.Groups["Country"].Value; values.League = m.Groups["League"].Value.Trim(" -".ToCharArray()); values.Round = m.Groups["Round"].Value; values.Round = Convert.ToInt32(values.Round) == 0 ? "01" : values.Round; for (int i = 1; i < tableLines.Length - 1; i++) { m = _rx.Match(tableLines[i]); date = m.Groups["Date"].Value.Trim(); if (date != "") { values.Date = date; //DateTime.Parse(date + " " + _thisYear).ToString("yyyy-MM-dd"); } values.HomeTeam = m.Groups["HTeam"].Value.Trim(); values.AwayTeam = m.Groups["ATeam"].Value.Trim(); values.ForecastH = m.Groups["FH"].Value; values.ForecastD = m.Groups["FD"].Value; values.ForecastA = m.Groups["FA"].Value; tip = m.Groups["Tip"].Value.Split('-'); values.TipGoalsH = values.TipGoalsA = null; if (tip.Length == 2) { values.TipGoalsH = tip[0]; values.TipGoalsA = tip[1]; } score = m.Groups["Score"].Value.Split('-'); values.ScoreH = values.ScoreA = null; if (score.Length == 2) { values.ScoreH = score[0]; values.ScoreA = score[1]; } /* * Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7} {8} {9}-{10} {11}:{12}", values.Country, values.League, values.Round, values.Date, * values.HomeTeam, values.AwayTeam, values.ForecastH, values.ForecastD, values.ForecastA, values.TipGoalsH, values.TipGoalsA, * values.ScoreH, values.ScoreA); */ if (insert(values) == 0) { Console.WriteLine("something is wrong [Ivnet.ProcessLink]"); } } } return(kvpLinks.Key); }
protected int insert(IvnetValues values) { string q = @"SELECT ID FROM dbo." + Table + @" WHERE Country=@Country AND League=@League AND Season=@Season AND Round IN (@Round,@RoundP1,@RoundP2) AND HomeTeam=@HomeTeam AND AwayTeam=@AwayTeam"; SqlCommand selectCmd = new SqlCommand(q, Connection); selectCmd.Parameters.AddWithValue("@Country", values.Country); selectCmd.Parameters.AddWithValue("@League", values.League); selectCmd.Parameters.AddWithValue("@Season", values.Season); byte round; byte.TryParse(values.Round, out round); selectCmd.Parameters.AddWithValue("@Round", round); selectCmd.Parameters.AddWithValue("@RoundP1", round+1); selectCmd.Parameters.AddWithValue("@RoundP2", round+2); selectCmd.Parameters.AddWithValue("@HomeTeam", values.HomeTeam); selectCmd.Parameters.AddWithValue("@AwayTeam", values.AwayTeam); SqlCommand modifyCmd; using (SqlDataReader reader = selectCmd.ExecuteReader()) { if (reader.HasRows) { q = @"UPDATE dbo." + Table + @" SET Country=@Country, League=@League, Season=@Season, Round=@Round, Date=@Date, HomeTeam=@HomeTeam, AwayTeam=@AwayTeam, ForecastH=@ForecastH, ForecastD=@ForecastD, ForecastA=@ForecastA, TipGoalsH=@TipGoalsH, TipGoalsA=@TipGoalsA, ScoreH=@ScoreH, ScoreA=@ScoreA, Guessed=@Guessed WHERE ID=@ID"; modifyCmd = new SqlCommand(q, Connection); reader.Read(); modifyCmd.Parameters.AddWithValue("@ID", reader[0]); } else { q = @"INSERT INTO dbo." + Table + @"( Country,League,Season,Round,Date,HomeTeam,AwayTeam,ForecastH,ForecastD,ForecastA,TipGoalsH,TipGoalsA,ScoreH,ScoreA,Guessed ) VALUES(@Country,@League,@Season,@Round,@Date,@HomeTeam,@AwayTeam,@ForecastH,@ForecastD,@ForecastA,@TipGoalsH,@TipGoalsA,@ScoreH,@ScoreA,@Guessed)"; modifyCmd = new SqlCommand(q, Connection); } } modifyCmd.Parameters.AddWithValue("@Country", values.Country); modifyCmd.Parameters.AddWithValue("@League", values.League); modifyCmd.Parameters.AddWithValue("@Season", values.Season); modifyCmd.Parameters.AddWithValue("@Round", values.Round); modifyCmd.Parameters.AddWithValue("@Date", values.Date); modifyCmd.Parameters.AddWithValue("@HomeTeam", values.HomeTeam); modifyCmd.Parameters.AddWithValue("@AwayTeam", values.AwayTeam); modifyCmd.Parameters.AddWithValue("@ForecastH", values.ForecastH); modifyCmd.Parameters.AddWithValue("@ForecastD", values.ForecastD); modifyCmd.Parameters.AddWithValue("@ForecastA", values.ForecastA); modifyCmd.Parameters.AddWithValue("@TipGoalsH", (object)values.TipGoalsH ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@TipGoalsA", (object)values.TipGoalsA ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@ScoreH", (object)values.ScoreH ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@ScoreA", (object)values.ScoreA ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@Guessed", (object)values.Guessed ?? DBNull.Value); return modifyCmd.ExecuteNonQuery(); }