public static int EditMarketEntry(MarketEntry updateEntry, MarketEntry oldEntry) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_update_market_entry"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@MarketEntryID", oldEntry.MarketEntryID); cmd.Parameters.AddWithValue("@OldResourceID", oldEntry.ResourceID); cmd.Parameters.AddWithValue("@NewResourceID", updateEntry.ResourceID); cmd.Parameters.AddWithValue("@OldUnits", oldEntry.ResourceAmount); cmd.Parameters.AddWithValue("@NewUnits", updateEntry.ResourceAmount); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
public int InsertCard(Card card) { int newId = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_add_card"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Name", card.Name); cmd.Parameters.AddWithValue("@ColorID", card.ColorID); cmd.Parameters.AddWithValue("@TypeID", card.TypeID); cmd.Parameters.AddWithValue("@EditionID", card.EditionID); cmd.Parameters.AddWithValue("@RarityID", card.RarityID); cmd.Parameters.AddWithValue("@CardText", card.CardText); cmd.Parameters.AddWithValue("@ImgFileNameID", card.ImgFileName); cmd.Parameters.AddWithValue("@Active", card.Active); cmd.Parameters.AddWithValue("@IsFoil", card.IsFoil); try { conn.Open(); decimal id = (decimal)cmd.ExecuteScalar(); newId = (int)id; } catch (Exception) { throw; } return(newId); }
/// <summary> /// Nathaniel Webber /// Created: 2021/02/05 /// /// This method grabs the corresponding stored procedure from the /// sql to help verfiy a login /// </summary> /// /// <remarks> /// Nathaniel Webber /// Updated: 2021/02/18 /// First MVP delivered /// </remarks> public int VerifyUserNameAndPassword(string userName, string passwordHash) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_authenticate_user", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@PasswordHash", SqlDbType.NVarChar, 100); cmd.Parameters["@UserName"].Value = userName; cmd.Parameters["@PasswordHash"].Value = passwordHash; try { conn.Open(); result = Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
public static int UpdateResourceActive(string resourceId, bool active) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_update_resource_active"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ResourceID", resourceId); cmd.Parameters.AddWithValue("@Active", active); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
/// <summary> /// Jory A. Wernette /// Created: 2021/05/10 /// /// a fake method used to reactivate a Player's account. /// </summary> /// /// <param name="email"> The email of the Player whose account will be retrieved</param> /// <exception>User could not be retrieved</exception> /// <returns>a user object</returns> public User SelectUserByEmail(string email) { User user = null; // get a connection var conn = DBConnection.GetDBConnection(); // create a command var cmd = new SqlCommand("sp_select_user_by_email", conn); // set the command type cmd.CommandType = CommandType.StoredProcedure; // create parameters cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100); // set parameter values cmd.Parameters["@Email"].Value = email; // execute the command try { // open the connection conn.Open(); // execute the command var reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); var userID = reader.GetInt32(0); var firstName = reader.GetString(2); var lastName = reader.GetString(3); var phoneNumber = reader.GetString(4); var active = reader.GetBoolean(5); reader.Close(); // get the user roles var playerAccessor = new PlayerAccessor(); var roles = playerAccessor.SelectRolesByKonamiID(userID); // add to a user object user = new User(userID, firstName, lastName, phoneNumber, email, roles); } else { throw new ApplicationException("User not found."); } } catch (Exception) { throw; } finally { conn.Close(); } return(user); }
public static int CreateMessage(Message message) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_create_message"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserID", message.UserID); cmd.Parameters.AddWithValue("@MarketEntryID", message.MarketEntryID); cmd.Parameters.AddWithValue("@Text", message.Text); cmd.Parameters.AddWithValue("@SentTime", message.SentTime); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
public static int UpdateCollection(Collection oldCollection, Collection newCollection) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_update_collection"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CollectionID", oldCollection.CollectionID); cmd.Parameters.AddWithValue("@OldName", oldCollection.Name); cmd.Parameters.AddWithValue("@NewName", newCollection.Name); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
/// <summary> /// Ryan Taylor /// Created: 2021/02/25 /// /// A method used to create a new journal entry to be /// stored within the data base. /// </summary> /// ///<param name="journalEntry">newly created journal</param> ///<exception></exception> ///<returns>An int to signify the journal was created</returns> public int InsertNewJournalEntry(JournalEntry journalEntry) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_insert_journal_entry", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserID_client", SqlDbType.Int); cmd.Parameters.Add("@UserID_ClientJournal", SqlDbType.Int); cmd.Parameters.Add("@JournalID", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@JournalEntryBody", SqlDbType.NVarChar, 500); cmd.Parameters["@UserID_client"].Value = journalEntry.UserIDClient; cmd.Parameters["@UserID_ClientJournal"].Value = journalEntry.UserIDClientJournal; cmd.Parameters["@JournalID"].Value = journalEntry.JournalID; cmd.Parameters["@JournalEntryBody"].Value = journalEntry.JournalEntryBody; try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
/// <summary> /// Jory A. Wernette /// Created: 2021/02/23 /// /// This method grabs the corresponding stored procedure from /// the sql to create a Journal using their userID, a journal name, /// and journal description /// </summary> /// /// <param name="userID"> The UserID of the User who is creatingthe Journal</param> /// <param name="journalName"> The Name of the Journal</param> /// <param name="journalDescription"> A Description of the Journal</param> /// <exception>No Journal created</exception> /// <returns>Rows Affected</returns> public int CreateNewJournal(int userID, string journalName, string journalDescription) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_insert_new_journal_by_userID", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserID_Client", userID); cmd.Parameters.AddWithValue("@JournalName", journalName); cmd.Parameters.AddWithValue("@JournalDescription", journalDescription); try { conn.Open(); result = cmd.ExecuteNonQuery(); if (result != 1) { throw new ApplicationException("The '" + journalName + "' Journal could not be added."); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
/// <summary> /// Jory A. Wernette /// Created: 2021/02/23 /// /// This method grabs the corresponding stored procedure from /// the sql to update a Journal using their userID, a journal name, /// and journal description /// </summary> /// /// <param name="newJournal"> The new things to be saved to the journal</param> /// <param name="oldJournal"> The old journal to check that we are overwritting the correct journal</param> /// <exception>No Journal updated</exception> /// <returns>Rows affected</returns> public int UpdateJournal(Journal newJournal, Journal oldJournal) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_update_journal", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserID", newJournal.UserID_Client); cmd.Parameters.AddWithValue("@NewJournalName", newJournal.JournalName); cmd.Parameters.AddWithValue("@NewJournalDescription", newJournal.JournalDescription); cmd.Parameters.AddWithValue("@OldJournalName", oldJournal.JournalName); cmd.Parameters.AddWithValue("@OldJournalDescription", oldJournal.JournalDescription); //cmd.Parameters["@OldJournalName"].Value = oldJournal.JournalName; //cmd.Parameters["@OldJournalDescription"].Value = oldJournal.JournalDescription; try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
/// <summary> /// Jory A. Wernette /// Created: 2021/03/03 /// /// This method grabs the corresponding stored procedure from the /// sql to help delete a Journal by a user's UserID, and a Journal's name /// </summary> /// /// <param name="userID">The UserID of the User whose Journals are getting retreived</param> /// <param name="journalName">The Name of this Journal</param> /// <exception>No Journal found</exception> /// <returns>Int Rows Affected</returns> public int DeleteJournalByUserIDAndJournalName(int userID, string journalName) { int result; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_delete_journal_by_userID_and_journalName", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserID_Client", userID); cmd.Parameters.AddWithValue("@JournalName", journalName); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
public static int CreateMarketEntry(MarketEntry entry) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_create_market_entry"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CollectionEntryID", entry.CollectionEntryID); cmd.Parameters.AddWithValue("@ResourceID", entry.ResourceID); cmd.Parameters.AddWithValue("@Units", entry.ResourceAmount); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
public static int EditMarketEntryStatus(MarketEntry marketEntry, string status) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_update_market_entry_status"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@MarketEntryID", marketEntry.MarketEntryID); cmd.Parameters.AddWithValue("@NewMarketEntryStatusID", status); cmd.Parameters.AddWithValue("@OldMarketEntryStatusID", marketEntry.MarketEntryStatusID); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
public static int CreateMarketEntryPurchase(User user, MarketEntry marketEntry) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_perform_market_entry_purchase"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserID", user.UserID); cmd.Parameters.AddWithValue("@MarketEntryID", marketEntry.MarketEntryID); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
public static int AddCreatureDiet(CreatureDiet creatureDiet) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_add_creature_diet"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CreatureDietID", creatureDiet.CreatureDietID); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
public int InsertNewEmployee(Employee employee) { int employeeID = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_insert_new_user", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100); cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50); cmd.Parameters.Add("@PhoneNumber", SqlDbType.NVarChar, 15); cmd.Parameters["@Email"].Value = employee.Email; cmd.Parameters["@FirstName"].Value = employee.FirstName; cmd.Parameters["@LastName"].Value = employee.LastName; cmd.Parameters["@PhoneNumber"].Value = employee.PhoneNumber; try { conn.Open(); employeeID = Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(employeeID); }
public static int UpdateCreatureType(CreatureType oldCreatureType, CreatureType newCreatureType) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_update_creature_type"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@OldCreatureTypeID", oldCreatureType.CreatureTypeID); cmd.Parameters.AddWithValue("@NewCreatureTypeID", newCreatureType.CreatureTypeID); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
public int DeactivateEmployee(int employeeID) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_safely_deactivate_employee", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EmployeeID", employeeID); try { conn.Open(); result = cmd.ExecuteNonQuery(); if (result != 1) { throw new ApplicationException("Employee could not be deactivated."); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
public static int DeactivateCollection(int collectionID) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_update_collection_active"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CollectionID", collectionID); cmd.Parameters.AddWithValue("@Active", false); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
public int InsertEmployeeRole(int employeeID, string role) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_add_employeerole", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EmployeeID", employeeID); cmd.Parameters.Add("@RoleID", SqlDbType.NVarChar, 25); cmd.Parameters["@RoleID"].Value = role; try { conn.Open(); result = cmd.ExecuteNonQuery(); if (result != 1) { throw new ApplicationException(role + " role could not be added."); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
public static List <Student> RetrieveStudentListByName(string name) { var studentList = new List <Student>(); // connection var conn = DBConnection.GetDBConnection(); // command text var cmdText = @"sp_retrieve_student_by_name"; // command var cmd = new SqlCommand(cmdText, conn); // command type cmd.CommandType = CommandType.StoredProcedure; // parameters and values cmd.Parameters.AddWithValue("@name", name); // try-catch try { // open connection conn.Open(); // execute command var reader = cmd.ExecuteReader(); // rows ? if (reader.HasRows) { // process the reader while (reader.Read()) { var eq = new Student() { StudentID = reader.GetString(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), PhoneNumber = reader.GetString(3), StudentEmail = reader.GetString(4), Active = reader.GetBoolean(5) }; studentList.Add(eq); } } else // no rows? { throw new ApplicationException("Data not found."); } } catch (Exception ex) { throw new ApplicationException("Database access error.", ex); } finally { conn.Close(); } return(studentList); }
/// <summary> /// Jory A. Wernette /// Created: 2021/05/10 /// /// a fake method used to reactivate a Player's account. /// </summary> /// /// <param name="konamiID"> The Konami ID of the Player whose account will be reactivated</param> /// <exception>Performance not reactivated or reactivated</exception> /// <returns>an int signifying the rows affected</returns> public int ReactivatePlayer(int konamiID) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_reactivate_user", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@KonamiID", konamiID); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
public static int AddResource(Resource resource) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmdText = @"sp_add_resource"; var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ResourceID", resource.ResourceID); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(result); }
/// <summary> /// Jory A. Wernette /// Created: 2021/05/10 /// /// a fake method used to deactivate a Player's account. /// </summary> /// /// <param name="konamiID"> The Konami ID of the Player whose role will be removed</param> /// <param name="role"> The role that will be removed from a Player</param> /// <exception>Performance not reactivated or deactivated</exception> /// <returns>an int signifying the rows affected</returns> public int DeletePlayerRole(int konamiID, string role) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_safely_remove_playerrole", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@KonamiID", konamiID); cmd.Parameters.Add("@RoleID", SqlDbType.NVarChar, 6); cmd.Parameters["@RoleID"].Value = role; try { conn.Open(); result = cmd.ExecuteNonQuery(); if (result != 1) { throw new ApplicationException(role + " role could not be removed."); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
public bool DeactivateCardByID(int cardID) { int result = 0; var cmdText = @"sp_deactivate_card_by_id"; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CardID", cardID); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } if (result == 1) { return(true); } else { return(false); } }
public int DeleteAward(int awardID) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_delete_award", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@AwardID", SqlDbType.Int); cmd.Parameters["@AwardID"].Value = awardID; try { conn.Open(); result = cmd.ExecuteNonQuery(); if (result != 1) { throw new ApplicationException("The Award could not be deleted."); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
public int ReactivateCustomer(int customerID) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_reactivate_customer", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CustomerID", customerID); try { conn.Open(); result = cmd.ExecuteNonQuery(); if (result != 1) { throw new ApplicationException("Product could not be deactivated."); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
/// <summary> /// Jory A. Wernette /// Created: 2021/05/10 /// /// updates a card. /// </summary> /// /// <param name="cardName"> The name of a card to have its Banlist Placement updated</param> /// <param name="oldBanlistPlacement"> The old data to be overwritten</param> /// <param name="newBanlistPlacement"> The new data to be saved</param> /// <exception>Card not updated</exception> /// <returns>an int signifying the rows affected</returns> public int UpdateACardsBanlistPlacement(string cardName, string oldBanlistPlacement, string newBanlistPlacement) { int rows = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_update_a_cards_banlistplacement", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CardName", cardName); cmd.Parameters.Add("@OldBanlistPlacement", SqlDbType.NVarChar, 12); cmd.Parameters["@OldBanlistPlacement"].Value = oldBanlistPlacement; cmd.Parameters.Add("@NewBanlistPlacement", SqlDbType.NVarChar, 12); cmd.Parameters["@NewBanlistPlacement"].Value = newBanlistPlacement; try { conn.Open(); rows = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(rows); }
/// <summary> /// Creator: Mitchell Paul /// Created: 3/10/2021 /// Approver: /// /// Method that allows the deletion of a UserAccount database item through the use of a Stored Procedure. /// </summary> /// <remarks> /// Updater: /// Updated: /// Update: /// </remarks> public int DeleteUserAccount(int userAccountID) { int result = 0; var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_safely_delete_UserAccount", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserID", userAccountID); try { conn.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(result); }
public static List <string> SelectAllProjectTypes() { List <string> projectTypes = new List <string>(); var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("sp_get_all_projecttypeid", conn); cmd.CommandType = CommandType.StoredProcedure; try { conn.Open(); var read = cmd.ExecuteReader(); if (read.HasRows) { while (read.Read()) { projectTypes.Add(read.GetString(0)); } } read.Close(); } catch (Exception) { throw; } finally { conn.Close(); } return(projectTypes); }