Esempio n. 1
0
        private static void CargaSalarios()
        {
            string sql;

            LectorCsv lector = new LectorCsv(@"D:\Trabajo\lahman-csv_2015-01-24\Salaries.csv", ',');
            lector.ProcesaFichero();

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BaseBall"].ConnectionString);
            conn.Open();

            SqlCommand comando;

            comando = conn.CreateCommand();
            comando.CommandType = System.Data.CommandType.Text;
            comando.CommandText = "delete from Salaries;";
            comando.ExecuteNonQuery();

            StreamWriter log = File.CreateText("SalaryLog.txt");

            for (int i = 0; i < lector.Count; i++)
            {
                sql = "insert into Salaries values(";
                sql += lector[i, "YearId"].ToSqlNumber();
                sql += "," + lector[i, "TeamId"].ToSqlString();
                sql += "," + lector[i, "LgId"].ToSqlString();
                sql += "," + lector[i, "PlayerId"].ToSqlString();
                sql += "," + lector[i, "Salary"].ToSqlNumber();
                sql += ")";

                comando = conn.CreateCommand();
                comando.CommandType = System.Data.CommandType.Text;
                comando.CommandText = sql;

                try
                {
                    comando.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    log.WriteLine("*** Error en" + i.ToString());
                    log.WriteLine(sql);
                }
            }

            conn.Close();
            conn.Dispose();
        }
Esempio n. 2
0
        private static void CargaEquipos()
        {
            string sql;

            LectorCsv lector = new LectorCsv(@"D:\Trabajo\lahman-csv_2015-01-24\Teams.csv", ',');
            lector.ProcesaFichero();

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BaseBall"].ConnectionString);
            conn.Open();

            SqlCommand comando;

            comando = conn.CreateCommand();
            comando.CommandType = System.Data.CommandType.Text;
            comando.CommandText = "delete from teams;";
            comando.ExecuteNonQuery();

            StreamWriter log = File.CreateText("TeamLog.txt");

            for (int i = 0; i < lector.Count; i++)
            {
                sql = "insert into teams values(";
                sql += lector[i, "yearID"];
                sql += ",'" + lector[i, "lgID"] + "'";
                sql += ",'" + lector[i, "teamID"] + "'";
                sql += ",'" + lector[i, "franchID"] + "'";
                sql += ",'" + lector[i, "divID"] + "'";
                sql += "," + lector[i, "Rank"].ToSqlNumber();
                sql += "," + lector[i, "G"].ToSqlNumber();
                sql += "," + lector[i, "Ghome"].ToSqlNumber();
                sql += "," + lector[i, "W"].ToSqlNumber();
                sql += "," + lector[i, "L"].ToSqlNumber();
                sql += ",'" + lector[i, "DivWin"] + "'";
                sql += ",'" + lector[i, "WCWin"] + "'";
                sql += ",'" + lector[i, "LgWin"] + "'";
                sql += ",'" + lector[i, "WSWin"] + "'";
                sql += "," + lector[i, "R"].ToSqlNumber();
                sql += "," + lector[i, "AB"].ToSqlNumber();
                sql += "," + lector[i, "H"].ToSqlNumber();
                sql += "," + lector[i, "2B"].ToSqlNumber();
                sql += "," + lector[i, "3B"].ToSqlNumber();
                sql += "," + lector[i, "HR"].ToSqlNumber();
                sql += "," + lector[i, "BB"].ToSqlNumber();
                sql += "," + lector[i, "SO"].ToSqlNumber();
                sql += "," + lector[i, "SB"].ToSqlNumber();
                sql += "," + lector[i, "CS"].ToSqlNumber();
                sql += "," + lector[i, "HBP"].ToSqlNumber();
                sql += "," + lector[i, "SF"].ToSqlNumber();
                sql += "," + lector[i, "RA"].ToSqlNumber();
                sql += "," + lector[i, "ER"].ToSqlNumber();
                sql += "," + lector[i, "ERA"].ToSqlNumber();
                sql += "," + lector[i, "CG"].ToSqlNumber();
                sql += "," + lector[i, "SHO"].ToSqlNumber();
                sql += "," + lector[i, "SV"].ToSqlNumber();
                sql += "," + lector[i, "IPouts"].ToSqlNumber();
                sql += "," + lector[i, "HA"].ToSqlNumber();
                sql += "," + lector[i, "HRA"].ToSqlNumber();
                sql += "," + lector[i, "BBA"].ToSqlNumber();
                sql += "," + lector[i, "SOA"].ToSqlNumber();
                sql += "," + lector[i, "E"].ToSqlNumber();
                sql += "," + lector[i, "DP"].ToSqlNumber();
                sql += "," + lector[i, "FP"].ToSqlNumber();
                sql += ",'" + lector[i, "name"] +"'";
                sql += ",'" + lector[i, "park"] +"'";
                sql += "," + lector[i, "attendance"].ToSqlNumber();
                sql += "," + lector[i, "BPF"].ToSqlNumber();
                sql += "," + lector[i, "PPF"].ToSqlNumber();
                sql += ",'" + lector[i, "teamIDBR"]+"'";
                sql += ",'" + lector[i, "teamIDlahman45"] + "'";
                sql += ",'" + lector[i, "teamIDretro"] + "'";
                sql += ")";

                comando = conn.CreateCommand();
                comando.CommandType = System.Data.CommandType.Text;
                comando.CommandText = sql;

                try
                {
                    comando.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    log.WriteLine("*** Error en" + i.ToString());
                    log.WriteLine(sql);
                }
            }

            conn.Close();
            conn.Dispose();
        }
