public static void Update(MySqlConnection sqlConnection, S_Bowl bowl)
{
try
{
//create command and assign the query and connection from the constructor
MySqlCommand command = new MySqlCommand();
command.Connection = sqlConnection;
command.CommandText = "UPDATE bowl SET frameid=@frameid, bowlnumber=@bowlnumber, total=@total, isstrike=@isstrike, isspare=@isspare, issplit=@issplit, isgutter=@isgutter, isfoul=@isfoul, ismanuallymodified=@ismanuallymodified, pins=@pins WHERE id=@id ";
command.Parameters.AddWithValue("@id", Conversion.LongToSql(bowl.id));
command.Parameters.AddWithValue("@frameid", Conversion.LongToSql(bowl.frameId));
command.Parameters.AddWithValue("@bowlnumber", Conversion.IntToSql(bowl.bowlNumber));
command.Parameters.AddWithValue("@total", Conversion.IntToSql(bowl.total));
command.Parameters.AddWithValue("@isstrike", Conversion.BoolToSql(bowl.isStrike));
command.Parameters.AddWithValue("@isspare", Conversion.BoolToSql(bowl.isSpare));
command.Parameters.AddWithValue("@issplit", Conversion.BoolToSql(bowl.isSplit));
command.Parameters.AddWithValue("@isgutter", Conversion.BoolToSql(bowl.isGutter));
command.Parameters.AddWithValue("@isfoul", Conversion.BoolToSql(bowl.isFoul));
command.Parameters.AddWithValue("@ismanuallymodified", Conversion.BoolToSql(bowl.isManuallyModified));
command.Parameters.AddWithValue("@pins", Conversion.StringToSql(bowl.pins));
//Execute command
command.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.Error(string.Format("Update, Error updating bowl data: {0}", ex.Message));
}
}
public static long?Insert(MySqlConnection sqlConnection, S_Bowl bowl)
{
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 bowl ( frameid, bowlnumber, total, isstrike, isspare, issplit, isgutter, isfoul, ismanuallymodified, pins) " +
"VALUES (@frameid, @bowlnumber, @total, @isstrike, @isspare, @issplit, @isgutter, @isfoul, @ismanuallymodified, @pins)";
command.Parameters.AddWithValue("@frameid", Conversion.LongToSql(bowl.frameId));
command.Parameters.AddWithValue("@bowlnumber", Conversion.IntToSql(bowl.bowlNumber));
command.Parameters.AddWithValue("@total", Conversion.IntToSql(bowl.total));
command.Parameters.AddWithValue("@isstrike", Conversion.BoolToSql(bowl.isStrike));
command.Parameters.AddWithValue("@isspare", Conversion.BoolToSql(bowl.isSpare));
command.Parameters.AddWithValue("@issplit", Conversion.BoolToSql(bowl.isSplit));
command.Parameters.AddWithValue("@isgutter", Conversion.BoolToSql(bowl.isGutter));
command.Parameters.AddWithValue("@isfoul", Conversion.BoolToSql(bowl.isFoul));
command.Parameters.AddWithValue("@ismanuallymodified", Conversion.BoolToSql(bowl.isManuallyModified));
command.Parameters.AddWithValue("@pins", Conversion.StringToSql(bowl.pins));
//Execute command
command.ExecuteNonQuery();
lastInsertedId = command.LastInsertedId;
}
catch (Exception ex)
{
logger.Error(string.Format("Insert, Error inserting bowl data: {0}", ex.Message));
}
return(lastInsertedId.Value);
}
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);
}
}
private static S_Bowl DataToObject(MySqlDataReader dataReader)
{
S_Bowl bowl = new S_Bowl();
bowl.id = Conversion.SqlToIntOrNull(dataReader["id"]).Value;
bowl.frameId = Conversion.SqlToIntOrNull(dataReader["frameid"]).Value;
bowl.bowlNumber = Conversion.SqlToIntOrNull(dataReader["bowlnumber"]).Value;
bowl.total = Conversion.SqlToIntOrNull(dataReader["total"]);
bowl.isStrike = Conversion.SqlToBoolOrNull(dataReader["isstrike"]).Value;
bowl.isSpare = Conversion.SqlToBoolOrNull(dataReader["isspare"]).Value;
bowl.isSplit = Conversion.SqlToBoolOrNull(dataReader["issplit"]).Value;
bowl.isGutter = Conversion.SqlToBoolOrNull(dataReader["isgutter"]).Value;
bowl.isFoul = Conversion.SqlToBoolOrNull(dataReader["isfoul"]).Value;
bowl.isManuallyModified = Conversion.SqlToBoolOrNull(dataReader["ismanuallymodified"]).Value;
bowl.pins = Conversion.SqlToString(dataReader["pins"]);
return(bowl);
}
//Update statement
public static void Update(S_Bowl bowl)
{
try
{
DatabaseConnection databaseconnection = new DatabaseConnection();
//open connection
if (databaseconnection.OpenConnection())
{
Update(databaseconnection.getConnection(), bowl);
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Update, Error updating bowl data: {0}", ex.Message));
}
}
public static S_Bowl GetBowlById(long id)
{
S_Bowl bowl = 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 bowl 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())
{
bowl = DataToObject(dataReader);
}
//close Data Reader
dataReader.Close();
//close Connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetBowlById, Error reading bowl data: {0}", ex.Message));
}
return(bowl);
}
//Insert statement
public static long?Insert(S_Bowl bowl)
{
long?lastInsertedId = null;
try
{
DatabaseConnection databaseconnection = new DatabaseConnection();
//open connection
if (databaseconnection.OpenConnection())
{
lastInsertedId = Insert(databaseconnection.getConnection(), bowl);
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Insert, Error inserting bowl data: {0}", ex.Message));
}
return(lastInsertedId.Value);
}
private static S_Bowl GetBowl(XmlNode xmlBowlNode)
{
S_Bowl bowl = new S_Bowl();
try
{
#region xml example
//<Bowl>
// <Total>8</Total>
// <IsStrike>No</IsStrike>
// <IsSpare>No</IsSpare>
// <IsSplit>Yes</IsSplit>
// <IsGutter>No</IsGutter>
// <IsFoul>No</IsFoul>
// <IsManuallyModified>No</IsManuallyModified>
// <Pins>1,4,5,6,7,8,9,10</Pins>
//</Bowl>
#endregion
// all child nodes in Event
XmlNodeList xmlBowlChildNodeList = xmlBowlNode.SelectNodes("*");
foreach (XmlNode xmlBowlChildNode in xmlBowlChildNodeList)
{
switch (xmlBowlChildNode.Name)
{
case "Total": //totaal van de bowls in dit frame, dus niet de worp zelf
bowl.total = Conversion.StringToInt(xmlBowlChildNode.InnerText).Value;
break;
case "IsStrike":
bowl.isStrike = Conversion.StringToBoolQubica(xmlBowlChildNode.InnerText).Value;
break;
case "IsSpare":
bowl.isSpare = Conversion.StringToBoolQubica(xmlBowlChildNode.InnerText).Value;
break;
case "IsSplit":
bowl.isSplit = Conversion.StringToBoolQubica(xmlBowlChildNode.InnerText).Value;
break;
case "IsGutter":
bowl.isGutter = Conversion.StringToBoolQubica(xmlBowlChildNode.InnerText).Value;
break;
case "IsFoul":
bowl.isFoul = Conversion.StringToBoolQubica(xmlBowlChildNode.InnerText).Value;
break;
case "IsManuallyModified":
bowl.isManuallyModified = Conversion.StringToBoolQubica(xmlBowlChildNode.InnerText).Value;
break;
case "Pins":
bowl.pins = Conversion.StringToString(xmlBowlChildNode.InnerText);
break;
}
}
}
catch (Exception ex)
{
logger.Error(String.Format("Error parsing Bowl from response: {0}", ex.Message));
}
return(bowl);
}
