Querying data from two databases in C#

Question

I currently have a query where I am getting data from 2 different databases which is getting appended to one list called "accountbuys".

1. My first table has three data entry(3 accounts which want to buy stocks

2. The next table with 17 data points(17 stocks to buy)

I am merging these tables and getting output

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

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!

This is my 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.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;
}

EDIT:

Split the tables into two different lists and merged them later. This is working well now. Seems good for scaling too.

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.GetString(reader1.GetOrdinal("PortfolioDetail"));
                   // accountb.PortfolioDescription = reader1.GetString(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.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;

    }

Answer 1

The following provides the cross join in the C# layer (not saying it is the best possible solution, but it gets you closer to ready):

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();
}

Edit: removed offending parentheses.