//Update statement
public static void Update(S_Opentime 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 opentime SET bowlingcenterid=@bowlingcenterid, day=@day, opentime=@opentime, closetime=@closetime WHERE id=@id ";
command.Parameters.AddWithValue("@id", Conversion.LongToSql(scores.id));
command.Parameters.AddWithValue("@bowlingcenterid", Conversion.LongToSql(scores.bowlingCenterId));
command.Parameters.AddWithValue("@day", Conversion.StringToSql(scores.day.ToString()));
command.Parameters.AddWithValue("@opentime", Conversion.StringToSql(scores.openTime));
command.Parameters.AddWithValue("@closetime", Conversion.StringToSql(scores.closeTime));
//Execute command
command.ExecuteNonQuery();
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Update, Error updating opentime data: {0}", ex.Message));
}
}
//Insert statement
public static long?Insert(S_Opentime opentime)
{
long?lastInsertedId = null;
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 = "INSERT INTO opentime (bowlingcenterid, day, opentime, closetime) VALUES (@bowlingcenterid, @day, @opentime, @closetime)";
command.Parameters.AddWithValue("@bowlingcenterid", Conversion.LongToSql(opentime.bowlingCenterId));
command.Parameters.AddWithValue("@day", Conversion.StringToSql(opentime.day.ToString()));
command.Parameters.AddWithValue("@opentime", Conversion.StringToSql(opentime.openTime));
command.Parameters.AddWithValue("@closetime", Conversion.StringToSql(opentime.closeTime));
//Execute command
command.ExecuteNonQuery();
lastInsertedId = command.LastInsertedId;
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Insert, Error inserting opentime data: {0}", ex.Message));
}
return(lastInsertedId.Value);
}
public ActionResult EditOpentime(OpentimeModel model)
{
if (ModelState.IsValid)
{
// Attempt to save the opentime
try
{
S_Opentime opentime = OpentimeManager.GetOpentimeById(model.Id);
opentime.day = (Day)Enum.Parse(typeof(Day), model.Day);
opentime.openTime = model.Opentime;
opentime.closeTime = model.Closetime;
OpentimeManager.Update(opentime);
TempData["message"] = "De openingstijd is aangepast.";
return(RedirectToAction("opentimes", "Bowlinghuis", new { id = opentime.bowlingCenterId }));
}
catch (Exception e)
{
TempData["error"] = "Er is een fout opgetreden";
}
}
// If we got this far, something failed, redisplay form
return(View(model));
}
private static S_Opentime DataToObject(MySqlDataReader dataReader)
{
S_Opentime opentime = new S_Opentime();
opentime.id = Conversion.SqlToLongOrNull(dataReader["id"]).Value;
opentime.bowlingCenterId = Conversion.SqlToLongOrNull(dataReader["bowlingcenterid"]).Value;
opentime.day = (Day)Enum.Parse(typeof(Day), Conversion.SqlToString(dataReader["day"]));
opentime.openTime = Conversion.SqlToString(dataReader["opentime"]);
opentime.closeTime = Conversion.SqlToString(dataReader["closetime"]);
return(opentime);
}
public ActionResult EditOpentime(long id)
{
S_Opentime opentime = OpentimeManager.GetOpentimeById(id);
OpentimeModel opentimeModel = new OpentimeModel();
opentimeModel.Id = opentime.id;
opentimeModel.Day = opentime.day.ToString();
opentimeModel.Opentime = opentime.openTime;
opentimeModel.Closetime = opentime.closeTime;
SelectListItem monday = new SelectListItem();
monday.Value = "Monday";
monday.Text = "Maandag";
SelectListItem tuesday = new SelectListItem();
tuesday.Value = "Tuesday";
tuesday.Text = "Dinsdag";
SelectListItem wednesday = new SelectListItem();
wednesday.Value = "Wednesday";
wednesday.Text = "Woensdag";
SelectListItem thursday = new SelectListItem();
thursday.Value = "Thursday";
thursday.Text = "Donderdag";
SelectListItem friday = new SelectListItem();
friday.Value = "Friday";
friday.Text = "Vrijdag";
SelectListItem saterday = new SelectListItem();
saterday.Value = "Saterday";
saterday.Text = "Zaterdag";
SelectListItem sunday = new SelectListItem();
sunday.Value = "Sunday";
sunday.Text = "Zondag";
opentimeModel.Days = new Collection <SelectListItem>()
{
monday, tuesday, wednesday, thursday, friday, saterday, sunday
};
return(View(opentimeModel));
}
public ActionResult DeleteOpentime(long id)
{
S_Opentime opentime = null;
try
{
opentime = OpentimeManager.GetOpentimeById(id);
OpentimeManager.Delete(id);
TempData["message"] = "De openingstijd is verwijderd.";
}
catch (Exception e)
{
TempData["error"] = e.Message;
}
return(RedirectToAction("opentimes", "Bowlinghuis", new { id = opentime.bowlingCenterId }));
}
public static S_Opentime GetOpentimeById(long id)
{
S_Opentime 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 opentime 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();
//close Connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetScoreById, Error reading opentimes data: {0}", ex.Message));
}
return(scores);
}
