public void WriteShippingImportLog(UploadTradeLog uploadTradeLog) { SqlParameter parameter; try { using (SqlConnection connection = new SqlConnection(TradeDBConnectionString)) { connection.Open(); SqlCommand cmd = new SqlCommand("WriteShippingImportLog", connection); cmd.CommandType = CommandType.StoredProcedure; parameter = new SqlParameter(); parameter.ParameterName = "@ShippingId"; parameter.SqlDbType = SqlDbType.Int; parameter.Value = uploadTradeLog.ShippingId; cmd.Parameters.Add(parameter); parameter = new SqlParameter(); parameter.ParameterName = "@WorkBookName"; parameter.SqlDbType = SqlDbType.NVarChar; parameter.Value = uploadTradeLog.WorkBookName; cmd.Parameters.Add(parameter); parameter = new SqlParameter(); parameter.ParameterName = "@TradeRequest"; parameter.SqlDbType = SqlDbType.NVarChar; parameter.Value = uploadTradeLog.TradeRequest; cmd.Parameters.Add(parameter); parameter = new SqlParameter(); parameter.ParameterName = "@ImportDate"; parameter.SqlDbType = SqlDbType.DateTime; parameter.Value = uploadTradeLog.ImportDate; cmd.Parameters.Add(parameter); parameter = new SqlParameter(); parameter.ParameterName = "@ImportStatus"; parameter.SqlDbType = SqlDbType.NVarChar; parameter.Value = uploadTradeLog.ImportStatus; cmd.Parameters.Add(parameter); parameter = new SqlParameter(); parameter.ParameterName = "@ExceptionMessage"; parameter.SqlDbType = SqlDbType.NVarChar; parameter.Value = uploadTradeLog.ExceptionMessage; cmd.Parameters.Add(parameter); cmd.ExecuteNonQuery(); connection.Close(); } } catch (Exception ex) { _tradeLogger.Error("Trading.DAL.Trade.LoadSheet", ex); throw ex; } }
public void ProcessSheet(dynamic sheetDetails) { UploadTrade uploadTradeDetails = new UploadTrade(); UploadTradeLog uploadTradeLog = new UploadTradeLog() { ImportDate = DateTime.Now, WorkBookName = sheetDetails.Sheet.SheetName, TradeRequest = string.Empty, ExceptionMessage = string.Empty }; DAL.Trade trade = new DAL.Trade(); trade.TradeDBConnectionString = this.TradeDBConnectionString; int shippingId = -1; string shippingMissedElements = string.Empty; try { uploadTradeDetails.Shipping = GetShipping(sheetDetails.Sheet, sheetDetails.ShippingDetailsRowIndex, sheetDetails.DocumentInstructionsRowIndex); uploadTradeDetails.Shipping.TradeSheetName = sheetDetails.Sheet.SheetName; uploadTradeDetails.DocumentInstructions = GetDocumentInstructions(sheetDetails.Sheet, sheetDetails.DocumentInstructionsRowIndex); uploadTradeDetails.ShippingModels = GetShippingModels(sheetDetails.Sheet, sheetDetails.ShippingModelsRowIndex); var CheckIsSINoAlreadyExist = trade.CheckIsSINoAlreadyExist(uploadTradeDetails.Shipping.SINo); if (CheckIsSINoAlreadyExist) { throw new Exception("Import already done for this Shipping Instruction"); } else { DataTable documentInstructionsTable = JsonConvert.DeserializeObject <DataTable>(JsonConvert.SerializeObject(uploadTradeDetails.DocumentInstructions)); DataTable shippingModelsTable = JsonConvert.DeserializeObject <DataTable>(JsonConvert.SerializeObject(uploadTradeDetails.ShippingModels)); shippingId = trade.SaveShippingTradeDetails(uploadTradeDetails.Shipping, documentInstructionsTable, shippingModelsTable); } if (shippingId > 0) { uploadTradeLog.ShippingId = shippingId; uploadTradeLog.ImportStatus = "IMPORTED"; trade.WriteShippingImportLog(uploadTradeLog); } else { uploadTradeLog.ShippingId = shippingId; uploadTradeLog.ImportStatus = "VALIDATIONS"; uploadTradeLog.ExceptionMessage = shippingMissedElements; trade.WriteShippingImportLog(uploadTradeLog); } } catch (Exception ex) { uploadTradeLog.ShippingId = shippingId; uploadTradeLog.ImportStatus = "FAILED"; uploadTradeLog.ExceptionMessage = ex.Message; trade.WriteShippingImportLog(uploadTradeLog); _tradeLogger.Error("Trading.BLL.Trade.LoadSheet", ex); throw ex; } }