public void ProcessInboundSalesFile() { try { using (var reader = new StreamReader(_path)) using (var csv = new CsvReader(reader)) { //Truncate inbound sales staging file var dal = new DapperDatabaseAccess <BroadridgeSalesInbound>(_connectionString); dal.SqlExecute("dbo.prc_BroadridgeSales_Staging_Truncate", null); //Ingest sales into staging table List <BroadridgeSalesInbound> inboundSales = csv.GetRecords <BroadridgeSalesInbound>().ToList(); foreach (var item in inboundSales) { var p = new DynamicParameters(); p.Add("@TradeID", item.TradeID); p.Add("@TransactionCodeOverrideDescription", item.TransactionCodeOverrideDescription); p.Add("@TradeDate", item.TradeDate); p.Add("@SettledDate", item.SettledDate); p.Add("@SuperSheetDate", item.SuperSheetDate); p.Add("@TradeAmount", item.TradeAmount); p.Add("@System", item.System); p.Add("@DealerNum", item.DealerNum); p.Add("@DealerBranchBranchCode", item.DealerBranchBranchCode); p.Add("@RepCode", item.RepCode); p.Add("@FirmId", item.FirmId); p.Add("@FirmName", item.FirmName); p.Add("@OfficeAddressLine1", item.OfficeAddressLine1); p.Add("@OfficeCity", item.OfficeCity); p.Add("@OfficeRegionRefCode", item.OfficeRegionRefCode); p.Add("@OfficePostalCode", item.OfficePostalCode); p.Add("@PersonFirstName", item.PersonFirstName); p.Add("@PersonLastName", item.PersonLastName); p.Add("@LineOfBusiness", item.LineOfBusiness); p.Add("@Channel", item.Channel); p.Add("@Region", item.Region); p.Add("@Territory", item.Territory); p.Add("@ProductNasdaqSymbol", item.ProductNasdaqSymbol); p.Add("@ProductName", item.ProductName); p.Add("@AccountTANumber", item.AccountTANumber); p.Add("@AccountId", item.AccountId); p.Add("@ExternalAccountNumber", item.ExternalAccountNumber); p.Add("@HoldingId", item.HoldingId); p.Add("@HoldingExternalAccountNumber", item.HoldingExternalAccountNumber); p.Add("@HoldingName", item.HoldingName); dal.SqlExecute("dbo.prc_BroadridgeSales_Staging_Save", p); } //Transform from staging to production var parms = new DynamicParameters(); parms.Add("@ReportingDate", DateTimeHandlers.GetQuarterEndDateFromYearQuarter(_reportingDate).ToString("yyyyMMdd")); // parms.Add("@ReportingDate", _reportingDate); dal.SqlExecute("dbo.prc_BroadridgeSales_Transform", parms); } } catch (Exception e) { Console.WriteLine(e); throw; } }
public void ProcessInboundAssetsFile() { try { using (var reader = new StreamReader(_path)) using (var csv = new CsvReader(reader)) { //Truncate inbound sales staging file var dal = new DapperDatabaseAccess <BroadridgeAssetsInbound>(_connectionString); dal.SqlExecute("dbo.prc_BroadridgeAssets_Staging_Truncate", null); //Ingest sales into staging table List <BroadridgeAssetsInbound> inboundAssets = csv.GetRecords <BroadridgeAssetsInbound>().ToList(); // DbHandlers.BulkInsertToDatabase(ListDataTable.ListToDataTable(inboundAssets), _connectionString, 1000, _metaData.stagingTable); foreach (var item in inboundAssets) { var p = new DynamicParameters(); p.Add("@System", item.System); p.Add("@FirmName", item.FirmName); p.Add("@FirmId", item.FirmId); p.Add("@FirmCRDNumber", item.FirmCRDNumber); p.Add("@HoldingId", item.HoldingId); p.Add("@HoldingExternalAccountNumber", item.HoldingExternalAccountNumber); p.Add("@HoldingName", item.HoldingName); p.Add("@HoldingStartDate", item.HoldingStartDate); p.Add("@HoldingCreateDate", item.HoldingCreateDate); p.Add("@MostRecentMonthAssetBalance", item.MostRecentMonthAssetBalance); p.Add("@Month1AgoAssetBalance", item.Month1AgoAssetBalance); p.Add("@Month2AgoAssetBalance", item.Month2AgoAssetBalance); p.Add("@Month3AgoAssetBalance", item.Month3AgoAssetBalance); p.Add("@ProductName", item.ProductName); p.Add("@ProductType", item.ProductType); p.Add("@Channel", item.Channel); p.Add("@Region", item.Region); p.Add("@Territory", item.Territory); p.Add("@PersonCRDNumber", item.PersonCRDNumber); p.Add("@PersonFirstName", item.PersonFirstName); p.Add("@PersonLastName", item.PersonLastName); p.Add("@PersonId", item.PersonId); p.Add("@OfficeAddressLine1", item.OfficeAddressLine1); p.Add("@OfficeAddressLine2", item.OfficeAddressLine2); p.Add("@OfficeCity", item.OfficeCity); p.Add("@OfficeRegionRefCode", item.OfficeRegionRefCode); p.Add("@OfficePostalCode", item.OfficePostalCode); p.Add("@PersonBrokerTeamFlag", item.PersonBrokerTeamFlag); p.Add("@Month4AgoAssetBalance", item.Month4AgoAssetBalance); p.Add("@Month5AgoAssetBalance", item.Month5AgoAssetBalance); p.Add("@Month6AgoAssetBalance", item.Month6AgoAssetBalance); p.Add("@Month7AgoAssetBalance", item.Month7AgoAssetBalance); p.Add("@Month8AgoAssetBalance", item.Month8AgoAssetBalance); p.Add("@Month9AgoAssetBalance", item.Month9AgoAssetBalance); p.Add("@Month10AgoAssetBalance", item.Month10AgoAssetBalance); p.Add("@Month11AgoAssetBalance", item.Month11AgoAssetBalance); p.Add("@Month12AgoAssetBalance", item.Month12AgoAssetBalance); p.Add("@HoldingAddressLine1", item.HoldingAddressLine1); p.Add("@AccountTANumber", item.AccountTANumber); p.Add("@ExternalAccountNumber", item.ExternalAccountNumber); p.Add("@AccountId", item.AccountId); dal.SqlExecute("dbo.prc_BroadridgeAssets_Staging_Save", p); } //Transform from staging to production var parms = new DynamicParameters(); parms.Add("@ReportingDate", DateTimeHandlers.GetQuarterEndDateFromYearQuarter(_reportingDate).ToString("yyyyMMdd")); dal.SqlExecute("dbo.prc_BroadridgeAssets_Transform", parms); } } catch (Exception e) { Console.WriteLine(e); throw; } }