title: Querying data from two databases in C#
body: <p>I currently have a query where I am getting data from 2 different databases which is getting appended to one list called "accountbuys".</p>
  
  <ol>
  <li><p>My first table has three data entry(3 accounts which want to buy stocks</p></li>
  <li><p>The next table with 17 data points(17 stocks to buy)</p></li>
  </ol>
  
  <p>I am merging these tables and getting output <a href="https://i.stack.imgur.com/ozFoA.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ozFoA.jpg" alt="enter image description here"></a></p>
  
  <p>However,the output I want , should have 17 data points repeated each time for the 3 different accounts, so that the end user can drill down and buy whichever stock he wants<a href="https://i.stack.imgur.com/G3KQH.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/G3KQH.jpg" alt="enter image description here"></a></p>
  
  <p>PS: If what I am trying to ask is not clear, please be polite and tell me so. Please try not to be rude, I am still learning and new to StackExchange! </p>
  
  <p>This is my code :`</p>
  
  <pre><code>public List&lt;BuySellModel&gt; GetBuyDataWthAccount()
  {
      List&lt;BuySellModel&gt; accountbuys = new List&lt;BuySellModel&gt;();
  
      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.GetString(reader1.GetOrdinal("PortfolioAccountNumber"));
                  accountb.PortfolioDescription = reader1.GetString(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.GetString(reader.GetOrdinal("Symbol"));
                          buy.Segment = reader.GetString(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;
  }
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>Split the tables into two different lists and merged them later. This is working well now. Seems good for scaling too.</p>
  
  <pre><code>public List&lt;BuySellModel&gt; GetBuyDataWthAccount()
      {
          List&lt;BuySellModel&gt; accountbuys = new List&lt;BuySellModel&gt;();
  
          List&lt;Account&gt; accounts = new List&lt;Account&gt;();
  
  
          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.GetString(reader1.GetOrdinal("PortfolioDetail"));
                     // accountb.PortfolioDescription = reader1.GetString(reader1.GetOrdinal("PortfolioDescription"));
                      accountb.AccountAmount = reader1.GetDecimal(reader1.GetOrdinal("TotalValue"));
  
                      accounts.Add(accountb);
                  }
              }
              //List&lt;BuyReits&gt; buys = new List&lt;BuyReits&gt;();
              if (reader.HasRows &amp;&amp; accounts.Count &gt; 0)
              {
                  while (reader.Read())
                  {
                      foreach (var acc in accounts)
                      {
                          BuySellModel buy = new BuySellModel();
                          buy.Symbol = reader.GetString(reader.GetOrdinal("Symbol"));
                          buy.Segment = reader.GetString(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;
  
      }
  </code></pre>