public void Replicate(ContractCompareDomesticObject x) { this.Id = x.Id; this.DateOfData = x.DateOfData; this.DateOfImport = x.DateOfImport; this.RecordType = x.RecordType; this.ParticipantID = x.ParticipantID; this.ContraPartyID = x.ContraPartyID; this.ActivityCode = x.ActivityCode; this.UserContractInternalReferenceNumber = x.UserContractInternalReferenceNumber; this.Filler1 = x.Filler1; this.SecurityID = x.SecurityID; this.SecurityIDType = x.SecurityIDType; this.OpenQuantity = x.OpenQuantity; this.ContractValue = x.ContractValue; this.RebateRateCode = x.RebateRateCode; this.RebateFeeRate = x.RebateFeeRate; this.CollateralType = x.CollateralType; this.Filler2 = x.Filler2; this.DeliveryDateofDeal = x.DeliveryDateofDeal; this.CloseDateorTermDateofTransaction = x.CloseDateorTermDateofTransaction; this.Filler3 = x.Filler3; this.UserContractInfo = x.UserContractInfo; this.SunGardUseOnly1 = x.SunGardUseOnly1; this.ComparisonCode = x.ComparisonCode; this.InternalAccountNumber = x.InternalAccountNumber; this.Filler4 = x.Filler4; this.SunGardUseOnly2 = x.SunGardUseOnly2; this.MarginParameter = x.MarginParameter; this.RoundingDirection = x.RoundingDirection; this.MarkRoundingFactor = x.MarkRoundingFactor; this.Filler5 = x.Filler5; this.AccruedBondInterestincludedinmarks = x.AccruedBondInterestincludedinmarks; this.Filler6 = x.Filler6; this.SunGardUseOnly3 = x.SunGardUseOnly3; this.DividendFlowThrough = x.DividendFlowThrough; this.Filler7 = x.Filler7; this.IncomeTrackingIndicator = x.IncomeTrackingIndicator; this.SunGardUseOnly4 = x.SunGardUseOnly4; this.Filler8 = x.Filler8; this.SunGardUseOnly5 = x.SunGardUseOnly5; this.OCCHedgeContract = x.OCCHedgeContract; this.CustodianID = x.CustodianID; this.SubAccount = x.SubAccount; this.Filler9 = x.Filler9; this.SunGardUseOnly6 = x.SunGardUseOnly6; }
public void InsertSingle(ContractCompareDomesticObject obj) { DA.InsertSingle(obj); }
public void InsertSingle(ContractCompareDomesticObject obj) { //Change to SP if required string sql =@"INSERT INTO ContractCompareDomestic(DateOfData, DateOfImport, RecordType, ParticipantID, ContraPartyID, ActivityCode, UserContractInternalReferenceNumber, Filler1, SecurityID, SecurityIDType, OpenQuantity, ContractValue, RebateRateCode, RebateFeeRate, CollateralType, Filler2, DeliveryDateofDeal, CloseDateorTermDateofTransaction, Filler3, UserContractInfo, SunGardUseOnly1, ComparisonCode, InternalAccountNumber, Filler4, SunGardUseOnly2, MarginParameter, RoundingDirection, MarkRoundingFactor, Filler5, AccruedBondInterestincludedinmarks, Filler6, SunGardUseOnly3, DividendFlowThrough, Filler7, IncomeTrackingIndicator, SunGardUseOnly4, Filler8, SunGardUseOnly5, OCCHedgeContract, CustodianID, SubAccount, Filler9, SunGardUseOnly6) VALUES(@DateOfData, @DateOfImport, @RecordType, @ParticipantID, @ContraPartyID, @ActivityCode, @UserContractInternalReferenceNumber, @Filler1, @SecurityID, @SecurityIDType, @OpenQuantity, @ContractValue, @RebateRateCode, @RebateFeeRate, @CollateralType, @Filler2, @DeliveryDateofDeal, @CloseDateorTermDateofTransaction, @Filler3, @UserContractInfo, @SunGardUseOnly1, @ComparisonCode, @InternalAccountNumber, @Filler4, @SunGardUseOnly2, @MarginParameter, @RoundingDirection, @MarkRoundingFactor, @Filler5, @AccruedBondInterestincludedinmarks, @Filler6, @SunGardUseOnly3, @DividendFlowThrough, @Filler7, @IncomeTrackingIndicator, @SunGardUseOnly4, @Filler8, @SunGardUseOnly5, @OCCHedgeContract, @CustodianID, @SubAccount, @Filler9, @SunGardUseOnly6)"; using (SqlConnection conn = new SqlConnection(cstr)) { SqlCommand cmd = new SqlCommand(sql, conn); //cmd.CommandType=CommandType.StoredProcedure; //cmd.Parameters.AddWithValue(","); cmd.Parameters.AddWithValue("@DateOfData", SetNullValue(obj.DateOfData)); cmd.Parameters.AddWithValue("@DateOfImport", SetNullValue(obj.DateOfImport)); cmd.Parameters.AddWithValue("@RecordType", SetNullValue(obj.RecordType)); cmd.Parameters.AddWithValue("@ParticipantID", SetNullValue(obj.ParticipantID)); cmd.Parameters.AddWithValue("@ContraPartyID", SetNullValue(obj.ContraPartyID)); cmd.Parameters.AddWithValue("@ActivityCode", SetNullValue(obj.ActivityCode)); cmd.Parameters.AddWithValue("@UserContractInternalReferenceNumber", SetNullValue(obj.UserContractInternalReferenceNumber)); cmd.Parameters.AddWithValue("@Filler1", SetNullValue(obj.Filler1)); cmd.Parameters.AddWithValue("@SecurityID", SetNullValue(obj.SecurityID)); cmd.Parameters.AddWithValue("@SecurityIDType", SetNullValue(obj.SecurityIDType)); cmd.Parameters.AddWithValue("@OpenQuantity", SetNullValue(obj.OpenQuantity)); cmd.Parameters.AddWithValue("@ContractValue", SetNullValue(obj.ContractValue)); cmd.Parameters.AddWithValue("@RebateRateCode", SetNullValue(obj.RebateRateCode)); cmd.Parameters.AddWithValue("@RebateFeeRate", SetNullValue(obj.RebateFeeRate)); cmd.Parameters.AddWithValue("@CollateralType", SetNullValue(obj.CollateralType)); cmd.Parameters.AddWithValue("@Filler2", SetNullValue(obj.Filler2)); cmd.Parameters.AddWithValue("@DeliveryDateofDeal", SetNullValue(obj.DeliveryDateofDeal)); cmd.Parameters.AddWithValue("@CloseDateorTermDateofTransaction", SetNullValue(obj.CloseDateorTermDateofTransaction)); cmd.Parameters.AddWithValue("@Filler3", SetNullValue(obj.Filler3)); cmd.Parameters.AddWithValue("@UserContractInfo", SetNullValue(obj.UserContractInfo)); cmd.Parameters.AddWithValue("@SunGardUseOnly1", SetNullValue(obj.SunGardUseOnly1)); cmd.Parameters.AddWithValue("@ComparisonCode", SetNullValue(obj.ComparisonCode)); cmd.Parameters.AddWithValue("@InternalAccountNumber", SetNullValue(obj.InternalAccountNumber)); cmd.Parameters.AddWithValue("@Filler4", SetNullValue(obj.Filler4)); cmd.Parameters.AddWithValue("@SunGardUseOnly2", SetNullValue(obj.SunGardUseOnly2)); cmd.Parameters.AddWithValue("@MarginParameter", SetNullValue(obj.MarginParameter)); cmd.Parameters.AddWithValue("@RoundingDirection", SetNullValue(obj.RoundingDirection)); cmd.Parameters.AddWithValue("@MarkRoundingFactor", SetNullValue(obj.MarkRoundingFactor)); cmd.Parameters.AddWithValue("@Filler5", SetNullValue(obj.Filler5)); cmd.Parameters.AddWithValue("@AccruedBondInterestincludedinmarks", SetNullValue(obj.AccruedBondInterestincludedinmarks)); cmd.Parameters.AddWithValue("@Filler6", SetNullValue(obj.Filler6)); cmd.Parameters.AddWithValue("@SunGardUseOnly3", SetNullValue(obj.SunGardUseOnly3)); cmd.Parameters.AddWithValue("@DividendFlowThrough", SetNullValue(obj.DividendFlowThrough)); cmd.Parameters.AddWithValue("@Filler7", SetNullValue(obj.Filler7)); cmd.Parameters.AddWithValue("@IncomeTrackingIndicator", SetNullValue(obj.IncomeTrackingIndicator)); cmd.Parameters.AddWithValue("@SunGardUseOnly4", SetNullValue(obj.SunGardUseOnly4)); cmd.Parameters.AddWithValue("@Filler8", SetNullValue(obj.Filler8)); cmd.Parameters.AddWithValue("@SunGardUseOnly5", SetNullValue(obj.SunGardUseOnly5)); cmd.Parameters.AddWithValue("@OCCHedgeContract", SetNullValue(obj.OCCHedgeContract)); cmd.Parameters.AddWithValue("@CustodianID", SetNullValue(obj.CustodianID)); cmd.Parameters.AddWithValue("@SubAccount", SetNullValue(obj.SubAccount)); cmd.Parameters.AddWithValue("@Filler9", SetNullValue(obj.Filler9)); cmd.Parameters.AddWithValue("@SunGardUseOnly6", SetNullValue(obj.SunGardUseOnly6)); try { conn.Open(); cmd.ExecuteNonQuery(); } //Only use whats in catch block if you included Log.cs catch (SqlException ex) { Utils.LogError(ex.Message); } } }
public FileImporter() { try { //string dir = Properties.Settings.Default.ImportFileDir + inputdate.ToString("yyyyMMdd"); string dir = @"C:\dev\backoffice\ContractCompareLoanetFile\ContractCompareLoanetFile\bin\Debug"; List<string> Allfiles = Directory.GetFiles(dir, "XX_ECO*").ToList(); if (Allfiles.Count < 1) { throw new Exception("There were no files to import"); } //Filename must have length of 10 List<string> FilesToImport = new List<string>(); Allfiles.Where(x => Path.GetFileName(x).Length == 10).ToList().ForEach(FilesToImport.Add); if (FilesToImport.Count < 1) { throw new Exception("There were files in dir but didn't have a length of 10"); } ContractCompareDomesticFactory CCDF = new ContractCompareDomesticFactory(); foreach (string f in FilesToImport) { try { string[] RawData = File.ReadAllText(f).BreakStringIntoArrayEveryNspaces(1000); #region Header Data string RecordType_Header = RawData[0].Substring(0, 1); string ParticipantID_Header = RawData[0].Substring(1, 8); string FileID_Header = RawData[0].Substring(9, 8); string FileFormatVersion_Header = RawData[0].Substring(17, 5); string Filler__Header_01 = RawData[0].Substring(22, 6); string FileDate_Header = RawData[0].Substring(28, 8); string CompareZone_Header = RawData[0].Substring(36, 1); string Filler_Header_02 = RawData[0].Substring(37, 963); #endregion DateTime FileDateOfData = DateTime.ParseExact(FileDate_Header, "MMddyyyy", CultureInfo.InvariantCulture); CCRF.DeleteByDateAndBook(FileDateOfData, ParticipantID_Header.Substring(4, 4)); CCDF.DeleteByDateAndBook(FileDateOfData, ParticipantID_Header); List<ContractCompareDomesticObject> CCDAList = new List<ContractCompareDomesticObject>(); bool iterError = false; //skip first line and last line for header and trailer for (int i = 1; i < RawData.Count() - 1; i++) { #region Add Each item to CCDAList ContractCompareDomesticObject CCDO = new ContractCompareDomesticObject(); try { CCDO.DateOfData = FileDateOfData; CCDO.DateOfImport = DateTime.Now; CCDO.RecordType = RawData[i].Substring(0, 1); CCDO.ParticipantID = RawData[i].Substring(1, 8); CCDO.ContraPartyID = RawData[i].Substring(9, 8); CCDO.ActivityCode = RawData[i].Substring(17, 1); CCDO.UserContractInternalReferenceNumber = RawData[i].Substring(18, 15); CCDO.Filler1 = RawData[i].Substring(33, 5); CCDO.SecurityID = RawData[i].Substring(38, 12); CCDO.SecurityIDType = RawData[i].Substring(50, 1); CCDO.OpenQuantity = RawData[i].Substring(51, 14); CCDO.ContractValue = RawData[i].Substring(65, 18); CCDO.RebateRateCode = RawData[i].Substring(83, 1); CCDO.RebateFeeRate = RawData[i].Substring(84, 9); CCDO.CollateralType = RawData[i].Substring(93, 1); CCDO.Filler2 = RawData[i].Substring(94, 12); CCDO.DeliveryDateofDeal = RawData[i].Substring(106, 8); CCDO.CloseDateorTermDateofTransaction = RawData[i].Substring(114, 8); CCDO.Filler3 = RawData[i].Substring(122, 8); CCDO.UserContractInfo = RawData[i].Substring(130, 60); CCDO.SunGardUseOnly1 = RawData[i].Substring(190, 9); CCDO.ComparisonCode = RawData[i].Substring(199, 1); CCDO.InternalAccountNumber = RawData[i].Substring(200, 16); CCDO.Filler4 = RawData[i].Substring(216, 106); CCDO.SunGardUseOnly2 = RawData[i].Substring(322, 184); CCDO.MarginParameter = RawData[i].Substring(506, 6); CCDO.RoundingDirection = RawData[i].Substring(512, 1); CCDO.MarkRoundingFactor = RawData[i].Substring(513, 4); CCDO.Filler5 = RawData[i].Substring(517, 5); CCDO.AccruedBondInterestincludedinmarks = RawData[i].Substring(522, 1); CCDO.Filler6 = RawData[i].Substring(523, 1); CCDO.SunGardUseOnly3 = RawData[i].Substring(524, 60); CCDO.DividendFlowThrough = RawData[i].Substring(584, 6); CCDO.Filler7 = RawData[i].Substring(590, 1); CCDO.IncomeTrackingIndicator = RawData[i].Substring(591, 1); CCDO.SunGardUseOnly4 = RawData[i].Substring(592, 42); CCDO.Filler8 = RawData[i].Substring(634, 12); CCDO.SunGardUseOnly5 = RawData[i].Substring(646, 48); CCDO.OCCHedgeContract = RawData[i].Substring(694, 1); CCDO.CustodianID = RawData[i].Substring(695, 8); CCDO.SubAccount = RawData[i].Substring(703, 35); CCDO.Filler9 = RawData[i].Substring(738, 203); CCDO.SunGardUseOnly6 = RawData[i].Substring(941, 59); CCDAList.Add(CCDO); CCDF.InsertSingle(CCDO); } catch (Exception e3) { iterError = true; Utils.LogError("Issue with counter # " + i + " from RawData[I] in book " + ParticipantID_Header + " " + e3.Message); } #endregion } #region Footer Data int ft = RawData.Count() - 1; string RecordType_Footer = RawData[ft].Substring(0, 1); string ParticipantID_Footer = RawData[ft].Substring(1, 8); string DetailRecordCount_Footer = RawData[ft].Substring(9, 9); string Filler_Footer = RawData[ft].Substring(18, 982); #endregion ConvertToOrigForExistingContractCompareRawTable(CCDAList); if (iterError) { //send email log notifying that some lines were skipped and check app logs throw new Exception("Some lines were skipped while iterating RawData[i]. Please check app logs"); } } catch (Exception e2) { Utils.LogError("ForEach(FilesToImport) " + e2.Message); //send email log } } } catch (Exception e) { Utils.LogError(e.Message); //Send Email Log } }
public void InsertSingle(ContractCompareDomesticObject obj) { //Change to SP if required string sql = @"INSERT INTO ContractCompareDomestic(DateOfData, DateOfImport, RecordType, ParticipantID, ContraPartyID, ActivityCode, UserContractInternalReferenceNumber, Filler1, SecurityID, SecurityIDType, OpenQuantity, ContractValue, RebateRateCode, RebateFeeRate, CollateralType, Filler2, DeliveryDateofDeal, CloseDateorTermDateofTransaction, Filler3, UserContractInfo, SunGardUseOnly1, ComparisonCode, InternalAccountNumber, Filler4, SunGardUseOnly2, MarginParameter, RoundingDirection, MarkRoundingFactor, Filler5, AccruedBondInterestincludedinmarks, Filler6, SunGardUseOnly3, DividendFlowThrough, Filler7, IncomeTrackingIndicator, SunGardUseOnly4, Filler8, SunGardUseOnly5, OCCHedgeContract, CustodianID, SubAccount, Filler9, SunGardUseOnly6) VALUES(@DateOfData, @DateOfImport, @RecordType, @ParticipantID, @ContraPartyID, @ActivityCode, @UserContractInternalReferenceNumber, @Filler1, @SecurityID, @SecurityIDType, @OpenQuantity, @ContractValue, @RebateRateCode, @RebateFeeRate, @CollateralType, @Filler2, @DeliveryDateofDeal, @CloseDateorTermDateofTransaction, @Filler3, @UserContractInfo, @SunGardUseOnly1, @ComparisonCode, @InternalAccountNumber, @Filler4, @SunGardUseOnly2, @MarginParameter, @RoundingDirection, @MarkRoundingFactor, @Filler5, @AccruedBondInterestincludedinmarks, @Filler6, @SunGardUseOnly3, @DividendFlowThrough, @Filler7, @IncomeTrackingIndicator, @SunGardUseOnly4, @Filler8, @SunGardUseOnly5, @OCCHedgeContract, @CustodianID, @SubAccount, @Filler9, @SunGardUseOnly6)"; using (SqlConnection conn = new SqlConnection(cstr)) { SqlCommand cmd = new SqlCommand(sql, conn); //cmd.CommandType=CommandType.StoredProcedure; //cmd.Parameters.AddWithValue(","); cmd.Parameters.AddWithValue("@DateOfData", SetNullValue(obj.DateOfData)); cmd.Parameters.AddWithValue("@DateOfImport", SetNullValue(obj.DateOfImport)); cmd.Parameters.AddWithValue("@RecordType", SetNullValue(obj.RecordType)); cmd.Parameters.AddWithValue("@ParticipantID", SetNullValue(obj.ParticipantID)); cmd.Parameters.AddWithValue("@ContraPartyID", SetNullValue(obj.ContraPartyID)); cmd.Parameters.AddWithValue("@ActivityCode", SetNullValue(obj.ActivityCode)); cmd.Parameters.AddWithValue("@UserContractInternalReferenceNumber", SetNullValue(obj.UserContractInternalReferenceNumber)); cmd.Parameters.AddWithValue("@Filler1", SetNullValue(obj.Filler1)); cmd.Parameters.AddWithValue("@SecurityID", SetNullValue(obj.SecurityID)); cmd.Parameters.AddWithValue("@SecurityIDType", SetNullValue(obj.SecurityIDType)); cmd.Parameters.AddWithValue("@OpenQuantity", SetNullValue(obj.OpenQuantity)); cmd.Parameters.AddWithValue("@ContractValue", SetNullValue(obj.ContractValue)); cmd.Parameters.AddWithValue("@RebateRateCode", SetNullValue(obj.RebateRateCode)); cmd.Parameters.AddWithValue("@RebateFeeRate", SetNullValue(obj.RebateFeeRate)); cmd.Parameters.AddWithValue("@CollateralType", SetNullValue(obj.CollateralType)); cmd.Parameters.AddWithValue("@Filler2", SetNullValue(obj.Filler2)); cmd.Parameters.AddWithValue("@DeliveryDateofDeal", SetNullValue(obj.DeliveryDateofDeal)); cmd.Parameters.AddWithValue("@CloseDateorTermDateofTransaction", SetNullValue(obj.CloseDateorTermDateofTransaction)); cmd.Parameters.AddWithValue("@Filler3", SetNullValue(obj.Filler3)); cmd.Parameters.AddWithValue("@UserContractInfo", SetNullValue(obj.UserContractInfo)); cmd.Parameters.AddWithValue("@SunGardUseOnly1", SetNullValue(obj.SunGardUseOnly1)); cmd.Parameters.AddWithValue("@ComparisonCode", SetNullValue(obj.ComparisonCode)); cmd.Parameters.AddWithValue("@InternalAccountNumber", SetNullValue(obj.InternalAccountNumber)); cmd.Parameters.AddWithValue("@Filler4", SetNullValue(obj.Filler4)); cmd.Parameters.AddWithValue("@SunGardUseOnly2", SetNullValue(obj.SunGardUseOnly2)); cmd.Parameters.AddWithValue("@MarginParameter", SetNullValue(obj.MarginParameter)); cmd.Parameters.AddWithValue("@RoundingDirection", SetNullValue(obj.RoundingDirection)); cmd.Parameters.AddWithValue("@MarkRoundingFactor", SetNullValue(obj.MarkRoundingFactor)); cmd.Parameters.AddWithValue("@Filler5", SetNullValue(obj.Filler5)); cmd.Parameters.AddWithValue("@AccruedBondInterestincludedinmarks", SetNullValue(obj.AccruedBondInterestincludedinmarks)); cmd.Parameters.AddWithValue("@Filler6", SetNullValue(obj.Filler6)); cmd.Parameters.AddWithValue("@SunGardUseOnly3", SetNullValue(obj.SunGardUseOnly3)); cmd.Parameters.AddWithValue("@DividendFlowThrough", SetNullValue(obj.DividendFlowThrough)); cmd.Parameters.AddWithValue("@Filler7", SetNullValue(obj.Filler7)); cmd.Parameters.AddWithValue("@IncomeTrackingIndicator", SetNullValue(obj.IncomeTrackingIndicator)); cmd.Parameters.AddWithValue("@SunGardUseOnly4", SetNullValue(obj.SunGardUseOnly4)); cmd.Parameters.AddWithValue("@Filler8", SetNullValue(obj.Filler8)); cmd.Parameters.AddWithValue("@SunGardUseOnly5", SetNullValue(obj.SunGardUseOnly5)); cmd.Parameters.AddWithValue("@OCCHedgeContract", SetNullValue(obj.OCCHedgeContract)); cmd.Parameters.AddWithValue("@CustodianID", SetNullValue(obj.CustodianID)); cmd.Parameters.AddWithValue("@SubAccount", SetNullValue(obj.SubAccount)); cmd.Parameters.AddWithValue("@Filler9", SetNullValue(obj.Filler9)); cmd.Parameters.AddWithValue("@SunGardUseOnly6", SetNullValue(obj.SunGardUseOnly6)); try { conn.Open(); cmd.ExecuteNonQuery(); } //Only use whats in catch block if you included Log.cs catch (SqlException ex) { Utils.LogError(ex.Message); } } }