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