ChangeResult IPostingRepository.Create(ItemPosting itemposting, bool archive) { string commandText = "INSERT INTO [dbo].[ArtPostingItems]" + "([Filename]" + ",[Title]" + ",[Shortname]" + ",[Header]" + ",[Description]" + ",[Size]" + ",[Price]" + ",[Archive_flag]" + ",[Order]) " + "VALUES " + "( @filename" + ", @title" + ", @shortName" + ", @header" + ", @desc" + ", @size" + ", @price" + ", @archive" + ", @order)"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { try { SqlDataAdapter adapter = new SqlDataAdapter(); adapter.UpdateCommand = new SqlCommand(commandText, connection, transaction); adapter = addPostingParamsToAdapter(adapter, itemposting, archive); updateOrderValues(connection, transaction, archive, true); adapter.UpdateCommand.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); return new ChangeResult(false, "Error inserting posting with filename: " + itemposting.FileName + ": " + ex.Message, HTTP_INTERNAL_SERVER_ERROR); } } } return new ChangeResult(true, "Inserted posting with filename: " + itemposting.FileName, HTTP_SUCCESS); }
ChangeResult IPostingRepository.Delete(ItemPosting itemposting) { bool archive = itemposting.Archive_Flag == true; int deleted = 0; string commandText = "DELETE FROM [dbo].[ArtPostingItems] WHERE [Filename] = @filename"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { try { SqlDataAdapter adapter = new SqlDataAdapter(); adapter.DeleteCommand = new SqlCommand(commandText, connection, transaction); SqlParameter filenameParam = new SqlParameter("@filename", SqlDbType.NVarChar); filenameParam.Value = itemposting.FileName.Normalise(); adapter.DeleteCommand.Parameters.Add(filenameParam); updateOrderValues(connection, transaction, archive, false, itemposting.Order); deleted = adapter.DeleteCommand.ExecuteNonQuery(); if (deleted > 0) { transaction.Commit(); return new ChangeResult(true, "Posting: " + itemposting.FileName + " was deleted from the database", HTTP_SUCCESS); } else { transaction.Rollback(); return new ChangeResult(false, "Posting: " + itemposting.FileName + " could not be deleted from the database", HTTP_INTERNAL_SERVER_ERROR); } } catch (Exception ex) { transaction.Rollback(); return new ChangeResult(false, "Posting: " + itemposting.FileName + " could not be deleted from the database - error: " + ex.Message, HTTP_INTERNAL_SERVER_ERROR); } } } }
ItemPosting selectPosting(int id, string selectString, string selectField) { try { ItemPosting posting = new ItemPosting(); string commandText = (!string.IsNullOrEmpty(selectString)) ? "SELECT [Id],[Filename],[Title],[Shortname],[Header], " + "[Description],[Size],[Price],[Archive_Flag] FROM [dbo].[ArtPostingItems]" + " WHERE (Id = @Id) AND (" + selectField + " = '' OR " + selectField + " = @selectValue" : "SELECT[Id],[Filename],[Title],[Shortname],[Header], " + "[Description],[Size],[Price],[Archive_Flag] FROM [dbo].[ArtPostingItems]" + " WHERE (Id = @Id)"; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(commandText, connection); command.Parameters.Add(new SqlParameter("@Id", id)); command.Parameters.Add(new SqlParameter("@selectValue", selectString)); connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { posting = new ItemPosting( reader["Id"].ToString(), reader["Order"].ToString(), reader["Filename"].ToString(), Path.Combine(webSafePictureFolder, reader["Filename"].ToString()), reader["Title"].ToString(), reader["Shortname"].ToString(), reader["Header"].ToString(), reader["Description"].ToString(), reader["Size"].ToString(), reader["Price"].ToString(), reader["Archive_Flag"].ToString() ); } return posting; } } catch (Exception ex) { throw (new Exception("Error retrieving item posting with id: " + id.ToString(), ex)); } }
private SqlDataAdapter addPostingParamsToAdapter(SqlDataAdapter _adapter, ItemPosting itemposting, bool archived) { SqlDataAdapter adapter = _adapter; SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int); idParam.Value = itemposting.Id; adapter.UpdateCommand.Parameters.Add(idParam); SqlParameter orderParam = new SqlParameter("@order", SqlDbType.Int); orderParam.Value = itemposting.Order; adapter.UpdateCommand.Parameters.Add(orderParam); SqlParameter filenameParam = new SqlParameter("@filename", SqlDbType.NVarChar); filenameParam.Value = itemposting.FileName; adapter.UpdateCommand.Parameters.Add(filenameParam); SqlParameter titleParam = new SqlParameter("@title", SqlDbType.NVarChar); titleParam.Value = itemposting.Title; adapter.UpdateCommand.Parameters.Add(titleParam); SqlParameter shortnameParam = new SqlParameter("@shortname", SqlDbType.NVarChar); shortnameParam.Value = itemposting.ShortName; adapter.UpdateCommand.Parameters.Add(shortnameParam); SqlParameter headerParam = new SqlParameter("@header", SqlDbType.NVarChar); headerParam.Value = itemposting.Header; adapter.UpdateCommand.Parameters.Add(headerParam); SqlParameter descParam = new SqlParameter("@desc", SqlDbType.NVarChar); descParam.Value = itemposting.Description; adapter.UpdateCommand.Parameters.Add(descParam); SqlParameter sizeParam = new SqlParameter("@size", SqlDbType.NVarChar); sizeParam.Value = itemposting.Size; adapter.UpdateCommand.Parameters.Add(sizeParam); SqlParameter priceParam = new SqlParameter("@price", SqlDbType.NVarChar); priceParam.Value = itemposting.Price; adapter.UpdateCommand.Parameters.Add(priceParam); SqlParameter archiveParam = new SqlParameter("@archive", SqlDbType.NVarChar); archiveParam.Value = archived.ToString(); adapter.UpdateCommand.Parameters.Add(archiveParam); return adapter; }
ChangeResult IPostingRepository.Update(ItemPosting itemposting, bool archived) { try { string commandText = "Update [dbo].[ArtPostingItems] " + "SET [Order] = @order," + "[Filename] = @filename, " + "[Title] = @title, " + "[Shortname] = @shortname, " + "[Header] = @header, " + "[Description] = @desc, " + "[Size] = @size, " + "[Price] = @price, " + "[Archive_flag] = @archive" + " WHERE id = @id"; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlDataAdapter adapter = new SqlDataAdapter(); connection.Open(); adapter.UpdateCommand = connection.CreateCommand(); adapter.UpdateCommand.CommandText = commandText; adapter = addPostingParamsToAdapter(adapter, itemposting, archived); adapter.UpdateCommand.ExecuteNonQuery(); } return new ChangeResult(true, "Posting: " + itemposting.Id.ToString() + " was updated", HTTP_SUCCESS); } catch (Exception ex) { return new ChangeResult(false, "Posting: " + itemposting.Id.ToString() + " could not be updated: " + ex.Message, HTTP_INTERNAL_SERVER_ERROR); } }
/// <summary> /// Exchange the order values of posting1 and posting2 /// </summary> /// <param name="posting1"></param> /// <param name="posting2"></param> /// <param name="archived"></param> /// <returns></returns> ChangeResult IPostingRepository.ExchangeOrders(ItemPosting posting1, ItemPosting posting2, bool archived) { int order1 = posting1.Order; int order2 = posting2.Order; ChangeResult result = new ChangeResult(); string commandText = "Update [dbo].[ArtPostingItems] " + "SET [Order] = @neworder " + "WHERE Id = @id"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { int updated = 0; try { SqlDataAdapter adapter = new SqlDataAdapter(); // update #1: set 1 to 2 adapter.UpdateCommand = new SqlCommand(commandText, connection, transaction); SqlParameter newOrderParam = new SqlParameter("@neworder", SqlDbType.Int); newOrderParam.Value = order2; SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int); idParam.Value = posting1.Id; adapter.UpdateCommand.Parameters.Add(newOrderParam); adapter.UpdateCommand.Parameters.Add(idParam); updated = adapter.UpdateCommand.ExecuteNonQuery(); // update #2: set 2 to 1 if (updated > 0) { updated = 0; newOrderParam.Value = order1; idParam.Value = posting2.Id; updated = adapter.UpdateCommand.ExecuteNonQuery(); } // commit if (updated > 0) { transaction.Commit(); } else { throw new Exception("There was a problem changing the orders of " + posting1.FileName + " and " + posting2.FileName); } } catch (Exception ex) { transaction.Rollback(); return new ChangeResult(false, ex.Message, HTTP_INTERNAL_SERVER_ERROR); } } result = new ChangeResult(true, "Promoted: " + posting1.FileName.Normalise(), HTTP_SUCCESS); return result; } }
public ChangeResult RemoveFromDisplay(ItemPosting posting) { throw new NotImplementedException(); }
ChangeResult IPostingRepository.Update(ItemPosting posting, bool archived) { throw new NotImplementedException(); }
public ChangeResult MovePicture(string filepath, bool archivedestination, bool displaydestination) { // filepaths / names brought in from ajax call will have %20 spaces and will not have JS escaped single quotes string filename = Utility.GetFilenameFromFilepath(filepath).Normalise(); // default constructor gives failed results ChangeResult removeResult = new ChangeResult(); ChangeResult insertResult = new ChangeResult(); // initialising variables PictureFileRecord pfr = new PictureFileRecord(filepath); PictureFileRecord.StatusType source; List<ItemPostingViewModel> postingVMs = new List<ItemPostingViewModel>(); // getting current database contents postingVMs.AddRange(ArchivePostings().ToList()); postingVMs.AddRange(ShopPostings().ToList()); // getting full info on item to move ItemPostingViewModel moveItem = postingVMs.FirstOrDefault(x => x.ItemPosting.FileName.ToUpper().Normalise() == filename.ToUpper()); ItemPosting posting = new ItemPosting(); // determine current location of move item if (moveItem == null) { source = PictureFileRecord.StatusType.NotDisplayed; } else { posting = moveItem.ItemPosting; if (posting.Archive_Flag == true) { source = PictureFileRecord.StatusType.Archived; } else { source = PictureFileRecord.StatusType.ForSale; } } if (displaydestination) { // (1) the move item's being moved to the list it's already in if (archivedestination && source == PictureFileRecord.StatusType.Archived || !archivedestination && source == PictureFileRecord.StatusType.ForSale) { return new ChangeResult( false, filename + " is already included in the " + ((archivedestination) ? "Archive" : "Home") + " page", 400); } else { // (2) the moveItem's being moved from one list to another if (archivedestination && source == PictureFileRecord.StatusType.ForSale || !archivedestination && source == PictureFileRecord.StatusType.Archived) { removeResult = RemoveFromDisplay(posting); insertResult = InsertPosting(pfr, archivedestination); return insertResult; } else { // (3) the moveItem's being moved onto a list from not_displayed insertResult = InsertPosting(pfr, archivedestination); return insertResult; } } } // (4) the moveItem's being removed from all lists else { if (source == PictureFileRecord.StatusType.NotDisplayed) { return new ChangeResult( false, filename + " is not currently displayed, and therefore is not in the database. Therefore it cannot be removed from the database", 500); } else { removeResult = RemoveFromDisplay(posting); return removeResult; } } }
public ChangeResult Delete(ItemPosting posting) { throw new NotImplementedException(); }
public ChangeResult Create(ItemPosting posting, bool archive) { throw new NotImplementedException(); }
public bool Create(ItemPosting posting) { throw new NotImplementedException(); }
public void Update(ItemPosting posting, bool archived) { throw new NotImplementedException(); }
private ItemPosting extractSQLItemPosting(ItemPosting nonSQLPosting) { ItemPosting SQLPosting = new ItemPosting(); SQLPosting.Id = nonSQLPosting.Id; SQLPosting.Description = Utility.NormaliseString(nonSQLPosting.Description); SQLPosting.FileName = Utility.NormaliseString(nonSQLPosting.FileName); SQLPosting.FilePath = Utility.NormaliseString(nonSQLPosting.FilePath); SQLPosting.Header = Utility.NormaliseString(nonSQLPosting.Header); SQLPosting.Price = Utility.NormaliseString(nonSQLPosting.Price); SQLPosting.ShortName = Utility.NormaliseString(nonSQLPosting.ShortName); SQLPosting.Size = Utility.NormaliseString(nonSQLPosting.Size); SQLPosting.Title = Utility.NormaliseString(nonSQLPosting.Title); SQLPosting.Order = nonSQLPosting.Order; return SQLPosting; }
/// <summary> /// Removes an item from the database so it is no longer displayed on any list /// </summary> /// <param name="posting"></param> /// <returns></returns> public ChangeResult RemoveFromDisplay(ItemPosting posting) { return repository.Delete(posting); }
ChangeResult IPostingRepository.ExchangeOrders(ItemPosting posting1, ItemPosting posting2, bool archived) { throw new NotImplementedException(); }
ItemPostingViewModel IPostingService.CreateItemPostingViewModel(PictureFileRecord pfr) { ItemPosting posting = new ItemPosting(); posting.FileName = Utility.GetFilenameFromFilepath(pfr.FilePath); posting.FilePath = pfr.FilePath; posting.Archive_Flag = true; posting.Description = ""; posting.Header = ""; posting.Order = 0; posting.Price = ""; posting.ShortName = ""; posting.Size = ""; posting.Title = ""; ItemPostingViewModel vm = new ItemPostingViewModel(); vm.Editing = false; vm.ItemPosting = posting; return vm; }