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