//private PGNReader reader = new PGNReader();
/// <summary>
/// This function handles the "Upload PGN" button.
/// Given a filename, parses the PGN file, and uploads
/// each chess game to the user's database.
/// </summary>
/// <param name="PGNfilename">The path to the PGN file</param>
private void UploadGamesToDatabase(string PGNfilename)
{
// This will build a connection string to your user's database on atr,
// assuimg you've typed a user and password in the GUI
PGNReader read = new PGNReader();
string connection = GetConnectionString();
List <ChessGame> games = read.ReadPGN(PGNfilename);
// TODO: Load and parse the PGN file
// We recommend creating separate libraries to represent chess data and load the file
// Use this to tell the GUI's progress bar how many total work steps there are
// For example, one iteration of your main upload loop could be one work step
SetNumWorkItems(games.Count);
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
conn.Open();
// Open a connection
foreach (ChessGame g in games)
{
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandText = "select 1 from Events where Name ='" + g.GetEventName() + "' and Site='" +
g.GetSite() + "';";
MySqlDataReader count = command.ExecuteReader();
if (count.HasRows == false)
{
count.Close();
command.CommandText = "insert ignore into Events(Name,Site,Date)" +
"values(@name,@site,@date) ;";
command.Parameters.AddWithValue("@name", g.GetEventName());
command.Parameters.AddWithValue("@site", g.GetSite());
command.Parameters.AddWithValue("@date", g.GetDate());
command.Prepare();
command.ExecuteNonQuery();
//eid is auto-increment
}
else
{
count.Close();
}
}
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
using (MySqlConnection conn2 = new MySqlConnection(connection))
{
try
{
// Open a connection
conn2.Open();
foreach (ChessGame g in games)
{
//conn2.Open();
MySqlCommand command2 = new MySqlCommand();
command2.Connection = conn2;
command2.CommandText = "select 1 from Players where Name ='" + g.GetWhiteName() + "'";
//command2.Parameters.AddWithValue("@Name", g.GetWhiteName());
// command2.Parameters.AddWithValue("@Elo", g.GetWhiteElo());
// command2.Prepare();
MySqlDataReader count = command2.ExecuteReader();
// MySqlDataReader count = command2.ExecuteReader();
//int count = Convert.ToInt32(command2.ExecuteScalar());
if (count.HasRows == false)
{
//conn2.Close();
count.Close();
command2.CommandText = "insert ignore into Players(Name, Elo) values(@Name1,@Elo1)";
command2.Parameters.AddWithValue("@Name1", g.GetWhiteName());
command2.Parameters.AddWithValue("@Elo1", g.GetWhiteElo());
command2.Prepare();
command2.ExecuteNonQuery();
}
else
{
command2.CommandText = "select Elo from Players where Name = '" + g.GetWhiteName() + "'; ";
MySqlDataReader dr = command2.ExecuteReader();
dr.Read();
int elo = dr.GetInt32(0);
System.Diagnostics.Debug.WriteLine("elo" + elo);
if (elo < g.GetWhiteElo())
{
command2.CommandText = "update Players set Elo= " + g.GetWhiteElo() + "where Name='" + g.GetWhiteName() + "';";
command2.ExecuteNonQuery();
}
count.Close();
}
}
conn2.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
using (MySqlConnection conn1 = new MySqlConnection(connection))
{
try
{
// Open a connection
conn1.Open();
foreach (ChessGame g in games)
{
//conn2.Open();
MySqlCommand command1 = new MySqlCommand();
command1.Connection = conn1;
// command2.CommandText = "insert into Players([Name],[Elo])" +
// "values(@Name,@Elo) where @Name not in (select Name from Players where Name =@Name);";
command1.CommandText = "select 1 from Players where Name =@Name";
command1.Parameters.AddWithValue("@Name", g.GetBlackName());
// command2.Parameters.AddWithValue("@Elo", g.GetWhiteElo());
// command2.Prepare();
MySqlDataReader count = command1.ExecuteReader();
// MySqlDataReader count = command2.ExecuteReader();
//int count = Convert.ToInt32(command2.ExecuteScalar());
if (count.HasRows == false)
{
//conn2.Close();
count.Close();
command1.CommandText = "insert ignore into Players(Name, Elo) values(@Name1,@Elo1)";
command1.Parameters.AddWithValue("@Name1", g.GetBlackName());
command1.Parameters.AddWithValue("@Elo1", g.GetBlackElo());
command1.ExecuteNonQuery();
}
else
{
command1.CommandText = "select Elo from Players where Name = '" + g.GetBlackName() + "'; ";
MySqlDataReader dr = command1.ExecuteReader();
dr.Read();
int elo = dr.GetInt32(0);
if (elo < g.GetBlackElo())
{
command1.CommandText = "update Players set Elo= " + g.GetBlackElo() + "where Name='" + g.GetBlackName() + "';";
command1.ExecuteNonQuery();
}
count.Close();
}
}
conn1.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
using (MySqlConnection conn3 = new MySqlConnection(connection))
{
try
{
// Open a connection
conn3.Open();
foreach (ChessGame g in games)
{
//conn2.Open();
MySqlCommand command3 = new MySqlCommand();
command3.Connection = conn3;
command3.CommandText = "select pID from Players where Name=@Name ";
command3.Parameters.AddWithValue("@Name", g.GetBlackName());
MySqlDataReader temp = command3.ExecuteReader();
bool hasContent = temp.Read();
String pid1 = "";
if (hasContent == true)
{
pid1 = temp.GetString(0);
}
temp.Close();
command3.CommandText = "select pID from Players where Name=@Name1";
command3.Parameters.AddWithValue("@Name1", g.GetWhiteName());
temp = command3.ExecuteReader();
hasContent = temp.Read();
String pid2 = "";
if (hasContent == true)
{
pid2 = temp.GetString(0);
}
temp.Close();
command3.CommandText = "select eId from Events where Name=@Name2 and Site=@Site";
command3.Parameters.AddWithValue("@Name2", g.GetEventName());
command3.Parameters.AddWithValue("@Site", g.GetSite());
temp = command3.ExecuteReader();
hasContent = temp.Read();
String eID = "";
if (hasContent == true)
{
eID = temp.GetString(0);
}
temp.Close();
//command2.CommandText = "select * from Games where BlackPlayer=@Black AND WhitePlayer=@White AND eID =@eID;";
//command2.Parameters.AddWithValue("@Black", pid1);
//command2.Parameters.AddWithValue("@White", pid2);
//command2.Parameters.AddWithValue("@eID", eID);
//// command2.Prepare();
//temp = command2.ExecuteReader();
//// MySqlDataReader count = command2.ExecuteReader();
////int count = Convert.ToInt32(command2.ExecuteScalar());
//if (temp.HasRows == false)
//{
//conn2.Close();
command3.CommandText = "insert ignore into Games(Result, Moves, BlackPlayer, WhitePlayer, eID) values(@Result, @Moves, @Player1, @Player2, @EEID)";
command3.Parameters.AddWithValue("@Result", g.GetResult());
command3.Parameters.AddWithValue("@Moves", g.Getmoves());
command3.Parameters.AddWithValue("@Player1", pid1);
command3.Parameters.AddWithValue("@Player2", pid2);
command3.Parameters.AddWithValue("@EEID", eID);
command3.ExecuteNonQuery();
WorkStepCompleted();
}
conn3.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
// TODO: iterate through your data and generate appropriate insert commands
// Use this to tell the GUI that one work step has completed:
}
/// <summary>
/// This function handles the "Upload PGN" button.
/// Given a filename, parses the PGN file, and uploads
/// each chess game to the user's database.
/// </summary>
/// <param name="PGNfilename">The path to the PGN file</param>
private void UploadGamesToDatabase(string PGNfilename)
{
// This will build a connection string to your user's database on atr,
// assuming you've typed a user and password in the GUI
string connection = GetConnectionString();
// Load and parse the PGN file
var reader = new PGNReader();
reader.ReadPGN(PGNfilename);
// Use this to tell the GUI's progress bar how many total work steps there are
// For example, one iteration of your main upload loop could be one work step
// SetNumWorkItems(...);
SetNumWorkItems(reader.GetNumWorkItems());
using (MySqlConnection conn = new MySqlConnection(connection))
{
try
{
// Open a connection
conn.Open();
// prepare commands to update Players table
var selectCmd = new MySqlCommand("select count(*) from Players where Name=@Name and Elo<@Elo;", conn);
selectCmd.Parameters.AddWithValue("@Name", "");
selectCmd.Parameters.AddWithValue("@Elo", 0);
var insertCmd = new MySqlCommand("insert ignore into Players(Name, Elo) values (@Name, @Elo);", conn);
insertCmd.Parameters.AddWithValue("@Name", "");
insertCmd.Parameters.AddWithValue("@Elo", 0);
var updateCmd = new MySqlCommand("update Players set Elo=@Elo where Name=@Name", conn);
updateCmd.Parameters.AddWithValue("@Name", "");
updateCmd.Parameters.AddWithValue("@Elo", 0);
// iterate over players and insert them
foreach (var player in reader.GetPlayers())
{
selectCmd.Parameters["@Name"].Value = player.Name;
selectCmd.Parameters["@Elo"].Value = player.Elo;
var o = selectCmd.ExecuteScalar();
int count = int.Parse(o.ToString());
if (count > 0)
{
updateCmd.Parameters["@Name"].Value = player.Name;
updateCmd.Parameters["@Elo"].Value = player.Elo;
updateCmd.ExecuteNonQuery();
}
else
{
insertCmd.Parameters["@Name"].Value = player.Name;
insertCmd.Parameters["@Elo"].Value = player.Elo;
insertCmd.ExecuteNonQuery();
}
WorkStepCompleted();
}
// prepare commands to update Events table
insertCmd = new MySqlCommand("insert ignore into Events(Name, Site, Date) values (@Name, @Site, @Date);", conn);
insertCmd.Parameters.AddWithValue("@Name", "");
insertCmd.Parameters.AddWithValue("@Site", "");
insertCmd.Parameters.AddWithValue("@Date", "");
// iterate over events and insert them
foreach (var e in reader.GetEvents())
{
insertCmd.Parameters["@Name"].Value = e.Name;
insertCmd.Parameters["@Site"].Value = e.Site;
insertCmd.Parameters["@Date"].Value = e.Date;
insertCmd.ExecuteNonQuery();
WorkStepCompleted();
}
// prepare commands to update Games table
insertCmd = new MySqlCommand("insert ignore into Games values (@Round, @Result, @Moves, (select pID from Players where Name=@BlackPlayer), "
+ "(select pID from Players where Name=@WhitePlayer), (select eID from Events where Name=@Name and Site=@Site and Date=@Date));", conn);
insertCmd.Parameters.AddWithValue("@Round", "");
insertCmd.Parameters.AddWithValue("@Result", "");
insertCmd.Parameters.AddWithValue("@Moves", "");
insertCmd.Parameters.AddWithValue("@BlackPlayer", "");
insertCmd.Parameters.AddWithValue("@WhitePlayer", "");
insertCmd.Parameters.AddWithValue("@Name", "");
insertCmd.Parameters.AddWithValue("@Site", "");
insertCmd.Parameters.AddWithValue("@Date", "");
// iterate over games and insert them
foreach (var game in reader.GetGames())
{
insertCmd.Parameters["@Round"].Value = game.Round;
insertCmd.Parameters["@Result"].Value = game.Result;
insertCmd.Parameters["@Moves"].Value = game.Moves;
insertCmd.Parameters["@BlackPlayer"].Value = game.BlackPlayer;
insertCmd.Parameters["@WhitePlayer"].Value = game.WhitePlayer;
insertCmd.Parameters["@Name"].Value = game.Name;
insertCmd.Parameters["@Site"].Value = game.Site;
insertCmd.Parameters["@Date"].Value = game.Date;
insertCmd.ExecuteNonQuery();
WorkStepCompleted();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
