ADO.NET 2.0 - New things

New in ADO.NET 2.0:

1. DataTableReader

A DataTableReader created using DataTable.CreateDataReader will expose the same rows and columns as the DataTable. When a DataTableReader is created from a DataSet or a DataTable's CreateDataReader method, the DataTableReader will contain all of the rows from the container object, with the exception of deleted rows.

The DataTableReader is a lighter weight object than the DataTable and, unlike the DataReader (SqlDataReader), the DataTableReader is disconnected. This is a great feature because you get the lightweight object that you can iterate through quickly (like the DataReader) and it is disconnected from any data source (unlike the DataReader). A DataTableReader can be thought of as an iterator over the underlying table's rows, similar to a foreach enumeration of the contents of the table. However, unlike enumerating a table's rows (which will cause an exception when a row is added or deleted from the collection during the enumeration), a DataTableReader is resilient to changes being made to the underlying table and will correctly position itself in light of any modifications that occur.

http://msdn2.microsoft.com/en-au/magazine/cc163709.aspx

2. DataView.ToTable method

Through the DataView.ToTable method, the DataView now allows you to create a DataTable directly from the DataView. The new DataTable is a separate and distinct object that is not linked to the original DataTable

3. Batch Update improvements - DataAdapter.UpdateBatchSize property

4. SqlConnectionStringBuilder

5. Binary serialization of DataTable or DataSet - RemotingFormat property

http://msdn2.microsoft.com/en-au/magazine/cc163671.aspx

6. SqlBulkCopy

The SqlBulkCopy class provides a high-performance method for copying data to a table in a SQL Server database. The source of the copy is constrained to the overloads of the WriteToServer method, which can accept an array of DataRow objects, an object that implements the IDbDataReader interface, a DataTable object, or a DataTable and DataRowState

7. DbProviderFactory

The DbProviderFactory lets you create a factory object that is responsible for creating the appropriate provider objects. Each provider must supply a subclass of DbProvider-Factory that can be used to create instances of its provider classes. For example, you can use the SqlClientFactory to create instances of any of the SQL Server classes.

8. Data Source enumerating - No more SqlDMO!
The following code snippet shows how to enumerate the data sources:

DbProviderFactory factory =
DbProviderFactories.GetFactory("System.Data.SqlClient");
//get SQL Server instances
DataTable sources =
factory.CreateDataSourceEnumerator().GetDataSources();
GridView gv = GetGridView(275, 20);
gv.DataSource = sources;
gv.DataBind();

Further more - http://blog.vuscode.com/malovicn/archive/2007/11/12/how-to-build-your-own-sql-server-explorer.aspx

9. DBException - provider-neutral exception

All provider specific exceptions inherit from a common base class called DbException. When working with a provider-neutral coding model, your try/catch block can simply catch DbException generically instead of trying to catch each provider-specific exception.

10. InfoMessage event on connection classes

The connection classes contain an event called InfoMessage that can be used to retrieve general and error information from the database. You can use the InfoMessage event to view the results of SQL Print statements and any messages that are available as a result of the SQL RaiseError statement,
regardless of the error level.

11. Asynchronous Data Access!!!

ADO.NET/SqlClient asynchronous command execution support is based on true asynchronous network I/O under the covers (or non-blocking signaling in the case of shared memory). If we have enough demand, perhaps I'll write about the internal implementation some day. For now, let me state that we do "true async," there are no blocked background threads waiting for a particular I/O operation to finish, and we use the overlapped I/O and the input/output completion ports facilities of the Windows 2000/XP/2003 operating systems to make it possible to use a single thread (or a few of them) to handle all the outstanding requests for a given process.

http://msdn2.microsoft.com/en-us/library/ms379553.aspx