使用sqldatareader读取多行数据

发布于 2025-01-01 10:23:48 字数 2763 浏览 0 评论 0原文

我有以下 sql 语句如下:

SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber

ViewSectorInvestments 中的字段:

AccountNumber
SectorName
AmountInvested

我正在尝试根据总投资计算每个部门的 AmountInvested。 所以公式将是: AmountInvested/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。 我的代码有什么问题吗? 请给我建议。提前致谢。

I've the below sql statement as follows:

SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber

Fields in ViewSectorInvestments:

AccountNumber
SectorName
AmountInvested

I'm trying to compute the AmountInvested in each sector against the total investments.
So the formula will be: AmountInvested/TotalInvestments * 100

my code is as follows:

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

When i test out the sql statement :

SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber

The result i got is :

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

I encountered System.IndexOutOfRangeException: SectorName.
What's wrong with my code?
Please advice me. Thanks in advance.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

執念 2025-01-08 10:23:48

string DMCommandText = "SELECT 名称、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 * 。

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

This CommandText contains multiple queries. Only the results from the last SELECT statement will be returned to the SqlDataReader.

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

You are trying to access the column ordinal of a field called "SectorName" in your SqlDataReader. The problem causing your exception is probably that the column doesn't exist, but it's hard to say since you are using SELECT * in your CommandText.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文