Returning and Working With Multiple Result Sets in SQL

Big white library with a lot of books

When working with a database it can often be useful to return more than one result set from a stored procedure. This can help cut down on the number of database calls, especially if the data you’re gathering is closely related. Here’s a few quick tips on how you can return and work with multiple result sets using C# with MSSQL.


C#/MSSQL Return Multiple Result Sets


The example database I’ll use for this will be that of the fictitious Electric Blue Airlines™ (Trademark pending (just kidding, that’s not true)). We’ll have two tables, one for the Frequent Flyer Member and one for the flight/ticket information. Here’s what a very simple stored procedure may look like when doing this:


SELECT MemberID, FullName, FrequentFlyerNumber FROM dbo.FrequentFlyerMember;

SELECT TicketID, MemberID, FlightNumber, DepartureDate, ArrivalDate FROM dbo.ScheduledFlight;


The painfully oversimplified stored procedure example from above will return two sets of data. Now, we need a way to capture and work with that data in our code. When I first learned that you could do this I was rather confused and a little uncertain as to how to handle that data. I felt like returning multiple sets was sort-of loose and gross. But, after researching for a while it actually makes a lot of sense, if used properly. The next step shows what the code may look like in order to use this data:


List<FrequentFlyerMember> members = new List<FrequentFlyerMember>(); List<ScheduledFlight> flights = new List<ScheduledFlight>();
using (SqlConnection conn = new SqlConnection(Database.MyDbConn))
{
    conn.Open();
    using (SqlCommand dbCommand = conn.CreateCommand())
    {
        dbCommand.CommandText = commandText;
        using(SqlDataReader reader = dbCommand.ExecuteReader())
        {
            while(reader.Read())
            {
                members.Add(new FrequentFlyerMember()
                {
                    MemberID = (int)reader["MemberID"],
                    FullName = (string)reader["FullName"],
                    FrequentFlyerNumber = (int)reader["FrequentFlyerNumber"]
                });
            }

            //Here's where we get the next result set
            reader.NextResult();

            while(reader.Read())
            {
                flights.Add(new ScheduledFlight()
                {
                    TicketID = (int)reader["TicketID"],
                    MemberID = (int)reader["MemberID"],
                    FlightNumber = (int)reader["FlightNumber"],
                    DepartureDate = (DateTime)reader["DepartureDate"],
                    ArrivalDate = (DateTime)reader["DepartureDate"]
                });
            }
        }
    }
}


So, there you have it. We can now gracefully return multiple result sets from our stored procedures. Hopefully this helps you in some way, or at least gets you going in your own project. This is not tested code and there’s probably some mistakes in it’s implementation. But, that’s for you to debug and for me to not worry about. This is just a starting point. Happy coding!


A great example from StackOverflow that I got this from


Image courtesy of  Tobias Fischer

Leave a Reply

Your email address will not be published. Required fields are marked *