使用sqldatareader读取多行数据

Question

我有下面的sql语句，如下所示：

SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber

ViewSectorInvestments领域：

AccountNumber
SectorName
AmountInvested

我试图将每个行业的投资额与总投资额进行比较。因此公式为：AmountInvestments/TotalInvestments*100

我的代码如下：

    string DMConnectionString = ConfigurationManager.ConnectionStrings["DMConnectionString"].ConnectionString;
    SqlConnection DMConnection = new SqlConnection(DMConnectionString);
    DMConnection.ConnectionString = DMConnectionString;
    string DMCommandText = "SELECT Name,RiskProfile,AccountNumber,TotalInvestments FROM ViewClientDetails WHERE AccountNumber = @AccountNumber; SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber ;SELECT * FROM ViewStockTypeInvestments WHERE AccountNumber = @AccountNumber  ";
    SqlCommand DMCommand = new SqlCommand(DMCommandText, DMConnection);
    DMCommand.Parameters.AddWithValue("@AccountNumber", lb_AcctNum.Text);
    DMConnection.Open();
    SqlDataReader DMReader = DMCommand.ExecuteReader();
    ArrayList SectorArray = new ArrayList();
    ArrayList StockTypeArray = new ArrayList();
    while (DMReader.Read())
    {
        CustName.Text = DMReader["Name"].ToString();
        lb_Risk.Text = DMReader["RiskProfile"].ToString();
        T_Investment.Text = DMReader.GetDecimal(DMReader.GetOrdinal("TotalInvestments")).ToString("N2");
        Client_RiskProfile.Text = DMReader["RiskProfile"].ToString();
        //encounter error when i add the datas into arraylist. 
        //System.IndexOutOfRangeException: SectorName
        SectorArray.Add(DMReader.GetOrdinal("SectorName").ToString());
        StockTypeArray.Add(DMReader.GetOrdinal("BlueChipName").ToString());

        foreach( Object objReader in SectorArray){
        //compute the percentage of amount invested in each sector
        //check if the percentage is more than 25%
        //if it is more than 25% lbMsg (an label) shows the name of the sector.
        }
    }
    DMReader.Close();
    DMConnection.Close();
}

当我测试sql语句时：

SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber

我得到的结果是：

AccountNumber  SectorName              AmountInvested
1001         Commerce            97230.00000
1001         Construction            389350.00000
1001         Finance             222830.00000
1001         Hotel                     14910.00000
1001         Loans                     105070.00000
1001         Manufacturing           1232210.00000
1001         Mining/Quarrying        32700.00000

遇到System.IndexOutOfRangeException：SectorName。我的代码出了什么问题？请给我建议。提前谢谢。

Answer 1

string DMCommandText = "SELECT Name,RiskProfile,AccountNumber,TotalInvestments FROM ViewClientDetails WHERE AccountNumber = @AccountNumber; SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber ;SELECT * FROM ViewStockTypeInvestments WHERE AccountNumber = @AccountNumber ";

此CommandText包含多个查询。只有最后一条SELECT语句的结果才会返回到SqlDataReader。

SectorArray.Add(DMReader.GetOrdinal("SectorName").ToString());

您正试图访问SqlDataReader中名为"SectorName"的字段的列序号。导致异常的问题可能是该列不存在，但很难说，因为您在CommandText中使用了SELECT*。