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);
}