IDataReader, .NET Core & Multiple Tables

With .NET Core maturing it’s worth noting a missing class previously in System.Data. It’s probably one you’ve forgotten about since the early days. System.Data.DataSet seems to have seen its day (at least in .NET Core). Whether you liked it or not it was useful for receiving multiple data sets.

What do you do now? Now we have to work directly with the data reader. Turns out it’s really easy, I just hadn’t ever looked at it before. If we have the following two tables:

ID	Description
1	1-1
2	1-2
3	1-3
4	1-4
5	1-5

ID	Description
1	2-1
2	2-2
3	2-3
4	2-4
5	2-5

Then we can get them both from a single call to the database sending two SELECT statements.

SELECT [Id],[Description] 
FROM [dbo].[Table1]; 

SELECT [Id],[Description] 
FROM [dbo].[Table2];

In order to read the two tables, we need to use two methods off the IDataReader interface.

• .Read() will advance the reader to the next row in the table, returning false when there are no more rows left.
• .NextResult() will advance the reader to the next table, again returning false when there are no more data sets.

The full code to get both the above tables in a single database call might look like this:

using (var connection = new SqlConnection(CONNECTION_STRING))
{
  const int WIDTH = 15;
  const string COMMAND_TEXT =
    @"SELECT [Id],[Description] 
     FROM [dbo].[Table1]; 
     SELECT [Id],[Description] 
     FROM [dbo].[Table2];";

  using (var command = connection.CreateCommand())
  {
    command.CommandText = COMMAND_TEXT;
    command.CommandType = CommandType.Text;
    connection.Open();

    using (var dataReader = command.ExecuteReader())
    {
      do
      {
        var columnCount = dataReader.FieldCount;
        WriteLine();
        WriteLine($"|{new string('-', WIDTH)}|{new string('-', WIDTH)}|");
        WriteLine($"|{dataReader.GetName(0),WIDTH}|{dataReader.GetName(1),WIDTH}|");
        WriteLine($"|{new string('-', WIDTH)}|{new string('-', WIDTH)}|");

        while (dataReader.Read())
        {
          for (var i = 0; i < columnCount; i++)
          {
            Write($"|{dataReader[i],WIDTH}");
          }
          WriteLine("|");
        }
        WriteLine($"|{new string('-', WIDTH)}|{new string('-', WIDTH)}|");
    } while (dataReader.NextResult());
  }
}

This will give you a nice output too:

|---------------|---------------|
|             Id|    Description|
|---------------|---------------|
|              1|            1-1|
|              2|            1-2|
|              3|            1-3|
|              4|            1-4|
|              5|            1-5|
|---------------|---------------|

|---------------|---------------|
|             Id|    Description|
|---------------|---------------|
|              1|            2-1|
|              2|            2-2|
|              3|            2-3|
|              4|            2-4|
|              5|            2-5|
|---------------|---------------|

No need to miss the DataSet any more.

For almost all use-cases this will be much more memory efficient as well.

Thanks for reading.

Please feel free to get in contact @BanksySan.