/// <summary> /// Gets every order in the DB. /// </summary> /// <returns>The a list of OrderDO.</returns> public List <OrderDO> GetAllOrders() { // Instantiate a new list of OrderDOs. List <OrderDO> orderList = new List <OrderDO>(); // Declare the variables used to interact with SQL. SqlConnection sqlConnection = null; SqlCommand sqlCommand = null; SqlDataAdapter adapter = null; try { // Initialize the sqlConnection. sqlConnection = new SqlConnection(_connectionString); // Set sqlCommand to use a defined stored procedure. sqlCommand = new SqlCommand("OBTAIN_ALL_ORDERS", sqlConnection); sqlCommand.CommandType = CommandType.StoredProcedure; // Initialize the adapter. adapter = new SqlDataAdapter(sqlCommand); // Instatiate a new DataTable to hold the results of the stored procedure. DataTable orderTable = new DataTable(); // Open the SQL connection. sqlConnection.Open(); // Fill the DataTable. adapter.Fill(orderTable); // Map the DataTable to a list of orderDOs. orderList = OrderDataTableMapping.DataTableToOrderDOs(orderTable); } catch (Exception exception) { Logger.LogExceptionNoRepeats(exception); throw exception; } finally { // Manually dispose if (sqlConnection != null) { sqlConnection.Close(); sqlConnection.Dispose(); } if (sqlCommand != null) { sqlCommand.Dispose(); } if (adapter != null) { adapter.Dispose(); } } // Return the mapped results. return(orderList); }
/// <summary> /// Gets every order filtered by the user's ID and a status on an order. /// </summary> /// <param name="userID">Primary key of a user to filter by.</param> /// <param name="status">The status by which to filter by.</param> public List <OrderDO> GetOrdersByUserID(long userID, string status) { List <OrderDO> orderList = new List <OrderDO>(); // Declare the variables used to interact with SQL. SqlConnection sqlConnection = null; SqlCommand sqlCommand = null; SqlDataAdapter adapter = null; try { // Initailize the sqlConnection sqlConnection = new SqlConnection(_connectionString); // Set sqlCommand to use a defined stored procedure. sqlCommand = new SqlCommand("OBTAIN_ORDERS_BY_STATUS_AND_USERID", sqlConnection); sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.Parameters.AddWithValue("@Status", status); sqlCommand.Parameters.AddWithValue("@UserID", userID); // Initialize the adapter. adapter = new SqlDataAdapter(sqlCommand); // Create a new DataTable to hold the results of the stored procedure. DataTable orderTable = new DataTable(); sqlConnection.Open(); // Fill the Data Table with the results from the stored procedure. adapter.Fill(orderTable); orderList = OrderDataTableMapping.DataTableToOrderDOs(orderTable); } catch (Exception exception) { Logger.LogExceptionNoRepeats(exception); throw exception; } finally { if (sqlConnection != null) { sqlConnection.Close(); sqlConnection.Dispose(); } if (sqlCommand != null) { sqlCommand.Dispose(); } if (adapter != null) { adapter.Dispose(); } } return(orderList); }
/// <summary> /// Gets every pending order in the DB. /// </summary> /// <returns>A list of OrderDOs with any status not matching "Complete".</returns> public List <OrderDO> GetPendingOrders() { List <OrderDO> orderList = new List <OrderDO>(); // Declare the variables used to interact with SQL. SqlConnection sqlConnection = null; SqlCommand sqlCommand = null; SqlDataAdapter adapter = null; try { // Initailize the sqlConnection sqlConnection = new SqlConnection(_connectionString); // Set sqlCommand to use a defined stored procedure. sqlCommand = new SqlCommand("OBTAIN_ALL_PENDING_ORDERS", sqlConnection); sqlCommand.CommandType = CommandType.StoredProcedure; // Initialize the adapter. adapter = new SqlDataAdapter(sqlCommand); // Create a new DataTable to hold the results of the stored procedure. DataTable orderTable = new DataTable(); sqlConnection.Open(); // Fill the Data Table with the results from the stored procedure. adapter.Fill(orderTable); orderList = OrderDataTableMapping.DataTableToOrderDOs(orderTable); } catch (Exception exception) { Logger.LogExceptionNoRepeats(exception); throw exception; } finally { if (sqlConnection != null) { sqlConnection.Close(); sqlConnection.Dispose(); } if (sqlCommand != null) { sqlCommand.Dispose(); } if (adapter != null) { adapter.Dispose(); } } return(orderList); }
/// <summary> /// Gets any order that has the "status" value passed in to this method. /// </summary> /// <param name="status">The status to filter by in the DB.</param> /// <returns>An filtered OrderDO list.</returns> public List <OrderDO> GetOrdersByStatus(string status) { List <OrderDO> orderList = new List <OrderDO>(); // Declare the variables used to interact with SQL. SqlConnection sqlConnection = null; SqlCommand sqlCommand = null; SqlDataAdapter adapter = null; try { // Initialize the sqlConnection. sqlConnection = new SqlConnection(_connectionString); // Set sqlCommand to use a defined stored procedure. sqlCommand = new SqlCommand("OBTAIN_ORDERS_BY_STATUS", sqlConnection); sqlCommand.CommandType = CommandType.StoredProcedure; // Add the parameters for the stored procedure. sqlCommand.Parameters.AddWithValue("@Status", status); // Initialize the adapter. adapter = new SqlDataAdapter(sqlCommand); // Instantiate a new DataData DataTable orderTable = new DataTable(); // Open the connection to SQL. sqlConnection.Open(); // Fill the Data Table with the results from the stored procedure. adapter.Fill(orderTable); // Mapp the DataTable to a list of OrderDOs orderList = OrderDataTableMapping.DataTableToOrderDOs(orderTable); } catch (Exception exception) { Logger.LogExceptionNoRepeats(exception); throw exception; } finally { // Manually close any connections. if (sqlConnection != null) { sqlConnection.Close(); sqlConnection.Dispose(); } if (sqlCommand != null) { sqlCommand.Dispose(); } if (adapter != null) { adapter.Dispose(); } } return(orderList); }