CODEWITHSUNDEEP
c#c#-4.0sqldatareader
user1890098
user1890098

Reputation: 495

How to handle Multiple Results by Using the DataReader in Visual C#?

I have a stored procedure which returns two tables. Table 1 has for example {1,2,3,4,5}, the table 2 will have have {a,b,c,d,e}. For example {1,a}, {2,b} similarly all are related.

In my reader, I have to read all these data into a List. Object will have have two properties where {1,a} go. This is the code I have now,

while(reader.Read())
        {
            Transaction transaction = null;
            transactions.Add(transaction = new Transaction()
            {
                TranID = reader.GetInt64(reader.GetOrdinal("TranID"))
            });

            if (reader.NextResult())
            {
                while (reader.Read())
                {
                    transaction.Amounts.Add(new Fund
                    {
                        Amount = reader.GetDecimal(reader.GetOrdinal("Amount")),
                    });
                }
            }
        }

What's happening here is if I return 10 Transaction Ids and 10 Amounts, my final result has only 1 Transaction and 10 Amounts mapped to it. How do I get 10 Transactions with equivalent Amount?

Upvotes: 0

Views: 2173

Answers (1)

Chetan
Chetan

Reputation: 6891

You can not access multiple resultsets this way from SqlDataReader. You can access all the rows from one resultset at a time. If you move to the nextresult set, you can not go back to the previous result set.

Following is the sample code of how you retrieve data from multiple resultsets of SqlDataReader.

while (reader.Read())
{
     Console.WriteLine(reader.GetString(0));
}

if (reader.NextResult())
{
    while (reader.Read())
    {
        Console.WriteLine(reader.GetString(0));
    }
}

Upvotes: 1

Related Questions