private static Game_Information convertToEntity(GameInformationDTO gameInformationDTO)
{
var gameInformation = new Game_Information();
gameInformation.record_id = gameInformationDTO.RecordID;
gameInformation.game_id = gameInformationDTO.GameID;
gameInformation.visiting_team_id = gameInformationDTO.VisitingTeam_ID;
gameInformation.home_team_id = gameInformationDTO.HomeTeam_ID;
gameInformation.game_date = gameInformationDTO.GameDate;
gameInformation.game_number = gameInformationDTO.GameNumber;
gameInformation.start_time = gameInformationDTO.StartTime;
gameInformation.day_night = gameInformationDTO.DayNight;
if (gameInformationDTO.UsedDH == true)
{
gameInformation.used_dh = "true";
}
else
{
gameInformation.used_dh = "false";
}
gameInformation.pitches = gameInformationDTO.Pitches;
gameInformation.umpire_home_id = gameInformationDTO.UmpireHome_ID;
gameInformation.umpire_first_base_id = gameInformationDTO.UmpireFirstBaseID;
gameInformation.umpire_second_base_id = gameInformationDTO.UmpireSecondBaseID;
gameInformation.umpire_third_base_id = gameInformationDTO.UmpireThirdBaseID;
gameInformation.field_condition = gameInformationDTO.FieldCondition;
gameInformation.precipitation = gameInformationDTO.Precipitation;
gameInformation.sky = gameInformationDTO.Sky;
gameInformation.temperature = gameInformationDTO.Temperature;
gameInformation.wind_direction = gameInformationDTO.WindDirection;
gameInformation.wind_speed = gameInformationDTO.WindSpeed;
gameInformation.game_time_length_minutes = gameInformationDTO.GameTimeLengthMinutes;
gameInformation.attendance = gameInformationDTO.Attendance;
gameInformation.ballpark_id = gameInformationDTO.BallPark_ID;
gameInformation.winning_pitcher_id = gameInformationDTO.WinningPitcherID;
gameInformation.losing_pitcher_id = gameInformationDTO.LosingPitcherID;
gameInformation.save_pitcher_id = gameInformationDTO.SavePitcherID;
gameInformation.winning_rbi_player_id = gameInformationDTO.WinningPitcherID;
gameInformation.oscorer = gameInformationDTO.OScorer;
gameInformation.season_year = gameInformationDTO.SeasonYear;
gameInformation.season_game_type = gameInformationDTO.SeasonGameType;
gameInformation.edit_type = gameInformationDTO.EditType;
gameInformation.how_scored = gameInformationDTO.HowScored;
gameInformation.input_prog_vers = gameInformationDTO.InputProgVers;
gameInformation.inputter = gameInformationDTO.Inputter;
gameInformation.scorer = gameInformationDTO.Scorer;
gameInformation.translator = gameInformationDTO.Translator;
return(gameInformation);
}
public static void BuildGameInformationData()
{
DateTime dateTime;
int intValue;
string sqlQuery = @"select
driver.game_id as _game_id
, a.game_info_value as _visiting_team_id
, b.game_info_value as _home_team_id
, c.game_info_value as _game_date
, d.game_info_value as _game_number
, e.game_info_value as _start_time
, f.game_info_value as _day_night
, g.game_info_value as _used_dh
, h.game_info_value as _pitches
, i.game_info_value as _umpire_home_id
, j.game_info_value as _umpire_first_base_id
, k.game_info_value as _umpire_second_base_id
, l.game_info_value as _umpire_third_base_id
, m.game_info_value as _field_condition
, n.game_info_value as _precipitation
, o.game_info_value as _sky
, p.game_info_value as _temperature
, q.game_info_value as _wind_direction
, r.game_info_value as _wind_speed
, s.game_info_value as _game_time_length_minutes
, t.game_info_value as _attendance
, u.game_info_value as _ballpark_id
, v.game_info_value as _winning_pitcher_id
, w.game_info_value as _losing_pitcher_id
, x.game_info_value as _save_pitcher_id
, y.game_info_value as _winning_rbi_player_id
, z.game_info_value as _oscorer
, aa.game_info_value as _season_year
, bb.game_info_value as _season_game_type
, cc.game_info_value as _edit_time
, dd.game_info_value as _how_scored
, ee.game_info_value as _input_prog_vers
, ff.game_info_value as _inputter
, gg.game_info_value as _input_time
, hh.game_info_value as _scorer
, ii.game_info_value as _translator
from game_info driver
left join game_info a on driver.game_id = a.game_id
and a.game_info_type = 'visteam'
left join game_info b on driver.game_id = b.game_id
and b.game_info_type = 'hometeam'
left join game_info c on driver.game_id = c.game_id
and c.game_info_type = 'date'
left join game_info d on driver.game_id = d.game_id
and d.game_info_type = 'number'
left join game_info e on driver.game_id = e.game_id
and e.game_info_type = 'starttime'
left join game_info f on driver.game_id = f.game_id
and f.game_info_type = 'daynight'
left join game_info g on driver.game_id = g.game_id
and g.game_info_type = 'usedh'
left join game_info h on driver.game_id = h.game_id
and h.game_info_type = 'pitches'
left join game_info i on driver.game_id = i.game_id
and i.game_info_type = 'umphome'
left join game_info j on driver.game_id = j.game_id
and j.game_info_type = 'ump1b'
left join game_info k on driver.game_id = k.game_id
and k.game_info_type = 'ump2b'
left join game_info l on driver.game_id = l.game_id
and l.game_info_type = 'ump3b'
left join game_info m on driver.game_id = m.game_id
and m.game_info_type = 'fieldcond'
left join game_info n on driver.game_id = n.game_id
and n.game_info_type = 'precip'
left join game_info o on driver.game_id = o.game_id
and o.game_info_type = 'sky'
left join game_info p on driver.game_id = p.game_id
and p.game_info_type = 'temp'
left join game_info q on driver.game_id = q.game_id
and q.game_info_type = 'winddir'
left join game_info r on driver.game_id = r.game_id
and r.game_info_type = 'windspeed'
left join game_info s on driver.game_id = s.game_id
and s.game_info_type = 'timeofgame'
left join game_info t on driver.game_id = t.game_id
and t.game_info_type = 'attendance'
left join game_info u on driver.game_id = u.game_id
and u.game_info_type = 'site'
left join game_info v on driver.game_id = v.game_id
and v.game_info_type = 'wp'
left join game_info w on driver.game_id = w.game_id
and w.game_info_type = 'lp'
left join game_info x on driver.game_id = x.game_id
and x.game_info_type = 'save'
left join game_info y on driver.game_id = y.game_id
and y.game_info_type = 'gwrbi'
left join game_info z on driver.game_id = z.game_id
and z.game_info_type = 'oscorer'
left join game_info aa on driver.game_id = aa.game_id
and aa.game_info_type = 'season_year'
left join game_info bb on driver.game_id = bb.game_id
and bb.game_info_type = 'season_game_type'
left join game_info cc on driver.game_id = cc.game_id
and cc.game_info_type = 'edit_time'
left join game_info dd on driver.game_id = dd.game_id
and dd.game_info_type = 'howscored'
left join game_info ee on driver.game_id = ee.game_id
and ee.game_info_type = 'input_prog_vers'
left join game_info ff on driver.game_id = ff.game_id
and ff.game_info_type = 'inputter'
left join game_info gg on driver.game_id = gg.game_id
and gg.game_info_type = 'input_time'
left join game_info hh on driver.game_id = hh.game_id
and hh.game_info_type = 'scorer'
left join game_info ii on driver.game_id = ii.game_id
and ii.game_info_type = 'translator'
where driver.game_info_type = 'date'";
using (RetrosheetDataContext dbCtx = new RetrosheetDataContext())
{
IEnumerable <_GameInformation> results = dbCtx.ExecuteQuery <_GameInformation>(sqlQuery).ToList();
var db = new retrosheetDB();
Console.WriteLine("Game_Information record count " + results.Count());
foreach (_GameInformation result in results)
{
Game_Information game_Information = new Game_Information();
game_Information.record_id = Guid.NewGuid();
game_Information.game_id = result._game_id;
game_Information.visiting_team_id = result._visiting_team_id;
game_Information.home_team_id = result._home_team_id;
if (DateTime.TryParse(result._game_date, out dateTime))
{
game_Information.game_date = dateTime;
}
else
{
game_Information.game_date = DateTime.MinValue;
}
if (int.TryParse(result._game_number, out intValue))
{
game_Information.game_number = intValue;
}
else
{
game_Information.game_number = -1;
}
game_Information.start_time = result._start_time;
game_Information.day_night = result._day_night;
if (result._used_dh == "true")
{
game_Information.used_dh = "Y";
}
else
{
game_Information.used_dh = "N";
}
game_Information.pitches = result._pitches;
game_Information.umpire_home_id = result._umpire_home_id;
game_Information.umpire_first_base_id = result._umpire_first_base_id;
game_Information.umpire_second_base_id = result._umpire_second_base_id;
game_Information.umpire_third_base_id = result._umpire_third_base_id;
game_Information.field_condition = result._field_condition;
game_Information.precipitation = result._precipitation;
game_Information.sky = result._sky;
if (int.TryParse(result._temperature, out intValue))
{
game_Information.temperature = intValue;
}
else
{
game_Information.temperature = -1;
}
game_Information.wind_direction = result._wind_direction;
if (int.TryParse(result._wind_speed, out intValue))
{
game_Information.wind_speed = intValue;
}
else
{
game_Information.wind_speed = -1;
}
if (int.TryParse(result._game_time_length_minutes, out intValue))
{
game_Information.game_time_length_minutes = intValue;
}
else
{
game_Information.game_time_length_minutes = -1;
}
if (int.TryParse(result._attendance, out intValue))
{
game_Information.attendance = intValue;
}
else
{
game_Information.attendance = -1;
}
game_Information.ballpark_id = result._ballpark_id;
game_Information.winning_pitcher_id = result._winning_pitcher_id;
game_Information.losing_pitcher_id = result._losing_pitcher_id;
game_Information.save_pitcher_id = result._save_pitcher_id;
game_Information.winning_rbi_player_id = result._winning_rbi_player_id;
game_Information.oscorer = result._oscorer;
game_Information.season_year = result._season_year;
game_Information.season_game_type = result._season_game_type;
game_Information.edit_time = result._edit_time;
game_Information.how_scored = result._how_scored;
game_Information.input_prog_vers = result._input_prog_vers;
game_Information.inputter = result._inputter;
game_Information.input_time = result._input_time;
game_Information.scorer = result._scorer;
game_Information.translator = result._translator;
db.Game_Information.Add(game_Information);
try
{
db.SaveChanges();
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
Trace.TraceInformation("Property: {0} Error: {1}",
validationError.PropertyName,
validationError.ErrorMessage);
}
}
}
catch (Exception e)
{
string text;
text = e.Message;
}
}
}
}
