public static S_Scores GetScoreById(MySqlConnection sqlConnection, long id)
{
S_Scores scores = null;
try
{
//Create Command
MySqlCommand command = new MySqlCommand();
command.Connection = sqlConnection;
command.CommandText = "SELECT * FROM scores 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())
{
scores = DataToObject(dataReader);
}
//close Data Reader
dataReader.Close();
}
catch (Exception ex)
{
logger.Error(string.Format("GetScoreById, Error reading scores data: {0}", ex.Message));
}
return(scores);
}
//Update statement
public static void Update(S_Scores scores)
{
try
{
DatabaseConnection databaseconnection = new DatabaseConnection();
//open connection
if (databaseconnection.OpenConnection())
{
//create command and assign the query and connection from the constructor
MySqlCommand command = new MySqlCommand();
command.Connection = databaseconnection.getConnection();
command.CommandText = "UPDATE scores SET bowlingcenterid=@bowlingcenterid WHERE id=@id ";
command.Parameters.AddWithValue("@id", Conversion.LongToSql(scores.id));
command.Parameters.AddWithValue("@bowlingcenterid", Conversion.LongToSql(scores.bowlingCenterId));
//Execute command
command.ExecuteNonQuery();
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Update, Error updating scores data: {0}", ex.Message));
}
}
private static S_Scores DataToObject(MySqlDataReader dataReader)
{
S_Scores score = new S_Scores();
score.id = Conversion.SqlToIntOrNull(dataReader["id"]).Value;
score.bowlingCenterId = Conversion.SqlToIntOrNull(dataReader["bowlingcenterid"]).Value;
return(score);
}
public static S_Scores GetScores(string xmlString)
{
logger.Debug(xmlString);
S_Scores scores = new S_Scores();
scores.Events = new List <S_Event>();
try
{
#region xml example
//<Scores>
// <Events>
// <Event>....</Event>
// <Event>....</Event>
// </Events>
//</Scores>
#endregion
if (xmlString != null)
{
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.LoadXml(xmlString);
XmlNodeList xmlEventNodeList = xmlDocument.SelectNodes("/Scores/Events/*");
// loop over all event nodes
foreach (XmlNode xmlEventNode in xmlEventNodeList)
{
scores.Events.Add(GetEvent(xmlEventNode));
}
}
else
{
logger.Warn("XML string is null");
}
}
catch (Exception ex)
{
logger.Error(String.Format("Error parsing scores from response: {0}", ex.Message));
}
return(scores);
}
public static void Save(MySqlConnection sqlConnection, long bowlingCenterId, S_Scores score)
{
long?scoreId = null;
score.bowlingCenterId = bowlingCenterId;
if (!ScoresManager.ScoresExistByBowlingCenterId(sqlConnection, bowlingCenterId, out scoreId))
{
score.id = Insert(sqlConnection, score).Value;
}
else
{
score.id = ScoresManager.GetScoreById(sqlConnection, scoreId.Value).id;
}
foreach (S_Event bowlEvent in score.Events)
{
EventManager.Save(sqlConnection, score, bowlEvent);
}
}
public static S_Scores GetScoresByBowlingCenterId(long bowlingCenterId)
{
S_Scores scores = 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 scores WHERE bowlingcenterid=@bowlingcenterid";
command.Parameters.AddWithValue("@bowlingcenterid", Conversion.LongToSql(bowlingCenterId));
//Create a data reader and Execute the command
MySqlDataReader dataReader = command.ExecuteReader();
//Read the data and store them in the list
if (dataReader.Read())
{
scores = DataToObject(dataReader);
}
//close Data Reader
dataReader.Close();
//close Connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetScoresByBowlingCenterId, Error reading scores data: {0}", ex.Message));
}
return(scores);
}
public static void Save(MySqlConnection sqlConnection, S_Scores score, S_Event bowlEvent)
{
long?eventId = null;
bowlEvent.scoresId = score.id;
if (!EventManager.EventExistByScoreIdAndEventCode(sqlConnection, score.id, bowlEvent.eventCode, out eventId))
{
bowlEvent.id = Insert(sqlConnection, bowlEvent).Value;
}
else
{
bowlEvent.id = eventId.Value;
// closeDateTime and Status might have changed
Update(sqlConnection, bowlEvent);
}
foreach (S_Game game in bowlEvent.games)
{
GameManager.Save(sqlConnection, bowlEvent, game);
}
}
public static long?Insert(MySqlConnection sqlConnection, S_Scores scores)
{
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 scores (bowlingcenterid) VALUES (@bowlingcenterid)";
command.Parameters.AddWithValue("@bowlingcenterid", Conversion.LongToSql(scores.bowlingCenterId));
//Execute command
command.ExecuteNonQuery();
lastInsertedId = command.LastInsertedId;
}
catch (Exception ex)
{
logger.Error(string.Format("Insert, Error inserting scores data: {0}", ex.Message));
}
return(lastInsertedId.Value);
}
public static S_Scores GetScoreById(long id)
{
S_Scores scores = null;
try
{
DatabaseConnection databaseconnection = new DatabaseConnection();
//Open connection
if (databaseconnection.OpenConnection())
{
scores = GetScoreById(databaseconnection.getConnection(), id);
//close Connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetScoreById, Error reading scores data: {0}", ex.Message));
}
return(scores);
}
//Insert statement
public static long?Insert(S_Scores scores)
{
long?lastInsertedId = null;
try
{
DatabaseConnection databaseconnection = new DatabaseConnection();
//open connection
if (databaseconnection.OpenConnection())
{
lastInsertedId = Insert(databaseconnection.getConnection(), scores);
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Insert, Error inserting scores data: {0}", ex.Message));
}
return(lastInsertedId.Value);
}