public FilesController(IRepository repository, AppContext context, IFileStorage storage, MapperDAL mapper, IRead read)
{
_repository = repository;
_db = context;
_fileStorage = storage;
_mapper = mapper;
_read = read;
}
//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);
}
//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);
}
//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);
}
//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);
}
public Read(AppContext context, MapperDAL mapper)
{
_db = context;
_mapper = mapper;
}
public Repository(AppContext context, IFileStorage fileStorage, MapperDAL mapper)
{
_db = context;
_fileStorage = fileStorage;
_mapper = mapper;
}