Interrogation des données de deux bases de données en C #

J'ai actuellement une requête où j'obtiens des données de 2 bases de données différentes qui sont ajoutées à une list appelée "accountbuys".

  1. Ma première table a trois inputs de données (3 counts qui veulent acheter des actions

  2. Le tableau suivant avec 17 points de données (17 stocks à acheter)

Je fusionne ces tables et j'obtiens des résultats entrez la description de l'image ici

Cependant, la sortie que je veux, devrait avoir 17 points de données répétés à chaque fois pour les 3 différents counts, de sorte que l'user final puisse forer vers le bas et acheter n'importe quel stock qu'il veut entrez la description de l'image ici

PS: Si ce que j'essaie de requestr n'est pas clair, s'il vous plaît soyez poli et dites-le moi. S'il vous plaît essayez de ne pas être impoli, je suis encore en train d'apprendre et de nouveau à StackExchange!

Ceci est mon code: `

public List<BuySellModel> GetBuyDataWthAccount() { List<BuySellModel> accountbuys = new List<BuySellModel>(); using (SqlConnection connectionreit = new SqlConnection(HubConnection)) using (SqlConnection connection = new SqlConnection(PMConnection)) { connectionhub.Open(); connection.Open(); SqlCommand command3 = new SqlCommand(@"SELECT distinct(table1.name) as 'Symbol' ,table2.Segment as 'Segment',table2.AllocationAmount as 'AllocationAmount',table2.PX_LAST as 'Price', table1.CUR_MKT_CAP as 'CMC',table1.FCFY_WITH_CUR_MKT_CAP as 'FCMC',table1.ROIC as 'ROIC', table1.ROICDELTA as 'ROICD' FROM View_REIT_Model_And_Holdings as table1 INNER JOIN [MostRecentlyInModelSelected] as table2 ON table1.name = table2.Ticker WHERE table1.AllocationAmount != -1 AND NOT EXISTS (SELECT NULL FROM [ViewPCData] as table3 WHERE table1.name = table3.Symbol AND table2.Segment = table3.SubsectorDescription AND table3.Objective = 'REITS' AND table3.SectorDescription != 'NULL' AND table3.SubsectorDescription != 'NULL')", connectionreit); command3.CommandType = CommandType.Text; SqlCommand command4 = new SqlCommand("SELECT PortfolioAccountNumber, PortfolioDescription, SUM(TotalValue) as 'TotalValue' FROM [ViewPCData] WHERE Objective = 'REITS' GROUP BY PortfolioAccountNumber,PortfolioDescription", connection); command4.CommandType = CommandType.Text; var reader = command3.ExecuteReader(); var reader1 = command4.ExecuteReader(); if (reader1.HasRows) { while (reader1.Read()) { BuySellModel accountb = new BuySellModel(); accountb.PortfolioAccount = reader1.GetSsortingng(reader1.GetOrdinal("PortfolioAccountNumber")); accountb.PortfolioDescription = reader1.GetSsortingng(reader1.GetOrdinal("PortfolioDescription")); accountb.AccountAmount = reader1.GetDecimal(reader1.GetOrdinal("TotalValue")); accountbuys.Add(accountb); if (reader.HasRows) { //foreach(var account in accountbuys) //{ while (reader.Read()) { BuySellModel buy = new BuySellModel(); buy.Symbol = reader.GetSsortingng(reader.GetOrdinal("Symbol")); buy.Segment = reader.GetSsortingng(reader.GetOrdinal("Segment")); //if (accountNumber == "soand os") //{ // 1/3 of totalaccountvalue buy.AllocationAmount = (reader.GetDouble(reader.GetOrdinal("AllocationAmount"))); //} buy.Price = reader.GetDouble(reader.GetOrdinal("Price")); buy.MarketValue = reader.GetDouble(reader.GetOrdinal("CMC")); buy.FCFY = reader.GetDouble(reader.GetOrdinal("FCMC")); buy.ROIC = reader.GetDouble(reader.GetOrdinal("ROIC")); buy.ROICdelta = reader.GetDouble(reader.GetOrdinal("ROICD")); buy.Buy = true; //account1 = account.accountnumber; accountbuys.Add(buy); } //} //for loop } } // accounts } //reader1.hasrows connectionhub.Close(); connection.Close(); } return accountbuys; } 

MODIFIER:

Diviser les tables en deux lists différentes et les merge plus tard. Cela fonctionne bien maintenant. Cela semble bon pour la mise à l'échelle aussi.

 public List<BuySellModel> GetBuyDataWthAccount() { List<BuySellModel> accountbuys = new List<BuySellModel>(); List<Account> accounts = new List<Account>(); using (SqlConnection connection = new SqlConnection(PMConnection)) { connection.Open(); SqlCommand command3 = new SqlCommand(@"SELECT distinct(table1.name) as 'Symbol' ,table2.Segment as 'Segment',table2.AllocationAmount as 'AllocationAmount',table2.PX_LAST as 'Price', table1.CUR_MKT_CAP as 'CMC',table1.FCFY_WITH_CUR_MKT_CAP as 'FCMC',table1.ROIC as 'ROIC', table1.ROICDELTA as 'ROICD' FROM View_Model_And_Holdings as table1 INNER JOIN [MostRecentlyInModelSelected] as table2 ON table1.name = table2.Ticker WHERE table1.AllocationAmount != -1 AND NOT EXISTS (SELECT NULL FROM [ViewPCData] as table3 WHERE table1.name = table3.Symbol AND table2.Segment = table3.SubsectorDescription AND table3.Objective = 'STOCKS' AND table3.SectorDescription != 'NULL' AND table3.SubsectorDescription != 'NULL')", connectionreit); command3.CommandType = CommandType.Text; SqlCommand command4 = new SqlCommand("SELECT PortfolioDetail , SUM(TotalValue) as 'TotalValue' FROM [ViewPCData] WHERE Objective = 'STOCKS' GROUP BY PortfolioAccountNumber,PortfolioDescription", connection); command4.CommandType = CommandType.Text; var reader = command3.ExecuteReader(); var reader1 = command4.ExecuteReader(); if (reader1.HasRows) { while (reader1.Read()) { Account accountb = new Account(); accountb.PortfolioDetail = reader1.GetSsortingng(reader1.GetOrdinal("PortfolioDetail")); // accountb.PortfolioDescription = reader1.GetSsortingng(reader1.GetOrdinal("PortfolioDescription")); accountb.AccountAmount = reader1.GetDecimal(reader1.GetOrdinal("TotalValue")); accounts.Add(accountb); } } //List<BuyReits> buys = new List<BuyReits>(); if (reader.HasRows && accounts.Count > 0) { while (reader.Read()) { foreach (var acc in accounts) { BuySellModel buy = new BuySellModel(); buy.Symbol = reader.GetSsortingng(reader.GetOrdinal("Symbol")); buy.Segment = reader.GetSsortingng(reader.GetOrdinal("Segment")); buy.AllocationAmount = (reader.GetDouble(reader.GetOrdinal("AllocationAmount"))); buy.Price = reader.GetDouble(reader.GetOrdinal("Price")); //buy.Quantity = reader.GetInt32((reader.GetOrdinal("AllocationAmount"))/(reader.GetOrdinal("Price"))); buy.MarketValue = reader.GetDouble(reader.GetOrdinal("CMC")); buy.FCFY = reader.GetDouble(reader.GetOrdinal("FCMC")); buy.ROIC = reader.GetDouble(reader.GetOrdinal("ROIC")); buy.ROICdelta = reader.GetDouble(reader.GetOrdinal("ROICD")); buy.Buy = true; buy.PortfolioAccount = acc.PortfolioDetail; buy.AccountAmount = acc.AccountAmount; accountbuys.Add(buy); } } } connection.Close(); } return accountbuys; } 

Ce qui suit fournit la jointure croisée dans la couche C # (ne pas dire que c'est la meilleure solution possible, mais cela vous rapproche de prêt):

 using (SqlConnection connectionhub = new SqlConnection(HubConnection)) using (SqlConnection connection = new SqlConnection(PMConnection)) { connectionhub.Open(); connection.Open(); SqlCommand command3 = new SqlCommand(@" SELECT distinct(table1.name) as 'Symbol', table2.Segment as 'Segment', table2.AllocationAmount as 'AllocationAmount', table2.PX_LAST as 'Price', table1.CUR_MKT_CAP as 'CMC', table1.FCFY_WITH_CUR_MKT_CAP as 'FCMC', table1.ROIC as 'ROIC', table1.ROICDELTA as 'ROICD' FROM View_REIT_Model_And_Holdings as table1 INNER JOIN [MostRecentlyInModelSelected] as table2 ON table1.name = table2.Ticker WHERE table1.AllocationAmount != -1 AND NOT EXISTS (SELECT NULL FROM [ViewPCData] as table3 WHERE table1.name = table3.Symbol AND table2.Segment = table3.SubsectorDescription AND table3.Objective = 'REITS' AND table3.SectorDescription != 'NULL' AND table3.SubsectorDescription != 'NULL')", connectionreit); command3.CommandType = CommandType.Text; SqlCommand command4 = new SqlCommand(@" SELECT PortfolioAccountNumber, PortfolioDescription, SUM(TotalValue) as 'TotalValue' FROM [ViewPCData] WHERE Objective = 'REITS' GROUP BY PortfolioAccountNumber, PortfolioDescription", connection); command4.CommandType = CommandType.Text; var stocksDS = new DataSet(); var stocksDA = new System.Data.SqlClient.SqlDataAdapter(); stocksDA.SelectCommand = command3 stocksDA.Fill(stocksDS, "stocks"); var acctsDS = new DataSet(); var acctsDA = new System.Data.SqlClient.SqlDataAdapter(); acctsDA.SelectCommand = command4 acctsDA.Fill(acctsDS, "accts"); var stocks = stocksDS.Tables["stocks"].AsEnumerable(); var accts = acctsDS.Tables["accts"].AsEnumerable(); var results = (from stocksDR in stocks from acctsDR in accts select new BuySellModel { PortfolioAccount = acctsDR["PortfolioAccountNumber"], PortfolioDescription = acctsDR["PortfolioAccountDescription"], AccountAmount = acctsDR["TotalValue"], Symbol = stocksDR["Symbol"], Segment = stocksDR["Segment"], AllocationAmount = stocksDR["AllocationAmount"], Price = stocksDR["Price"], MarketValue = stocksDR["CMC"], FCFY = stocksDR["FCMC"], ROIC = stocksDR["ROIC"], ROICdelta = stocksDR["ROICD"], Buy = true }); foreach (BySellModel buy in results) { accountBuys.Add(buy); } connectionhub.Close(); connection.Close(); } 

Modifier: enlevé parenthèses fautives.

Sans modifier votre code C #, il est possible d'get ce que vous voulez en changeant la requête SQL d'une requête INNER JOIN en une requête CROSS JOIN.

Quelques mises en garde concernant cette approche si:

  1. Si vous incluez une clause WHERE, la requête agira comme une INNER JOIN.

  2. Les jointures croisées peuvent être sensiblement plus lentes, car la requête cherche à faire toutes les combinaisons possibles. Avec seulement 51 combinaisons (3 * 17) disponibles, ce ne sera pas un problème important, mais si vous cherchez à augmenter le nombre de clients et d'actions, cela deviendra de plus en plus mauvais en termes de performance.