protected int insert(ArchiveKey aKey, int round, string team, HistoryValue hVal) { string q = @"SELECT ID FROM dbo.history WHERE Country=@Country AND League=@League AND Season=@Season AND Round=@Round AND Team=@Team"; SqlCommand selectCmd = new SqlCommand(q, Connection); selectCmd.Parameters.AddWithValue("@Country", aKey.Country); selectCmd.Parameters.AddWithValue("@League", aKey.League); selectCmd.Parameters.AddWithValue("@Season", aKey.Season); selectCmd.Parameters.AddWithValue("@Round", round); selectCmd.Parameters.AddWithValue("@Team", team); SqlCommand modifyCmd; using (SqlDataReader reader = selectCmd.ExecuteReader()) { if (reader.HasRows) { q = @"UPDATE dbo.history SET Country=@Country, League=@League, Season=@Season, Round=@Round, Date=@Date, Field=@Field, Team=@Team, ScoredH=@ScoredH, ReceivedH=@ReceivedH, ScoredA=@ScoredA, ReceivedA=@ReceivedA, Scored=@Scored, Received=@Received, Points=@Points, Position=@Position, grLevel=@grLevel, grStrength=@grStrength, OtherTeam=@OtherTeam WHERE ID=@ID"; modifyCmd = new SqlCommand(q, Connection); reader.Read(); modifyCmd.Parameters.AddWithValue("@ID", reader[0]); } else { q = @"INSERT INTO dbo.history ( Country,League,Season,Round,Date,Field,Team,ScoredH,ReceivedH,ScoredA,ReceivedA,Scored,Received,Points,Position,grLevel,grStrength,OtherTeam ) VALUES( @Country,@League,@Season,@Round,@Date,@Field,@Team,@ScoredH,@ReceivedH,@ScoredA,@ReceivedA,@Scored,@Received,@Points,@Position,@grLevel,@grStrength,@OtherTeam )"; modifyCmd = new SqlCommand(q, Connection); } } modifyCmd.Parameters.AddWithValue("@Country", aKey.Country); modifyCmd.Parameters.AddWithValue("@League", aKey.League); modifyCmd.Parameters.AddWithValue("@Season", aKey.Season); modifyCmd.Parameters.AddWithValue("@Round", round); modifyCmd.Parameters.AddWithValue("@Date", hVal.Date); modifyCmd.Parameters.AddWithValue("@Team", team); modifyCmd.Parameters.AddWithValue("@Field", hVal.Field); modifyCmd.Parameters.AddWithValue("@ScoredH", hVal.SumScoredH); modifyCmd.Parameters.AddWithValue("@ReceivedH", hVal.SumReceivedH); modifyCmd.Parameters.AddWithValue("@ScoredA", hVal.SumScoredA); modifyCmd.Parameters.AddWithValue("@ReceivedA", hVal.SumReceivedA); modifyCmd.Parameters.AddWithValue("@Scored", hVal.SumScored); modifyCmd.Parameters.AddWithValue("@Received", hVal.SumReceived); modifyCmd.Parameters.AddWithValue("@Points", hVal.Points); modifyCmd.Parameters.AddWithValue("@Position", hVal.Position); modifyCmd.Parameters.AddWithValue("@grLevel", hVal.grLevel); modifyCmd.Parameters.AddWithValue("@grStrength", (object)hVal.grStrength ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@OtherTeam", hVal.OtherTeam); return(modifyCmd.ExecuteNonQuery()); }
protected int insert(ArchiveKey aKey, int round, string team, HistoryValue hVal) { string q = @"SELECT ID FROM dbo.history WHERE Country=@Country AND League=@League AND Season=@Season AND Round=@Round AND Team=@Team"; SqlCommand selectCmd = new SqlCommand(q, Connection); selectCmd.Parameters.AddWithValue("@Country", aKey.Country); selectCmd.Parameters.AddWithValue("@League", aKey.League); selectCmd.Parameters.AddWithValue("@Season", aKey.Season); selectCmd.Parameters.AddWithValue("@Round", round); selectCmd.Parameters.AddWithValue("@Team", team); SqlCommand modifyCmd; using (SqlDataReader reader = selectCmd.ExecuteReader()) { if (reader.HasRows) { q = @"UPDATE dbo.history SET Country=@Country, League=@League, Season=@Season, Round=@Round, Date=@Date, Field=@Field, Team=@Team, ScoredH=@ScoredH, ReceivedH=@ReceivedH, ScoredA=@ScoredA, ReceivedA=@ReceivedA, Scored=@Scored, Received=@Received, Points=@Points, Position=@Position, grLevel=@grLevel, grStrength=@grStrength, OtherTeam=@OtherTeam WHERE ID=@ID"; modifyCmd = new SqlCommand(q, Connection); reader.Read(); modifyCmd.Parameters.AddWithValue("@ID", reader[0]); } else { q = @"INSERT INTO dbo.history ( Country,League,Season,Round,Date,Field,Team,ScoredH,ReceivedH,ScoredA,ReceivedA,Scored,Received,Points,Position,grLevel,grStrength,OtherTeam ) VALUES( @Country,@League,@Season,@Round,@Date,@Field,@Team,@ScoredH,@ReceivedH,@ScoredA,@ReceivedA,@Scored,@Received,@Points,@Position,@grLevel,@grStrength,@OtherTeam )"; modifyCmd = new SqlCommand(q, Connection); } } modifyCmd.Parameters.AddWithValue("@Country", aKey.Country); modifyCmd.Parameters.AddWithValue("@League", aKey.League); modifyCmd.Parameters.AddWithValue("@Season", aKey.Season); modifyCmd.Parameters.AddWithValue("@Round", round); modifyCmd.Parameters.AddWithValue("@Date", hVal.Date); modifyCmd.Parameters.AddWithValue("@Team", team); modifyCmd.Parameters.AddWithValue("@Field", hVal.Field); modifyCmd.Parameters.AddWithValue("@ScoredH", hVal.SumScoredH); modifyCmd.Parameters.AddWithValue("@ReceivedH", hVal.SumReceivedH); modifyCmd.Parameters.AddWithValue("@ScoredA", hVal.SumScoredA); modifyCmd.Parameters.AddWithValue("@ReceivedA", hVal.SumReceivedA); modifyCmd.Parameters.AddWithValue("@Scored", hVal.SumScored); modifyCmd.Parameters.AddWithValue("@Received", hVal.SumReceived); modifyCmd.Parameters.AddWithValue("@Points", hVal.Points); modifyCmd.Parameters.AddWithValue("@Position", hVal.Position); modifyCmd.Parameters.AddWithValue("@grLevel", hVal.grLevel); modifyCmd.Parameters.AddWithValue("@grStrength", (object)hVal.grStrength ?? DBNull.Value); modifyCmd.Parameters.AddWithValue("@OtherTeam", hVal.OtherTeam); return modifyCmd.ExecuteNonQuery(); }
public void process() { byte i, round; float grDiff; int aI = 1, progress; string progressTitle; string q = @"SELECT Country,League,Season,Round,Date,HomeTeam,AwayTeam,ScoreH,ScoreA FROM dbo.archive ORDER BY Country,League,Season,Round"; ArchiveKey aKey = new ArchiveKey(); Dictionary<ArchiveKey, Dictionary<int, RoundValue>> aDict = new Dictionary<ArchiveKey, Dictionary<int, RoundValue>>(); Dictionary<ArchiveKey, Dictionary<int, RoundValue>> newADict = new Dictionary<ArchiveKey, Dictionary<int, RoundValue>>(); HistoryValue hVal; RoundValue rVal; SqlCommand selectCmd; SqlDataReader reader; mainForm.backgroundWorker.ReportProgress(0, new BGReport(" Building aDict ...")); History.errDict = new Dictionary<ArchiveKey, string>(); using (reader = new SqlCommand(q, this.Connection).ExecuteReader()) { while (reader.Read()) { aKey.Country = reader["Country"].ToString(); aKey.League = reader["League"].ToString(); aKey.Season = reader["Season"].ToString(); round = (byte)reader["Round"]; if (!aDict.ContainsKey(aKey)) aDict[aKey] = new Dictionary<int, RoundValue>(); else if (aDict[aKey].ContainsKey(round)) { rVal = aDict[aKey][round]; rVal.calc(reader, aDict[aKey]); aDict[aKey][round] = rVal; continue; } aDict[aKey][round] = new RoundValue(reader, aDict[aKey]); } } mainForm.backgroundWorker.ReportProgress(10, new BGReport(" Inserting/Updating records ...")); foreach (var aKVP in aDict) { newADict[aKVP.Key] = new Dictionary<int, RoundValue>(); selectCmd = new SqlCommand("SELECT TOP 1 Country FROM dbo.history WHERE Country=@Country AND League=@League AND Season=@Season", this.Connection); selectCmd.Parameters.AddWithValue("@Country", aKVP.Key.Country); selectCmd.Parameters.AddWithValue("@League", aKVP.Key.League); selectCmd.Parameters.AddWithValue("@Season", aKVP.Key.Season); using (reader = selectCmd.ExecuteReader()) progressTitle = reader.HasRows ? " Updating " : " Inserting "; progress = (int)((double)(aI++) / aDict.Count * 80) + 10; mainForm.backgroundWorker.ReportProgress(progress, new BGReport(progressTitle + aKVP.Key + " ...")); if (History.errDict.ContainsKey(aKVP.Key)) mainForm.backgroundWorker.ReportProgress(progress, new BGReport(History.errDict[aKVP.Key], System.Drawing.Brushes.Red)); foreach (var rKVP in aKVP.Value) { var orderedEnum = from item in rKVP.Value.tDict let diff = item.Value.SumScored - item.Value.SumReceived orderby item.Value.Points descending, diff descending select item; rVal = new RoundValue(rKVP.Value); grDiff = (float)(rVal.maxPoints - rVal.minPoints) / 4; i = 1; foreach (var kvp in orderedEnum) { hVal = kvp.Value; hVal.Position = i++; hVal.grLevel = (byte)(Math.Round((rVal.maxPoints - hVal.Points) / grDiff) + 1); if (rKVP.Key == 1) hVal.grStrength = 50; else { //hVal.grStrength = getStrength1(newADict[aKVP.Key][rKVP.Key - 1], kvp); //Console.Write("{0} -> {1}). ", rKVP.Key - 1, rKVP.Key); hVal.grStrength = getStrength2(newADict[aKVP.Key][rKVP.Key - 1], kvp); //Console.WriteLine(", PrevAvgStrength:{0}", hVal.grStrength); } if (insert(aKVP.Key, rKVP.Key, kvp.Key, hVal) == 0) Console.WriteLine("something is wrong [History.process]"); //Console.WriteLine("{0}:{1}, {2}, P:{3}({4},{5})", aKVP.Key, rKVP.Key, kvp.Key, hVal.Points, hVal.grLevel, hVal.grStrength); rVal.tDict[kvp.Key] = hVal; } newADict[aKVP.Key][rKVP.Key] = rVal; //Console.WriteLine("-- {0} --\n", rKVP.Value); } } }
public void process() { byte i, round; float grDiff; int aI = 1, progress; string progressTitle; string q = @"SELECT Country,League,Season,Round,Date,HomeTeam,AwayTeam,ScoreH,ScoreA FROM dbo.archive ORDER BY Country,League,Season,Round"; ArchiveKey aKey = new ArchiveKey(); Dictionary <ArchiveKey, Dictionary <int, RoundValue> > aDict = new Dictionary <ArchiveKey, Dictionary <int, RoundValue> >(); Dictionary <ArchiveKey, Dictionary <int, RoundValue> > newADict = new Dictionary <ArchiveKey, Dictionary <int, RoundValue> >(); HistoryValue hVal; RoundValue rVal; SqlCommand selectCmd; SqlDataReader reader; mainForm.backgroundWorker.ReportProgress(0, new BGReport(" Building aDict ...")); History.errDict = new Dictionary <ArchiveKey, string>(); using (reader = new SqlCommand(q, this.Connection).ExecuteReader()) { while (reader.Read()) { aKey.Country = reader["Country"].ToString(); aKey.League = reader["League"].ToString(); aKey.Season = reader["Season"].ToString(); round = (byte)reader["Round"]; if (!aDict.ContainsKey(aKey)) { aDict[aKey] = new Dictionary <int, RoundValue>(); } else if (aDict[aKey].ContainsKey(round)) { rVal = aDict[aKey][round]; rVal.calc(reader, aDict[aKey]); aDict[aKey][round] = rVal; continue; } aDict[aKey][round] = new RoundValue(reader, aDict[aKey]); } } mainForm.backgroundWorker.ReportProgress(10, new BGReport(" Inserting/Updating records ...")); foreach (var aKVP in aDict) { newADict[aKVP.Key] = new Dictionary <int, RoundValue>(); selectCmd = new SqlCommand("SELECT TOP 1 Country FROM dbo.history WHERE Country=@Country AND League=@League AND Season=@Season", this.Connection); selectCmd.Parameters.AddWithValue("@Country", aKVP.Key.Country); selectCmd.Parameters.AddWithValue("@League", aKVP.Key.League); selectCmd.Parameters.AddWithValue("@Season", aKVP.Key.Season); using (reader = selectCmd.ExecuteReader()) progressTitle = reader.HasRows ? " Updating " : " Inserting "; progress = (int)((double)(aI++) / aDict.Count * 80) + 10; mainForm.backgroundWorker.ReportProgress(progress, new BGReport(progressTitle + aKVP.Key + " ...")); if (History.errDict.ContainsKey(aKVP.Key)) { mainForm.backgroundWorker.ReportProgress(progress, new BGReport(History.errDict[aKVP.Key], System.Drawing.Brushes.Red)); } foreach (var rKVP in aKVP.Value) { var orderedEnum = from item in rKVP.Value.tDict let diff = item.Value.SumScored - item.Value.SumReceived orderby item.Value.Points descending, diff descending select item; rVal = new RoundValue(rKVP.Value); grDiff = (float)(rVal.maxPoints - rVal.minPoints) / 4; i = 1; foreach (var kvp in orderedEnum) { hVal = kvp.Value; hVal.Position = i++; hVal.grLevel = (byte)(Math.Round((rVal.maxPoints - hVal.Points) / grDiff) + 1); if (rKVP.Key == 1) { hVal.grStrength = 50; } else { //hVal.grStrength = getStrength1(newADict[aKVP.Key][rKVP.Key - 1], kvp); //Console.Write("{0} -> {1}). ", rKVP.Key - 1, rKVP.Key); hVal.grStrength = getStrength2(newADict[aKVP.Key][rKVP.Key - 1], kvp); //Console.WriteLine(", PrevAvgStrength:{0}", hVal.grStrength); } if (insert(aKVP.Key, rKVP.Key, kvp.Key, hVal) == 0) { Console.WriteLine("something is wrong [History.process]"); } //Console.WriteLine("{0}:{1}, {2}, P:{3}({4},{5})", aKVP.Key, rKVP.Key, kvp.Key, hVal.Points, hVal.grLevel, hVal.grStrength); rVal.tDict[kvp.Key] = hVal; } newADict[aKVP.Key][rKVP.Key] = rVal; //Console.WriteLine("-- {0} --\n", rKVP.Value); } } }