A.3. Disconnected Class Enhancements

ADO.NET 2.0 introduces new features and enhancements for working with disconnected data. These changes affect both retrieving and updating data. The following subsections describe the key changes.

A.3.1. DataSet and DataTable Enhancements

The new DataTableReader class lets you iterate over the rows in a DataTable object in a read-only, forward-only manner much like a regular DataReader. The DataTableReader object returns the rows and columns in the same order as in the underlying DataTable object. The DataTableReader returns only the current version of the row in the DataTable objectrows marked for deletion are skipped over. The data in the underlying DataTable object can be modified or deleted while the DataTableReader object is active, and the DataTableReader object will maintain its position and validity.

The DataTableReader object has an overloaded constructorone takes a DataTable object as an argument and the other takes a DataTable[] object as an argument. The DataTableReader object can also be constructed by calling the CreateDataReader( ) method of the DataTable or DataSet class. For multiple tables, the tables appear in the same order in which they exist in the DataTable array or DataSet object. The NexTResult( ) method of the DataTableReader object advances to the next result set if one exists.

The following console application creates a DataTable object containing all rows in the Person.Contact table in AdventureWorks, creates a DataTableReader object, and writes the first and last name for each person to the console window:

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

class Program
{
    static void Main(string[] args)
    {
        // open a connection
        SqlConnection conn = new SqlConnection(  );
        conn.ConnectionString = "Data Source=localhost;" +
            "Integrated Security=SSPI;Initial Catalog=AdventureWorks";

        // create a DataTable with the Person.Contact data
        SqlCommand selectCommand = conn.CreateCommand(  );
        selectCommand.CommandText = "SELECT * FROM Person.Contact";

        DataTable dt = new DataTable(  );

        SqlDataAdapter da = new SqlDataAdapter(selectCommand);
        da.Fill(dt);

        // create a DataTableReader
        DataTableReader dtr = dt.CreateDataReader(  );

        // iterate over the rows in the DataTableReader and output
        // the first name and last name for each person
        while (dtr.Read(  ))
            Console.WriteLine("{0}\t{1}",
                dtr["FirstName"], dtr["LastName"]);

        Console.WriteLine("Press any key to continue.");
        Console.ReadKey(  );
    }
}

Results are shown in Figure A-13.

Figure A-13. Results for CreateDataReader( ) method example

A.3.2. Batch Processing with the DataAdapter

The DataAdapter class in ADO.NET 2.0 lets you group insert, update, and delete operations on a DataSet object or a DataTable object, instead of sending one row at a time to the server. This reduces round trips and typically results in performance gains. The SQL Server and Oracle providers support batch updates.

The UpdateBatchSize property of the DataAdapter object specifies the number of rows to be sent in each batch. If the UpdateBatchSize property is set to 0, the DataAdapter object uses the largest batch size that the database server can handle. Extremely large batches can negatively affect performancethe size of the batch should be tuned for your environment before deploying an application.

When batching updates, the UpdatedRowSource property of the DataAdapter object UpdateCommand, InsertCommand, and DeleteCommand properties must be set to the value UpdateRowSource.None or UpdateRowSource.OutputParameters. The values UpdateRowSource.FirstReturnedRecord and UpdateRowSource.Both are both invalid.

When updating rows using the DataAdapter object with batch processing disabled, the RowUpdating and RowUpdated events are raised for each row processed. When batch processing is enabled, the RowUpdating event occurs for each row processed, while the RowUpdated event is raised only onceafter the batch is processed. Because the RowUpdated event is raised only once for all rows in the batch, its Row property is null. Instead, you can use the CopyToRows( ) method of the RowUpdatedEventArgs object to copy the processed rows to a DataRow array, where you can access them.