public int SaveShippingTradeDetails(DAO.Shipping shipping, DataTable documentInstructionsTable, DataTable shippingModelsTable)
{
SqlParameter parameter;
int shippingId = -1;
try
{
using (SqlConnection connection = new SqlConnection(TradeDBConnectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand("SaveShippingTradeDetails", connection);
cmd.CommandType = CommandType.StoredProcedure;
foreach (var property in shipping.GetType().GetProperties())
{
parameter = new SqlParameter();
parameter.ParameterName = "@" + property.Name;
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Value = property.GetValue(shipping, null);
cmd.Parameters.Add(parameter);
}
parameter = new SqlParameter();
parameter.ParameterName = "@tvpDocumentInstructions";
parameter.SqlDbType = SqlDbType.Structured;
parameter.Value = documentInstructionsTable;
cmd.Parameters.Add(parameter);
parameter = new SqlParameter();
parameter.ParameterName = "@tvpShippingModels";
parameter.SqlDbType = SqlDbType.Structured;
parameter.Value = shippingModelsTable;
cmd.Parameters.Add(parameter);
shippingId = (int)cmd.ExecuteScalar();
connection.Close();
}
}
catch (Exception ex)
{
_tradeLogger.Error("Trading.DAL.Trade.LoadSheet", ex);
throw ex;
}
return(shippingId);
}
private DAO.Shipping GetShipping(ISheet tradeSheet, int shippingDetailsRowIndex, int nextSectionRowIndex)
{
DAO.Shipping shippingDetail = new DAO.Shipping();
IRow tradeSheetRow;
for (int shippingRowCounter = shippingDetailsRowIndex + 1; shippingRowCounter < nextSectionRowIndex; shippingRowCounter++)
{
tradeSheetRow = tradeSheet.GetRow(shippingRowCounter);
if (tradeSheetRow == null)
{
continue;
}
try
{
string shippingElementKey = tradeSheetRow.Cells[0].ToString().Replace(":", "").Trim(); // first cell of each row
if (shippingElementKey != string.Empty)
{
IEnumerable <XElement> shippingProperty = _shippingModelDocument.Root.Descendants("Shipping").Descendants("Properties")
.Elements("Property").Where(x => shippingElementKey.Contains(x.Attribute("RowLabel").Value));
if (shippingProperty.Any())
{
PropertyInfo propertyInfo = shippingDetail.GetType().GetProperty(shippingProperty.First().Attribute("DBColumn").Value);
propertyInfo.SetValue(shippingDetail, string.Empty, null);
if (tradeSheetRow.Cells.ElementAtOrDefault(1) != null)
{
string propertyValue = tradeSheetRow.Cells[1].ToString(); // to do check
propertyInfo.SetValue(shippingDetail, propertyValue, null);
}
}
}
}
catch (Exception ex)
{
_tradeLogger.Error("Trading.BLL.Trade.LoadSheet", ex);
throw ex;
}
}
return(shippingDetail);
}