private static void ReadOutParam(SqlCommand cmd, object sqlParams) { if (sqlParams != null && sqlParams.GetType().GetProperties().Any(p => p.GetCustomAttributes(typeof(SqlParameterAttribute), false) .Any(o => ((SqlParameterAttribute)o).ParameterDirection == ParameterDirection.Output))) { var sqlParameters = new SqlParameter[cmd.Parameters.Count]; var outParam = sqlParams.GetType().GetProperties() .First(p => p.GetCustomAttributes(typeof(SqlParameterAttribute), true).Any(o => ((SqlParameterAttribute)o).ParameterDirection == ParameterDirection.Output)); var outParamAttribute = outParam.GetCustomAttributes(typeof(SqlParameterAttribute), true).First() as SqlParameterAttribute; cmd.Parameters.CopyTo(sqlParameters, 0); var sqlParameter = sqlParameters.First(p => p.ParameterName == $"@{outParamAttribute.Name}"); outParam.SetValue(sqlParams, sqlParameter.Value); } }
private void UpdateBoxscore(string gameId, string seasonId) { if (!_cfgSvc.Get <bool>("Nba_Update_Boxscores")) { return; } var data = _httpSvc.Get <string>("http://stats.nba.com/stats/boxscoretraditionalv2?EndPeriod=10&EndRange=28800&GameID={0}&RangeType=0&StartPeriod=1&StartRange=0", gameId); if (string.IsNullOrEmpty(data)) { return; } var json = JObject.Parse(data); var teamPlayers = new Dictionary <int, HashSet <int> >(); var players = new Dictionary <int, string>(); SqlParameter[] sqlParameters; const string sqlBoxscores = @" MERGE NbaBoxscores s USING (SELECT @GameId GameId, @TeamId TeamId, @PlayerId PlayerId) src ON s.GameId = src.GameId AND s.TeamId = src.TeamId AND s.PlayerId = src.PlayerId WHEN MATCHED THEN UPDATE SET StartPosition = @StartPosition, Min = @Min, FGM = @FGM, FGA = @FGA, FG3M = @FG3M, FG3A = @FG3A, FTM = @FTM, FTA = @FTA, OREB = @OREB, REB = @REB, AST = @AST, STL = @STL, BLK = @BLK, TOVR = @TOVR, PF = @PF, PTS = @PTS WHEN NOT MATCHED THEN INSERT (GameId, TeamId, PlayerId, StartPosition, Min, FGM, FGA, FG3M, FG3A, FTM, FTA, OREB, REB, AST, STL, BLK, TOVR, PF, PTS) VALUES (@GameId, @TeamId, @PlayerId, @StartPosition, @Min, @FGM, @FGA, @FG3M, @FG3A, @FTM, @FTA, @OREB, @REB, @AST, @STL, @BLK, @TOVR, @PF, @PTS);"; var mapCustomColumns = new Dictionary <string, string> { { "GAME_ID", "GameId" }, { "TEAM_ID", "TeamId" }, { "PLAYER_ID", "PlayerId" }, { "PLAYER_NAME", "PlayerName" }, { "START_POSITION", "StartPosition" }, { "TO", "TOVR" } }; foreach (var resultSet in json["resultSets"]) { if (!((string)resultSet["name"]).Equals("PlayerStats")) { continue; } var headers = resultSet["headers"].Select(header => mapCustomColumns.ContainsKey(((string)header).Trim()) ? mapCustomColumns[((string)header).Trim()] : ((string)header).Trim()).ToArray(); sqlParameters = new SqlParameter[headers.Count()]; foreach (var playerStats in resultSet["rowSet"]) { for (var i = 0; i < headers.Count(); i++) { var value = GetJProperty <string>(playerStats, i); sqlParameters[i] = string.IsNullOrEmpty(value) ? new SqlParameter { ParameterName = "@" + headers[i], Value = DBNull.Value } : new SqlParameter { ParameterName = "@" + headers[i], Value = value }; } _sqlRepo.ExecuteSQL(sqlBoxscores, sqlParameters); var teamId = Convert.ToInt32(sqlParameters.First(p => p.ParameterName.Equals("@TeamId")).Value); var playerId = Convert.ToInt32(sqlParameters.First(p => p.ParameterName.Equals("@PlayerId")).Value); if (!teamPlayers.ContainsKey(teamId)) { teamPlayers[teamId] = new HashSet <int>(); } teamPlayers[teamId].Add(playerId); players[playerId] = sqlParameters.First(p => p.ParameterName.Equals("@PlayerName")).Value.ToString(); } } UpdatePlayers(players); UpdateTeamPlayers(seasonId, teamPlayers); }