Esempio n. 3
0
        private static void CargaJugadores()
        {
            string sql, aux;

            LectorCsv lector = new LectorCsv(@"D:\Trabajo\lahman-csv_2015-01-24\Master.csv", ',');
            lector.ProcesaFichero();

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BaseBall"].ConnectionString);
            conn.Open();

            SqlCommand comando;

            comando = conn.CreateCommand();
            comando.CommandType = System.Data.CommandType.Text;
            comando.CommandText = "delete from Master;";
            comando.ExecuteNonQuery();

            StreamWriter log = File.CreateText("MasterLog.txt");

            for (int i = 0; i < lector.Count; i++)
            {
                sql = "insert into Master values(";
                sql += lector[i, "playerID"].ToSqlString();
                sql += "," + lector[i, "bithyear"].ToSqlNumber();
                sql += "," + lector[i, "birthmonth"].ToSqlNumber();
                sql += "," + lector[i, "birthday"].ToSqlNumber();
                sql += "," + lector[i, "birthCountry"].ToSqlString();
                aux = lector[i, "birthState"];
                aux = aux.Length > 2 ? aux.Substring(0, 2) : aux;
                sql += "," + aux.ToSqlString();
                sql += "," + lector[i, "birthCity"].ToSqlString();
                sql += "," + lector[i, "deathYear"].ToSqlNumber();
                sql += "," + lector[i, "deathMonth"].ToSqlNumber();
                sql += "," + lector[i, "deathDay"].ToSqlNumber();
                sql += "," + lector[i, "deathCountry"].ToSqlString();
                aux = lector[i, "deathState"];
                aux = aux.Length > 2 ? aux.Substring(0, 2) : aux;
                sql += "," + aux.ToSqlString();
                sql += "," + lector[i, "deathCity"].ToSqlString();
                sql += "," + lector[i, "nameFirst"].ToSqlString();
                sql += "," + lector[i, "nameLast"].ToSqlString();
                sql += "," + lector[i, "nameGiven"].ToSqlString();
                sql += "," + lector[i, "weight"].ToSqlNumber();
                sql += "," + lector[i, "height"].ToSqlNumber();
                sql += "," + lector[i, "bats"].ToSqlString();
                sql += "," + lector[i, "throws"].ToSqlString();
                sql += "," + lector[i, "debut"].ToSqlDate();
                sql += "," + lector[i, "finalGame"].ToSqlDate();
                sql += "," + lector[i, "retroID"].ToSqlString();
                sql += "," + lector[i, "bbrefID"].ToSqlString();
                sql += ")";

                comando = conn.CreateCommand();
                comando.CommandType = System.Data.CommandType.Text;
                comando.CommandText = sql;

                try
                {
                    comando.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    log.WriteLine("*** Error en" + i.ToString());
                    log.WriteLine(sql);
                }
            }

            conn.Close();
            conn.Dispose();
        }