public static void CreateSubstituteUmpire(SubstituteUmpireDTO substituteUmpireDTO)
{
// ballpark instance of Umpire class in Retrosheet_Persist.Retrosheet
var substituteUmpire = convertToEntity(substituteUmpireDTO);
// entity data model
//var dbCtx = new retrosheetDB();
var dbCtx = new retrosheetEntities();
dbCtx.Substitute_Umpire.Add(substituteUmpire);
try
{
dbCtx.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;
}
}
public static void CreateBatterAdjustment(BatterAdjustmentDTO batterAdjustmentDTO)
{
// ballpark instance of Player class in Retrosheet_Persist.Retrosheet
var batterAdjustment = convertToEntity(batterAdjustmentDTO);
// entity data model
//var dbCtx = new retrosheetDB();
var dbCtx = new retrosheetEntities();
dbCtx.Batter_Adjustment.Add(batterAdjustment);
try
{
dbCtx.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;
}
}
public static void CreateReferenceData(ReferenceDataDTO referenceDataDTO)
{
// ballpark instance of Ballpark class in Retrosheet_Persist.Retrosheet
var referenceData = convertToEntity(referenceDataDTO);
// entity data model
//var dbCtx = new retrosheetDB();
var dbCtx = new retrosheetEntities();
try
{
dbCtx.Reference_Data.Add(referenceData);
}
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;
}
try
{
dbCtx.SaveChanges();
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine(ve.PropertyName);
Console.WriteLine(ve.ErrorMessage);
}
}
}
catch (Exception e)
{
string text;
text = e.Message;
}
}
public static void TruncateAllTables()
{
//var dbCtx = new retrosheetDB();
var dbCtx = new retrosheetEntities();
try
{
//dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Game_Info");
dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Reference_Data");
//dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Personnel");
//dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Ballpark");
/*
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Admin_Info");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Batter_Adjustment");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Ejection");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Game_Comment");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Game_Data");
*
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Game_Information");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Game_Suspension");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Pitcher_Adjustment");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Play");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Player");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Protest");
*
*
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Replay");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Starting_Player");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Substitute_Player");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Substitute_Umpire");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Team");
* dbCtx.Database.ExecuteSqlCommand("TRUNCATE TABLE Team_Batting_Order_Adjustment");
*/
}
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);
}
}
}
}
public static void CreateTeam(TeamDTO teamDTO)
{
// ballpark instance of Player class in Retrosheet_Persist.Retrosheet
var team = convertToEntity(teamDTO);
// entity data model
//var dbCtx = new retrosheetDB();
var dbCtx = new retrosheetEntities();
dbCtx.Teams.Add(team);
try
{
dbCtx.SaveChanges();
}
catch (Exception e)
{
string text;
text = e.Message;
}
}
public static void CreatePersonnel(PersonnelDTO personnelDTO)
{
// ballpark instance of Player class in Retrosheet_Persist.Retrosheet
var personnel = convertToEntity(personnelDTO);
// entity data model
//var dbCtx = new retrosheetDB();
var dbCtx = new retrosheetEntities();
dbCtx.Personnel.Add(personnel);
try
{
dbCtx.SaveChanges();
}
catch (Exception e)
{
string text;
text = e.Message;
}
}
//public static void BuildGameInformationData(string inputSeasonGameType, string inputSeasonYear)
public static void BuildGameInformationData()
{
DateTime dateTime;
int intValue;
string sqlQuery = @"select
DISTINCT(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'";
//using (RetrosheetDataContext dbCtx = new RetrosheetDataContext())
//using (SampleDatabaseDataContext dbCtx = new SampleDatabaseDataContext(ConfigurationManager.ConnectionStrings["SampleDatabaseEntities"].ConnectionString)
//SampleDatabaseDataContext dbCtx = new SampleDatabaseDataContext(ConfigurationManager.ConnectionStrings["SampleDataContext"].ConnectionString);
var dbCtx = new retrosheetEntities();
var results = dbCtx.Database.SqlQuery <_GameInformation>(sqlQuery).ToList();
//IEnumerable<_GameInformation> xx = dbCtx.Database.SqlQuery<string>(sqlQuery);
//IEnumerable < _GameInformation > results = dbCtx.ExecuteQuery<_GameInformation>(sqlQuery);
//var db = new retrosheetDB();
var db = new retrosheetEntities();
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_type = 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;
}
}
}