Exemplo n.º 1
0
        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;
            }
        }
Exemplo n.º 3
0
        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;
            }
        }
Exemplo n.º 4
0
        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);
                    }
                }
            }
        }
Exemplo n.º 5
0
        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;
            }
        }
Exemplo n.º 6
0
        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;
            }
        }
Exemplo n.º 7
0
        //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;
                }
            }
        }