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