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 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); }
//Insert statement public static long?Insert(S_User user) { long?lastInsertedId = null; try { DatabaseConnection databaseconnection = new DatabaseConnection(); if (!UserExistByEmail(user.email)) { if (!UserExistByFrequentBowlerNumber(user.frequentbowlernumber)) { //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 = "INSERT INTO user (name, password, roleid, logindatetime, email, address, username, city, ismember, membernumber, isregistrationconfirmed, frequentbowlernumber) VALUES (@name, @password, @roleid, @logindatetime, @email, @address, @username, @city, @ismember, @membernumber, @isregistrationconfirmed, @frequentbowlernumber)"; command.Parameters.AddWithValue("@name", Conversion.StringToSql(user.name)); command.Parameters.AddWithValue("@password", Conversion.StringToSql(user.password)); command.Parameters.AddWithValue("@roleid", Conversion.IntToSql((int)user.roleid)); command.Parameters.AddWithValue("@logindatetime", Conversion.DoubleToSql(user.logindatetime)); command.Parameters.AddWithValue("@email", Conversion.StringToSql(user.email)); command.Parameters.AddWithValue("@address", Conversion.StringToSql(user.address)); command.Parameters.AddWithValue("@username", Conversion.StringToSql(user.username)); command.Parameters.AddWithValue("@city", Conversion.StringToSql(user.city)); command.Parameters.AddWithValue("@ismember", Conversion.BoolToSql(user.isMember)); command.Parameters.AddWithValue("@membernumber", Conversion.IntToSql(user.memberNumber)); command.Parameters.AddWithValue("@isregistrationconfirmed", Conversion.BoolToSql(user.isRegistrationConfirmed)); command.Parameters.AddWithValue("@frequentbowlernumber", Conversion.LongToSql(user.frequentbowlernumber)); //Execute command command.ExecuteNonQuery(); lastInsertedId = command.LastInsertedId; //close connection databaseconnection.CloseConnection(); } } else { throw (new Exception("Frequent bowler nummer is reeds in gebruik")); } } else { throw (new Exception("Email adres is reeds in gebruik")); } } catch (Exception ex) { logger.Error(string.Format("Insert, Error inserting uUser data: {0}", ex.Message)); throw; } return(lastInsertedId); }
//Update statement public static void Update(S_User user) { 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 user SET name=@name, password=@password, roleid=@roleid, logindatetime=@logindatetime, email=@email, address=@address, username=@username, city=@city, ismember=@ismember, membernumber=@membernumber, isregistrationconfirmed=@isregistrationconfirmed, frequentbowlernumber=@frequentbowlernumber WHERE id=@id "; command.Parameters.AddWithValue("@id", Conversion.LongToSql(user.id)); command.Parameters.AddWithValue("@name", Conversion.StringToSql(user.name)); command.Parameters.AddWithValue("@password", Conversion.StringToSql(user.password)); command.Parameters.AddWithValue("@roleid", Conversion.IntToSql((int)user.roleid)); command.Parameters.AddWithValue("@logindatetime", Conversion.DoubleToSql(user.logindatetime)); command.Parameters.AddWithValue("@email", Conversion.StringToSql(user.email)); command.Parameters.AddWithValue("@address", Conversion.StringToSql(user.address)); command.Parameters.AddWithValue("@username", Conversion.StringToSql(user.username)); command.Parameters.AddWithValue("@city", Conversion.StringToSql(user.city)); command.Parameters.AddWithValue("@ismember", Conversion.BoolToSql(user.isMember)); command.Parameters.AddWithValue("@membernumber", Conversion.IntToSql(user.memberNumber)); command.Parameters.AddWithValue("@isregistrationconfirmed", Conversion.BoolToSql(user.isRegistrationConfirmed)); command.Parameters.AddWithValue("@frequentbowlernumber", Conversion.LongToSql(user.frequentbowlernumber)); //Execute command command.ExecuteNonQuery(); //close connection databaseconnection.CloseConnection(); } } catch (Exception ex) { logger.Error(string.Format("Update, Error updating user data: {0}", ex.Message)); } }
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)); } }