//updating an existing item entry public int UpdateItemEntryInformation(ItemsDO itemInfo) { int rowsAffected = 0; try { //defining some commands using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand updateItem = new SqlCommand("ITEMS_UPDATE", sqlConnection)) { //timing out after 60 seconds updateItem.CommandType = CommandType.StoredProcedure; updateItem.CommandTimeout = 60; //inseting information updateItem.Parameters.AddWithValue("ItemID", itemInfo.ItemID); updateItem.Parameters.AddWithValue("Type", itemInfo.Type); updateItem.Parameters.AddWithValue("SubType", itemInfo.SubType); updateItem.Parameters.AddWithValue("Trait", itemInfo.Trait); updateItem.Parameters.AddWithValue("Style", itemInfo.Style); updateItem.Parameters.AddWithValue("Set", itemInfo.Set); updateItem.Parameters.AddWithValue("Level", itemInfo.Level); updateItem.Parameters.AddWithValue("Quality", itemInfo.Quality); updateItem.Parameters.AddWithValue("OrderID", itemInfo.OrderID); updateItem.Parameters.AddWithValue("Price", itemInfo.Price); //opening connection and completing procedure sqlConnection.Open(); rowsAffected = updateItem.ExecuteNonQuery(); } } //Logging errors catch (SqlException sqlEx) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.ErrorLog(ex); throw ex; } return(rowsAffected); }
public List <byte> AllRatings(int GameID) { //Setting the variabes that will be used within the method. SqlConnection connectionToSql = null; SqlCommand storedProcedure = null; SqlDataReader reader = null; List <byte> ratingList = new List <byte>(); try { //Set up for connecting to SQL, using the stored procedure and how to access the information. connectionToSql = new SqlConnection(_connectionString); storedProcedure = new SqlCommand("OBTAIN_RATINGS", connectionToSql); storedProcedure.CommandType = System.Data.CommandType.StoredProcedure; //Gives the value passed to the method to the stored procedure parameter. storedProcedure.Parameters.AddWithValue("@GameID", GameID); //Opens the connection to SQL. connectionToSql.Open(); //Tell SQLDataReader to gather the information from SQL as determined by the stored procedure. reader = storedProcedure.ExecuteReader(); //Cycle through the database at a gameId and add all ratings to a list. while (reader.Read()) { ratingList.Add(byte.Parse(reader["Rating"].ToString())); } } catch (Exception ex) { LoggerDAL.Log(ex, "Fatal"); //If an issue occurs, the result would likely be fatal, throw the exception. throw ex; } finally { //When the connection has been established, close and dispose the connection before finishing. if (connectionToSql != null) { connectionToSql.Close(); connectionToSql.Dispose(); } } return(ratingList); }
public void AddGame(GameDO form) { //Setting the variables to be used for this method. SqlConnection connectionToSql = null; SqlCommand storedProcedure = null; try { //Setup for connecting to SQl and accessing the stored procedure. connectionToSql = new SqlConnection(_connectionString); storedProcedure = new SqlCommand("ADD_GAME", connectionToSql); storedProcedure.CommandType = System.Data.CommandType.StoredProcedure; //Add the value from the object to the parameters in the stored procedure to SqlCommand. storedProcedure.Parameters.AddWithValue("@GameName", form.GameName); storedProcedure.Parameters.AddWithValue("@ReleaseYear", form.ReleaseYear); storedProcedure.Parameters.AddWithValue("@Genre", form.Genre); storedProcedure.Parameters.AddWithValue("@Developer", form.Developer); storedProcedure.Parameters.AddWithValue("@Publisher", form.Publisher); storedProcedure.Parameters.AddWithValue("@Platform", form.Platform); storedProcedure.Parameters.AddWithValue("@Download", form.Download); storedProcedure.Parameters.AddWithValue("@Picture", form.Picture); storedProcedure.Parameters.AddWithValue("@Description", form.Description); //Open the connection to SQL. connectionToSql.Open(); //Applies all the values stored in the SqlCommand to the database. storedProcedure.ExecuteNonQuery(); } catch (Exception ex) { LoggerDAL.Log(ex, "Fatal"); //If an issue occurs, the result would likely be fatal, throw the exception. throw ex; } finally { //When the connection has been established, close and dispose the connection before finishing. if (connectionToSql != null) { connectionToSql.Close(); connectionToSql.Dispose(); } } }
//Retrieving orders by a specific user public List <OrdersDO> ViewOrderByStatus(byte status) { List <OrdersDO> orderData = new List <OrdersDO>(); try { //defining commands to access the database using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand viewByStatus = new SqlCommand("ORDERS_SELECT_BY_STATUS", sqlConnection)) { //giving up after 60 seconds viewByStatus.CommandType = CommandType.StoredProcedure; viewByStatus.CommandTimeout = 60; //inserting the UserID to sort entries viewByStatus.Parameters.AddWithValue("Status", status); //reading the data and using Mapper to store it sqlConnection.Open(); using (SqlDataReader reader = viewByStatus.ExecuteReader()) { while (reader.Read()) { orderData.Add(MapperDAL.ReaderToOrder(reader)); } } } } //logging errors catch (SqlException sqlEx) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.ErrorLog(ex); throw ex; } //returning order return(orderData); }
//Retrieving a single order from the database public OrdersDO ViewOrderByID(int OrderID) { OrdersDO orderData = new OrdersDO(); try { //defining commands to access the database using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand viewByID = new SqlCommand("ORDERS_SELECT_BY_ID", sqlConnection)) { //give up after 60 seconds viewByID.CommandType = CommandType.StoredProcedure; viewByID.CommandTimeout = 60; //inserting the UserID to sort through entries viewByID.Parameters.AddWithValue("OrderID", OrderID); //reading the data and using Mapper to store it sqlConnection.Open(); using (SqlDataReader reader = viewByID.ExecuteReader()) { if (reader.Read()) { orderData = MapperDAL.ReaderToOrder(reader); } } } } //logging any errors catch (SqlException sqlEx) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.ErrorLog(ex); throw ex; } // returning order return(orderData); }
//retrieve a single user entry from database public List <UsersDO> ViewUserByServer(string server) { List <UsersDO> userData = new List <UsersDO>(); try { //defining commands to access the database using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand viewByRole = new SqlCommand("USERS_SELECT_BY_SERVER", sqlConnection)) { //giving up after 60 seconds viewByRole.CommandType = CommandType.StoredProcedure; viewByRole.CommandTimeout = 60; //inserting the UserID to sort through entries viewByRole.Parameters.AddWithValue("Server", server); //reading the data and using Mapper to store it sqlConnection.Open(); using (SqlDataReader reader = viewByRole.ExecuteReader()) { while (reader.Read()) { //creating a list and mapping objects userData.Add(MapperDAL.ReaderToUser(reader)); } } } } //logging errors catch (SqlException sqlEx) { LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLog(ex); throw ex; } //returning data return(userData); }
//Retrieving User Data from the Database public List <UsersDO> ViewAllUsers() { //Creating a list of users List <UsersDO> usersList = new List <UsersDO>(); try { //defining commands to access the database using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand viewUserTable = new SqlCommand("USERS_SELECT_ALL", sqlConnection)) { //giving up after 60 seconds viewUserTable.CommandType = CommandType.StoredProcedure; viewUserTable.CommandTimeout = 60; //Reading the data and using Mapper to store it sqlConnection.Open(); using (SqlDataReader reader = viewUserTable.ExecuteReader()) { while (reader.Read()) { //creating a new user object for each entry and adding them to a list UsersDO user = MapperDAL.ReaderToUser(reader); usersList.Add(user); } } sqlConnection.Close(); } } //logging errors catch (SqlException sqlEx) { LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLog(ex); throw ex; } //returning list of all users return(usersList); }
public void UpdateUser(UserDO form) { //Setting the variables to be used for this method. SqlConnection connectionToSql = null; SqlCommand storedProcedure = null; try { //Set up for connecting to SQL, using the stored procedure and how to access the information. connectionToSql = new SqlConnection(_connectionString); storedProcedure = new SqlCommand("UPDATE_USER", connectionToSql); storedProcedure.CommandType = System.Data.CommandType.StoredProcedure; //Add the value from the object to the parameters in the stored procedure to SqlCommand. storedProcedure.Parameters.AddWithValue("@UserID", form.UserID); storedProcedure.Parameters.AddWithValue("@Username", form.Username); storedProcedure.Parameters.AddWithValue("@Password", form.Password); storedProcedure.Parameters.AddWithValue("@FirstName", form.FirstName); storedProcedure.Parameters.AddWithValue("@LastName", form.LastName); storedProcedure.Parameters.AddWithValue("Email", form.Email); storedProcedure.Parameters.AddWithValue("@RoleID", form.RoleID); //Open the connection to SQL. connectionToSql.Open(); //Applies all the values stored in the SqlCommand to the database. storedProcedure.ExecuteNonQuery(); } catch (Exception ex) { LoggerDAL.Log(ex, "Fatal"); //If an issue occurs, the result would likely be fatal, throw the exception. throw; } finally { //When the connection has been established, close and dispose the connection before finishing. if (connectionToSql != null) { connectionToSql.Close(); connectionToSql.Dispose(); } } }
//retrieving all orders from the database public List <OrdersDO> ViewAllOrders() { //Creating a list of users List <OrdersDO> ordersList = new List <OrdersDO>(); try { //defining commands and accessing the database using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand viewAllOrders = new SqlCommand("ORDERS_SELECT_ALL", sqlConnection)) { // gives up after 60 seconds viewAllOrders.CommandType = CommandType.StoredProcedure; viewAllOrders.CommandTimeout = 60; //reading the database and using a mapper to store it to memory sqlConnection.Open(); using (SqlDataReader reader = viewAllOrders.ExecuteReader()) { while (reader.Read()) { //creating objects to add them to a list ordersList.Add(MapperDAL.ReaderToOrder(reader)); } } } } //logging any errors catch (SqlException sqlEx) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.ErrorLog(ex); throw ex; } return(ordersList); }
//retrieve a single user entry from database public UsersDO ViewUserByUsername(string Username) { UsersDO userData = new UsersDO(); try { //defining commands to access the database using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand viewByUsername = new SqlCommand("USERS_SELECT_BY_USERNAME", sqlConnection)) { //time out after 60 seconds viewByUsername.CommandType = CommandType.StoredProcedure; viewByUsername.CommandTimeout = 60; //inserting the UserID to sort through entries viewByUsername.Parameters.AddWithValue("@Username", @Username); //reading the data and using Mapper to store it sqlConnection.Open(); using (SqlDataReader reader = viewByUsername.ExecuteReader()) { if (reader.Read()) { userData = MapperDAL.ReaderToUser(reader); } } } } //logging errors catch (SqlException sqlEx) { LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLog(ex); throw ex; } //returning data return(userData); }
public void AddComment(CommentDO form) { //Setting the variables to be used for this method. SqlConnection connectionToSql = null; SqlCommand storedProcedure = null; try { //Setup for connecting to SQl and accessing the stored procedure. connectionToSql = new SqlConnection(_connectionString); storedProcedure = new SqlCommand("ADD_COMMENT", connectionToSql); storedProcedure.CommandType = System.Data.CommandType.StoredProcedure; //Add the value from the object to the parameters in the stored procedure to SqlCommand. storedProcedure.Parameters.AddWithValue("@CommentTime", form.CommentTime); storedProcedure.Parameters.AddWithValue("@CommentText", form.CommentText); //When the rating given is the default of byte, make it null in the database, otherwise keep it's value. storedProcedure.Parameters.AddWithValue("@Rating", form.Rating == default(byte) ? (object)DBNull.Value : form.Rating); storedProcedure.Parameters.AddWithValue("@GameID", form.GameID); storedProcedure.Parameters.AddWithValue("@UserID", form.UserID); //Open connection to Sql. connectionToSql.Open(); //Applies all the values stored in the SqlCommand to the database. storedProcedure.ExecuteNonQuery(); } catch (Exception ex) { LoggerDAL.Log(ex, "Fatal"); //If an issue occurs, the result would likely be fatal, throw the exception. throw ex; } finally { //When the connection has been established, close and dispose the connection before finishing. if (connectionToSql != null) { connectionToSql.Close(); connectionToSql.Dispose(); } } }
private void LogViewer_Load(object sender, EventArgs e) { List <UserCredential> usersList = UserDAL.GetUsers(); foreach (var user in usersList) { this.tab_userLogs.TabPages.Add(GenerateTabForUser(user.Email)); } if (usersList.Any()) { string userEmail = usersList[0].Email; List <LogInfo> logsForUser = LoggerDAL.GetLogForUser(userEmail); Guna2DataGridView gridView = this.tab_userLogs.SelectedTab.Controls[userEmail + "_logview"] as Guna2DataGridView; gridView.DataSource = logsForUser.ToDataTable(); } InitTimer(); }
//Creating a new User entry public int CreateNewUserEntry(UsersDO userInfo) { int rowsAffected = 0; try { //defining commands using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand createUser = new SqlCommand("USERS_CREATE_NEW", sqlConnection)) { //timing out after 60 seconds createUser.CommandType = CommandType.StoredProcedure; createUser.CommandTimeout = 60; //inserting information createUser.Parameters.AddWithValue("Username", userInfo.Username); createUser.Parameters.AddWithValue("Email", userInfo.Email); createUser.Parameters.AddWithValue("Password", userInfo.Password); createUser.Parameters.AddWithValue("ESOname", userInfo.ESOname); createUser.Parameters.AddWithValue("RoleID", userInfo.RoleID); createUser.Parameters.AddWithValue("Server", userInfo.Server); //Saving information to database sqlConnection.Open(); rowsAffected = createUser.ExecuteNonQuery(); } } //logging errors catch (SqlException sqlEx) { LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLog(ex); throw ex; } return(rowsAffected); }
//updating an existing order's crafer public int UpdateOrderCrafter(OrdersDO newInfo) { int rowsAffected = 0; try { //defining some commands using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand updateOrder = new SqlCommand("ORDERS_UPDATE_CRAFTER", sqlConnection)) { //timing out after 60 seconds updateOrder.CommandType = CommandType.StoredProcedure; updateOrder.CommandTimeout = 60; //inserting information updateOrder.Parameters.AddWithValue("OrderID", newInfo.OrderID); updateOrder.Parameters.AddWithValue("CrafterID", newInfo.CrafterID); updateOrder.Parameters.AddWithValue("Status", newInfo.Status); //Saving information to database sqlConnection.Open(); rowsAffected = updateOrder.ExecuteNonQuery(); } } //logging errors catch (SqlException sqlEx) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.ErrorLog(ex); throw ex; } return(rowsAffected); }
//Updating an existing user public void UpdateUserInformation(UsersDO userInfo) { try { //defining some commands using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand updateUser = new SqlCommand("USERS_UPDATE_ACCOUNT", sqlConnection)) { //timing out after 60 seconds updateUser.CommandType = CommandType.StoredProcedure; updateUser.CommandTimeout = 60; //inserting information updateUser.Parameters.AddWithValue("UserID", userInfo.UserID); updateUser.Parameters.AddWithValue("Username", userInfo.Username); updateUser.Parameters.AddWithValue("Email", userInfo.Email); updateUser.Parameters.AddWithValue("RoleID", userInfo.RoleID); updateUser.Parameters.AddWithValue("Password", userInfo.Password); updateUser.Parameters.AddWithValue("ESOname", userInfo.ESOname); updateUser.Parameters.AddWithValue("Server", userInfo.Server); //Saving information to database sqlConnection.Open(); updateUser.ExecuteNonQuery(); } } //logging errors catch (SqlException sqlEx) { LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLog(ex); throw ex; } }
//deleting an item entry public int DeleteItemEntry(int ItemID) { int rowsAffected = 0; try { using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand deleteItem = new SqlCommand("ITEMS_DELETE", sqlConnection)) { //Defining procedure and timing out after 60 seconds deleteItem.CommandType = CommandType.StoredProcedure; deleteItem.CommandTimeout = 60; //adding ItemID as parameter deleteItem.Parameters.AddWithValue("ItemID", ItemID); //opening connection and executing procedure sqlConnection.Open(); rowsAffected = deleteItem.ExecuteNonQuery(); sqlConnection.Close(); sqlConnection.Dispose(); } } // logging errors catch (SqlException sqlEx) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.ErrorLog(ex); throw ex; } return(rowsAffected); }
//creating a new order public void CreateNewOrder(OrdersDO orderInfo) { try { //defining commands using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString)) using (SqlCommand createOrder = new SqlCommand("ORDERS_CREATE_NEW", sqlConnection)) { //timing out after 60 seconds createOrder.CommandType = CommandType.StoredProcedure; createOrder.CommandTimeout = 60; //inserting information createOrder.Parameters.AddWithValue("UserID", orderInfo.UserID); createOrder.Parameters.AddWithValue("Requested", orderInfo.Requested); createOrder.Parameters.AddWithValue("Due", orderInfo.Due); createOrder.Parameters.AddWithValue("Status", orderInfo.Status); //Saving information to database sqlConnection.Open(); createOrder.ExecuteNonQuery(); } } //logging errors catch (SqlException sqlEx) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.SqlErrorLog(sqlEx); throw sqlEx; } catch (Exception ex) { LoggerDAL.ErrorLogPath = _ErrorLogPath; LoggerDAL.ErrorLog(ex); throw ex; } }
public void RemoveComment(int CommentID) { //Setting the variabes that will be used within the method. SqlConnection connectionToSql = null; SqlCommand storedProcedure = null; try { //Set up for connecting to SQL, using the stored procedure and how to access the information. connectionToSql = new SqlConnection(_connectionString); storedProcedure = new SqlCommand("REMOVE_COMMENT", connectionToSql); storedProcedure.CommandType = System.Data.CommandType.StoredProcedure; //Gives the value passed to the method to the stored procedure parameter. storedProcedure.Parameters.AddWithValue("@CommentID", CommentID); //Opens the connection to SQL. connectionToSql.Open(); //Applies the stored procedure in the SqlCommand to the database. storedProcedure.ExecuteNonQuery(); } catch (Exception ex) { LoggerDAL.Log(ex, "Fatal"); //If an issue occurs, the result would likely be fatal, throw the exception. throw ex; } finally { //When the connection has been established, close and dispose the connection before finishing. if (connectionToSql != null) { connectionToSql.Close(); connectionToSql.Dispose(); } } }