ADO.NET Cookbook

9.9. Improving Performance While Filling a DataSet

Problem

Given a DataSet containing many related tables that takes a long time to fill, you need to improve the performance.

Solution

Use the EnforceConstraints property of the DataSet and the BeginLoadData( ) and EndLoadData( ) methods of the contained DataTable objects to improve performance while filling a complex DataSet.

The sample code contains one event handler and one method:

Go Button.Click

Times the filling of the DataSet created by the CreateDataSet( ) method (described next). The EnforceConstraints property of the DataSet is set as specified and the BeginLoadData( ) and EndLoadData( ) methods of the contained DataTable objects are used, if specified. A DataAdapter is used to fill a specified DataSet with data from the Orders and Order Details tables in the Northwind database. Ten iterations are performed, and the total fill time is returned in ticks, which are 100-nanosecond intervals.

CreateDataSet( )

This method builds a DataSet containing the table schema for the Orders and Order Details tables from the Northwind database and creates a data relation between the tables. The DataSet is returned by the method.

The C# code is shown in Example 9-12.

Example 9-12. File: DataSetFillPerformanceForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; // Table name constants public const String ORDERS_TABLE = "Orders"; public ...