How to Efficiently Fill a DataSet with Multiple Tables Using a DataReader?

Filling a DataSet with Multiple Tables Using a DataReader

When working with a DataSet that contains multiple tables with one-to-many relationships, it's possible to fill it using a DataReader. However, the default approach of using a single DataReader may not capture data from all tables.

To overcome this limitation, you can use the following approach:

using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace SampleApp
{
    public class DataSetWithTables
    {
        private SqlConnection connection;

        public DataSet SelectOne(int id)
        {
            DataSet result = new DataSet();
            string query = @"select * from table1; select * from table2 where table1_id = @ID;";
            using (connection = new SqlConnection("ConnectionString"))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("ID", id);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        DataTable table1 = new DataTable("Table1");
                        DataTable table2 = new DataTable("Table2");
                        table1.Load(reader);
                        if (reader.NextResult())
                        {
                            table2.Load(reader);
                        }
                        result.Tables.Add(table1);
                        result.Tables.Add(table2);
                    }
                }
                connection.Close();
            }
            return result;
        }
    }
}

In this approach:

1. We define a query that includes two SELECT statements, one for each table.
2. We use a SqlCommand with parameters to execute the query.
3. We execute the ExecuteReader command and use reader.NextResult() to load data into the second table.
4. We create two DataTable objects to represent our tables and load the data into them.
5. We add the DataTable objects to the DataSet and close the connection.

The above is the detailed content of How to Efficiently Fill a DataSet with Multiple Tables Using a DataReader?. For more information, please follow other related articles on the PHP Chinese website!