Filling a DataSet with Multiple Tables Using SqlDataReader

When working with a DataSet that contains multiple tables with relationships, it's crucial to consider how to efficiently fill the DataSet while maintaining data integrity. While using a DataReader provides a lightweight approach, it may pose challenges when filling multiple tables. Here's an improved solution that leverages DataReader's capabilities while addressing the one-to-many relationship:

Leveraging Table Mappings for Multiple Select Queries

To fill a DataSet with multiple tables using DataReader, one can send multiple select statements to the database server in a single request. This optimized approach allows the server to process the queries efficiently and eliminates the need for separate executions. However, by default, the tables generated from the queries will have automatic names (Table, Table1, etc.).

To map these generated table names to specific tables in the DataSet, you can utilize the TableMappings property of the SqlDataAdapter. This property enables the association of table names used in the query with the corresponding tables in the DataSet. Here's an example:

SqlDataAdapter adapter = new SqlDataAdapter(
    "SELECT * FROM Customers; SELECT * FROM Orders", connection);
adapter.TableMappings.Add("Table", "Customer");
adapter.TableMappings.Add("Table1", "Order");

adapter.Fill(ds);

By mapping the generated table names to "Customer" and "Order," the data from the queries will be loaded into the correspondingly named tables in the DataSet. This approach ensures that the appropriate data flows into the correct tables while maintaining the established relationships.