public void Setup()
{
_shippingOrderLineItem = new ShippingOrderLineItem(2, 4)
{
ProductName = "Test Monitor",
Quantity = 3,
ProductLocation = "Place",
IsPicked = false
};
}
public static bool AddShippingOrderLineItems(ShippingOrderLineItem lineItem, SqlConnection myConnection)
{
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_InsertIntoShippingOrderLineItems", myConnection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@shippingOrderID", lineItem.ShippingOrderID);
mySqlCommand.Parameters.AddWithValue("@productID", lineItem.ProductId);
mySqlCommand.Parameters.AddWithValue("@quantity", lineItem.Quantity);
mySqlCommand.Parameters.AddWithValue("@picked", lineItem.IsPicked ? 1 : 0); //Change to bit, Ternary Operator
myConnection.Open();
if (mySqlCommand.ExecuteNonQuery() == 1)
{
return true;
}
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return false;
}
public static List<ShippingOrderLineItem> GetAllShippingOrderLineItems(SqlConnection myConnection)
{
var shippingOrderLineItemList = new List<ShippingOrderLineItem>();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetAllShippingOrderLineItems", myConnection)
{
CommandType = CommandType.StoredProcedure
};
myConnection.Open();
var mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while (mySqlReader.Read())
{
// shippingOrderId and productId added in construction below
var shippingLineItem = new ShippingOrderLineItem(mySqlReader.GetInt32(0),mySqlReader.GetInt32(1))
{
ProductName = mySqlReader.GetString(2),
Quantity = mySqlReader.GetInt32(3),
ProductLocation = mySqlReader[4] as string,
IsPicked = mySqlReader.GetBoolean(5)
};
shippingOrderLineItemList.Add(shippingLineItem);
} //end while
} // end if
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return shippingOrderLineItemList;
}
public static bool UnpickShippingOrderLineItem(ShippingOrderLineItem lineItem, SqlConnection myConnection)
{
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_UpdateShippingOrderLineItemRemovePicked", myConnection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@shippingOrderID", lineItem.ShippingOrderID);
mySqlCommand.Parameters.AddWithValue("@productID", lineItem.ProductId);
myConnection.Open();
if (mySqlCommand.ExecuteNonQuery() == 1)
{
return true;
}
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return false;
}
public static ShippingOrderLineItem GetShippingOrderLineItemById(int productId, int shippingOrderID, SqlConnection myConnection)
{
var shippingLineItem = new ShippingOrderLineItem();
myConnection = myConnection ?? GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_GetShippingOrderLineItem", myConnection);
mySqlCommand.Parameters.AddWithValue("@productID", productId);
mySqlCommand.Parameters.AddWithValue("@shippingOrderID", shippingOrderID);
mySqlCommand.CommandType = CommandType.StoredProcedure;
myConnection.Open();
SqlDataReader mySqlReader = mySqlCommand.ExecuteReader();
if (mySqlReader.HasRows)
{
while(mySqlReader.Read())
{
shippingLineItem = new ShippingOrderLineItem(mySqlReader.GetInt32(0), mySqlReader.GetInt32(1))
{
ProductName = mySqlReader.GetString(2),
Quantity = mySqlReader.GetInt32(3),
ProductLocation = mySqlReader[4] as string,
IsPicked = mySqlReader.GetBoolean(5)
};
}
} // End If
mySqlReader.Close();
}
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
myConnection.Close();
}
return shippingLineItem;
}
public bool Update(ShippingOrderLineItem lineItem, ShippingOrderLineItem originalLineItem)
{
if (lineItem == null) throw new ApplicationException("ShippingOrderLineItem is null");
return ShippingOrderLineItemDAL.UpdateShippingOrderLineItem(lineItem, originalLineItem, _connection);
}
public bool Insert(ShippingOrderLineItem lineItem)
{
if (lineItem == null) throw new ApplicationException("ShippingOrderLineItem is null");
return ShippingOrderLineItemDAL.AddShippingOrderLineItems(lineItem, _connection);
}
public bool UpdatePickedTrue(ShippingOrderLineItem lineItem)
{
//Need to do error checking... Try/Catch.
return ShippingOrderLineItemDAL.PickShippingOrderLineItem(lineItem, _connection);
//Need to decrement available inventory for this line item by the quantity.
}
void InsertLineItemNullDel()
{
_shippingOrderLineItem = null;
_shippingOrderLineItemManager.Insert(_shippingOrderLineItem);
}
public void TearDown()
{
_shippingOrderLineItem = null;
}