/// <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 a OrderDO by an ID.
/// </summary>
/// <param name="orderID">The primary key of the order.</param>
/// <returns>Returns the Order if it was found otherwise returns null.</returns>
public OrderDO GetOrderByID(long orderID)
{
// Start of with the orderDO set to null.
OrderDO orderDO = null;
// 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);
// Initialize the sqlCommand
sqlCommand = new SqlCommand("OBTAIN_ORDER_BY_ORDERID", sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
// Add the paramerter this stroed procedure requires.
sqlCommand.Parameters.AddWithValue("@OrderID", orderID);
// Initialize the adapter.
adapter = new SqlDataAdapter(sqlCommand);
// Instantiate a new DataTable to store the results from the stored procedure.
DataTable orderTable = new DataTable();
// Open the connection.
sqlConnection.Open();
// Fill the DataTable with the results from the stored procedure.
adapter.Fill(orderTable);
// If the DataTable has any rows..
if (orderTable.Rows.Count > 0)
{
// Map the row to the orderDO.
orderDO = OrderDataTableMapping.DataRowToOrderDO(orderTable.Rows[0]);
}
}
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(orderDO);
}
/// <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);
}
