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