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