public static long?Insert(MySqlConnection sqlConnection, S_Frame frame)
{
long?lastInsertedId = null;
try
{
//create command and assign the query and connection from the constructor
MySqlCommand command = new MySqlCommand();
command.Connection = sqlConnection;
command.CommandText = "INSERT INTO frame ( gameid, framenumber, progressivetotal, isconvertedsplit) " +
"VALUES (@gameid, @framenumber, @progressivetotal, @isconvertedsplit)";
command.Parameters.AddWithValue("@gameid", Conversion.LongToSql(frame.gameId));
command.Parameters.AddWithValue("@framenumber", Conversion.IntToSql(frame.frameNumber));
command.Parameters.AddWithValue("@progressivetotal", Conversion.IntToSql(frame.progressiveTotal));
command.Parameters.AddWithValue("@isconvertedsplit", Conversion.BoolToSql(frame.isConvertedSplit));
//Execute command
command.ExecuteNonQuery();
lastInsertedId = command.LastInsertedId;
}
catch (Exception ex)
{
logger.Error(string.Format("Insert, Error inserting frame data: {0}", ex.Message));
}
return(lastInsertedId.Value);
}
public static void Save(MySqlConnection sqlConnection, S_Game game, S_Frame frame)
{
long?frameId = null;
frame.gameId = game.id;
if (!FrameManager.FrameExistByGameIdAndFrameNumber(sqlConnection, game.id, frame.frameNumber, out frameId))
{
frame.id = Insert(sqlConnection, frame).Value;
}
else
{
frame.id = frameId.Value;
// update progressiveTotal and isConvertedsplit
Update(sqlConnection, frame);
}
if (frame.bowl1 != null)
{
frame.bowl1.bowlNumber = 1;
BowlManager.Save(sqlConnection, frame, frame.bowl1);
}
if (frame.bowl2 != null)
{
frame.bowl2.bowlNumber = 2;
BowlManager.Save(sqlConnection, frame, frame.bowl2);
}
if (frame.bowl3 != null)
{
frame.bowl3.bowlNumber = 3;
BowlManager.Save(sqlConnection, frame, frame.bowl3);
}
}
private static S_Frame GetFrame(XmlNode xmlFrameNode)
{
S_Frame frame = new S_Frame();
try
{
#region xml example
//<Frame>
// <FrameNumber>1</FrameNumber>
// <ProgressiveTotal>8</ProgressiveTotal>
// <IsConvertedSplit>No</IsConvertedSplit>
// <Bowl1>..</Bowl1>
// <Bowl2>..</Bowl2>
// <Bowl3>..</Bowl3>
//</Frame>
#endregion
// all child nodes in Frame
XmlNodeList xmlFrameChildNodeList = xmlFrameNode.SelectNodes("*");
foreach (XmlNode xmlFrameChildNode in xmlFrameChildNodeList)
{
switch (xmlFrameChildNode.Name)
{
case "FrameNumber":
frame.frameNumber = Conversion.StringToInt(xmlFrameChildNode.InnerText).Value;
break;
case "ProgressiveTotal":
frame.progressiveTotal = Conversion.StringToInt(xmlFrameChildNode.InnerText).Value;
break;
case "IsConvertedSplit":
frame.isConvertedSplit = Conversion.StringToBoolQubica(xmlFrameChildNode.InnerText).Value;
break;
case "Bowl1":
frame.bowl1 = GetBowl(xmlFrameChildNode);
break;
case "Bowl2":
frame.bowl2 = GetBowl(xmlFrameChildNode);
break;
case "Bowl3":
frame.bowl3 = GetBowl(xmlFrameChildNode);
break;
}
}
}
catch (Exception ex)
{
logger.Error(String.Format("Error parsing Frame from response: {0}", ex.Message));
}
return(frame);
}
private static S_Frame DataToObject(MySqlDataReader dataReader)
{
S_Frame frame = new S_Frame();
frame.id = Conversion.SqlToIntOrNull(dataReader["id"]).Value;
frame.gameId = Conversion.SqlToIntOrNull(dataReader["gameid"]).Value;
frame.frameNumber = Conversion.SqlToIntOrNull(dataReader["framenumber"]).Value;
frame.progressiveTotal = Conversion.SqlToIntOrNull(dataReader["progressivetotal"]);
frame.isConvertedSplit = Conversion.SqlToBoolOrNull(dataReader["isconvertedsplit"]).Value;
return(frame);
}
public static void Save(MySqlConnection sqlConnection, S_Frame frame, S_Bowl bowl)
{
long?bowlId = null;
bowl.frameId = frame.id;
if (!BowlManager.BowlExistByFrameIdAndBowlNumber(sqlConnection, frame.id, bowl.bowlNumber, out bowlId))
{
bowl.id = Insert(sqlConnection, bowl).Value;
}
else
{
bowl.id = bowlId.Value;
// Update total, is... and pins
Update(sqlConnection, bowl);
}
}
//Update statement
public static void Update(S_Frame frame)
{
try
{
DatabaseConnection databaseconnection = new DatabaseConnection();
//open connection
if (databaseconnection.OpenConnection())
{
Update(databaseconnection.getConnection(), frame);
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Update, Error updating frame data: {0}", ex.Message));
}
}
public static S_Frame GetFrameById(long id)
{
S_Frame frame = null;
try
{
DatabaseConnection databaseconnection = new DatabaseConnection();
//Open connection
if (databaseconnection.OpenConnection())
{
//Create Command
MySqlCommand command = new MySqlCommand();
command.Connection = databaseconnection.getConnection();
command.CommandText = "SELECT * FROM frame WHERE id=@id";
command.Parameters.AddWithValue("@id", Conversion.LongToSql(id));
//Create a data reader and Execute the command
MySqlDataReader dataReader = command.ExecuteReader();
//Read the data and store them in the list
if (dataReader.Read())
{
frame = DataToObject(dataReader);
}
//close Data Reader
dataReader.Close();
//close Connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetFrameById, Error reading frame data: {0}", ex.Message));
}
return(frame);
}
public static void Update(MySqlConnection sqlConnection, S_Frame frame)
{
try
{
//create command and assign the query and connection from the constructor
MySqlCommand command = new MySqlCommand();
command.Connection = sqlConnection;
command.CommandText = "UPDATE frame SET gameid=@gameid, framenumber=@framenumber, progressivetotal=@progressivetotal, isconvertedsplit=@isconvertedsplit WHERE id=@id ";
command.Parameters.AddWithValue("@id", Conversion.LongToSql(frame.id));
command.Parameters.AddWithValue("@gameid", Conversion.LongToSql(frame.gameId));
command.Parameters.AddWithValue("@framenumber", Conversion.IntToSql(frame.frameNumber));
command.Parameters.AddWithValue("@progressivetotal", Conversion.IntToSql(frame.progressiveTotal));
command.Parameters.AddWithValue("@isconvertedsplit", Conversion.BoolToSql(frame.isConvertedSplit));
//Execute command
command.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.Error(string.Format("Update, Error updating frame data: {0}", ex.Message));
}
}
//Insert statement
public static long?Insert(S_Frame frame)
{
long?lastInsertedId = null;
try
{
DatabaseConnection databaseconnection = new DatabaseConnection();
//open connection
if (databaseconnection.OpenConnection())
{
lastInsertedId = Insert(databaseconnection.getConnection(), frame);
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Insert, Error inserting frame data: {0}", ex.Message));
}
return(lastInsertedId.Value);
}