//Deleting an order from the database
public int DeleteOrder(int OrderID)
{
int rowsAffected = 0;
try
{
using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
using (SqlCommand deleteOrder = new SqlCommand("ORDERS_DELETE", sqlConnection))
{
//giving up after 60 seconds and adding the ID to sort by
deleteOrder.CommandType = CommandType.StoredProcedure;
deleteOrder.CommandTimeout = 60;
deleteOrder.Parameters.AddWithValue("OrderID", OrderID);
//opening connection and completing procedure
sqlConnection.Open();
rowsAffected = deleteOrder.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);
}
//deleting a user entry
public int DeleteUserEntry(int UserID)
{
int rowsAffected = 0;
try
{
//defining connections
using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
using (SqlCommand deleteUser = new SqlCommand("USERS_DELETE_ACCOUNT", sqlConnection))
{
//giving up after 60 seconds
deleteUser.CommandType = CommandType.StoredProcedure;
deleteUser.CommandTimeout = 60;
//assigning parameters
deleteUser.Parameters.AddWithValue("UserID", UserID);
//opening connection and executing procedure
sqlConnection.Open();
rowsAffected = deleteUser.ExecuteNonQuery();
}
}
//logging errors
catch (SqlException sqlEx)
{
LoggerDAL.SqlErrorLog(sqlEx);
throw sqlEx;
}
catch (Exception ex)
{
LoggerDAL.ErrorLog(ex);
throw ex;
}
return(rowsAffected);
}
//Retrieving all items under a single order
public List <ItemsDO> ItemsByOrderID(int orderID)
{
List <ItemsDO> items = new List <ItemsDO>();
try
{
//Defining some commands again
using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
using (SqlCommand viewByOrder = new SqlCommand("ITEMS_SELECT_BY_ORDER_ID", sqlConnection))
{
//after 60 seconds, it stops trying to complete
viewByOrder.CommandType = CommandType.StoredProcedure;
viewByOrder.CommandTimeout = 60;
//Sorting the table by OrderID, so only items in an order are returned
viewByOrder.Parameters.AddWithValue("OrderID", orderID);
sqlConnection.Open();
//gathering and storing data
using (SqlDataReader reader = viewByOrder.ExecuteReader())
{
while (reader.Read())
{
//creating a new item object for each entry and adding them all to a list
ItemsDO item = MapperDAL.ReaderToItem(reader);
items.Add(item);
}
}
}
}
//keeping a log of 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 list of items under that order
return(items);
}
//Viewing a single item entry
public ItemsDO ViewItemByID(int ItemID)
{
ItemsDO singleItem = new ItemsDO();
try
{
//defining commands to access the database
using (SqlConnection sqlConnection = new SqlConnection(_ConnectionString))
using (SqlCommand viewItemByID = new SqlCommand("ITEMS_SELECT_BY_ID", sqlConnection))
{
//telling it to time out after 60 seconds
viewItemByID.CommandType = CommandType.StoredProcedure;
viewItemByID.CommandTimeout = 60;
//using the ItemID to return only that item
viewItemByID.Parameters.AddWithValue("ItemID", ItemID);
sqlConnection.Open();
//collecting and storing the data
using (SqlDataReader reader = viewItemByID.ExecuteReader())
{
if (reader.Read())
{
singleItem = MapperDAL.ReaderToItem(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 data
return(singleItem);
}
//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);
}
//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);
}
//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);
}
//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);
}
//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);
}
//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;
}
}
//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;
}
}
