//Insert statement
public static long?Insert(S_Advert advert)
{
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 advert (bowlingcenter_id, advertisement, advertisement_url, advertisement_www) VALUES (@bowlingcenter_id, @advertisement, @advertisement_url, @advertisement_www)";
command.Parameters.AddWithValue("@bowlingcenter_id", Conversion.LongToSql(advert.bowlingcenterId));
command.Parameters.AddWithValue("@advertisement", Conversion.StringToSql(advert.advertisement));
command.Parameters.AddWithValue("@advertisement_url", Conversion.StringToSql(advert.advertisement_url));
command.Parameters.AddWithValue("@advertisement_www", Conversion.StringToSql(advert.advertisement_www));
//Execute command
command.ExecuteNonQuery();
lastInsertedId = command.LastInsertedId;
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Insert, Error inserting advert data: {0}", ex.Message));
}
return(lastInsertedId.Value);
}
//Update statement
public static void Update(S_Advert advert)
{
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 advert SET advertisement=@advertisement, advertisement_url=@advertisement_url, advertisement_www=@advertisement_www WHERE id=@id ";
command.Parameters.AddWithValue("@id", Conversion.LongToSql(advert.id));
command.Parameters.AddWithValue("@advertisement", Conversion.StringToSql(advert.advertisement));
command.Parameters.AddWithValue("@advertisement_url", Conversion.StringToSql(advert.advertisement_url));
command.Parameters.AddWithValue("@advertisement_www", Conversion.StringToSql(advert.advertisement_www));
//Execute command
command.ExecuteNonQuery();
//close connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("Update, Error updating advert data: {0}", ex.Message));
}
}
public AdvertisementInfo GetAdvertisement(string id)
{
logger.Debug(Settings.MethodName());
logger.Trace("Parameters: id: " + id);
AdvertisementInfo advertisementInfo = new AdvertisementInfo();
if (isCorrectUser(id))
{
try
{
S_Advert advert = AdvertManager.GetRandomAdvert();
if (advert != null)
{
advertisementInfo.advertisement = advert.advertisement;
advertisementInfo.advertisement_url = Conversion.UriToEscapedUri(advert.advertisement_url);
advertisementInfo.advertisement_www = Conversion.UriToEscapedUri(advert.advertisement_www);
}
}
catch
{
}
}
logger.Trace("Return: advertisement: " + advertisementInfo.advertisement);
logger.Trace("Return: advertisement_url: " + advertisementInfo.advertisement_url);
logger.Trace("Return: advertisement_www: " + advertisementInfo.advertisement_www);
return(advertisementInfo);
}
private static S_Advert DataToObject(MySqlDataReader dataReader)
{
S_Advert advert = new S_Advert();
advert.id = Conversion.SqlToLongOrNull(dataReader["id"]).Value;
advert.advertisement = Conversion.SqlToString(dataReader["advertisement"]);
advert.advertisement_url = Conversion.SqlToString(dataReader["advertisement_url"]);
advert.advertisement_www = Conversion.SqlToString(dataReader["advertisement_www"]);
advert.bowlingcenterId = Conversion.SqlToLongOrNull(dataReader["bowlingcenter_id"]).Value;
return(advert);
}
public ActionResult EditAdvert(long id)
{
S_Advert advert = AdvertManager.GetAdvertById(id);
AdvertModel advertModel = new AdvertModel();
advertModel.Id = advert.id;
advertModel.Advertisement = advert.advertisement;
advertModel.AdvertisementUrl = advert.advertisement_url;
advertModel.AdvertisementWWW = advert.advertisement_www;
return(View(advertModel));
}
public ActionResult DeleteAdvert(long id)
{
S_Advert advert = null;
try
{
advert = AdvertManager.GetAdvertById(id);
AdvertManager.Delete(id);
TempData["message"] = "De advertenttie is verwijderd.";
}
catch (Exception e)
{
TempData["error"] = e.Message;
}
return(RedirectToAction("adverts", "Bowlinghuis", new { id = advert.bowlingcenterId }));
}
public ActionResult EditAdvert(AdvertModel model)
{
if (ModelState.IsValid)
{
// Attempt to save the opentime
try
{
S_Advert advert = AdvertManager.GetAdvertById(model.Id);
advert.advertisement = model.Advertisement;
advert.advertisement_url = model.AdvertisementUrl;
advert.advertisement_www = model.AdvertisementWWW;
HttpPostedFileBase remoteFile = Request.Files["UploadedFile"];
if ((remoteFile != null) && (remoteFile.ContentLength > 0) && !string.IsNullOrEmpty(remoteFile.FileName))
{
string remoteFileName = remoteFile.FileName;
string remoteFileContentType = remoteFile.ContentType;
byte[] remoteFileBytes = new byte[remoteFile.ContentLength];
remoteFile.InputStream.Read(remoteFileBytes, 0, Convert.ToInt32(remoteFile.ContentLength));
var fileName = Path.GetFileName(remoteFile.FileName);
var path = Path.Combine(Server.MapPath("~/Banners"), fileName);
remoteFile.SaveAs(path);
string baseUrl = Request.Url.Scheme + "://" + Request.Url.Authority + Request.ApplicationPath.TrimEnd('/') + "/";
advert.advertisement_url = baseUrl + "/Banners/" + fileName;
}
AdvertManager.Update(advert);
TempData["message"] = "De advertentie is aangepast.";
return(RedirectToAction("adverts", "Bowlinghuis", new { id = advert.bowlingcenterId }));
}
catch (Exception e)
{
TempData["error"] = "Er is een fout opgetreden";
}
}
// If we got this far, something failed, redisplay form
return(View(model));
}
public static S_Advert GetAdvertById(long id)
{
S_Advert advert = 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 advert 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())
{
advert = DataToObject(dataReader);
}
//close Data Reader
dataReader.Close();
//close Connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetAdvertById, Error reading advert data: {0}", ex.Message));
}
return(advert);
}
public static S_Advert GetRandomAdvert()
{
S_Advert advert = 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 advert ORDER BY RAND() LIMIT 1";
//Create a data reader and Execute the command
MySqlDataReader dataReader = command.ExecuteReader();
//Read the data and store them in the list
if (dataReader.Read())
{
advert = DataToObject(dataReader);
}
//close Data Reader
dataReader.Close();
//close Connection
databaseconnection.CloseConnection();
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetRandomAdvert, Error reading advert data: {0}", ex.Message));
}
return(advert);
}
