Introducing ADO.Net Whidbey

Asynchronous data access 



This is one of the new features of the upcoming ADO.Net version. Remember those times when you used to attempt to connect to the database and the application used to block until the connection got established, or an error was thrown via an exception detailing why the attempt failed. Now that's no longer an issue.

With ADO.Net 2.0, your applications will no longer block. The blocking Open method of the connection instance now has its BeginOpen and EndOpen cousins that shall allow you to connect to a data source asynchronously. 

As of the moment, the next version of the SqlClient class will support this true asynchronous data access support for SQL Server 7, 2000 and Yukon (the next version of SQL Server). And it doesn't end here. Complete API support is available for third-party managed data providers to help implement asynchronous data-access support for their databases, too, via ADO.Net 2.0

Batch updates



As of today's implementation of ADO.Net, whenever developers modify the contents of DataTable and use the DataAdapter to update the changes in the datasource, the 'DataAdapter.Update' call makes round trips to the datasource for each modification that was made. So, if there were 10 updates, then 10 trips will be made to the database server to make those updates.

Coming with ADO.Net 2.0 is the concept of Batch Updates. This is the support in ADO.Net to allow a single database server trip to make multiple updates to the data source. Using the UpdateBatchSize property of the DataAdapter, the developer specifies the size of the batch of updates that need to happen. And, that's it! 

Invoking the Update method of the DataAdapter shall then take the update size in consideration and do the needful. This makes the update process more efficient. And with the ADO.Net 2.0, this is supported for the SqlClient and the OracleClient. 



Not only this, these batch updates can be within a transaction, which is set at the command object associated with the
DataAdapter.

DataSet remoted



One feedback that came in from developers was that the DataSet was too heavy an entity to be remoted, since it was an XML construct. Keeping this in consideration, ADO.Net 2.0 has been tweaked so that it can be remoted using Binary and thus, has better performance when used across the network.

Performance benchmarks show that when greater than 1000 rows, the binary remoting of DataSet is 80 times faster than the corresponding XML version.

Paging eased



One of the requirements of developers who work with databases is that at some point they will need to page the results that have got from an SQL query execution. 

Typically, as of today, this is done by writing a complex stored procedure that will take into account all conditions of paging (such as 'where' clause support) using T-SQL. And then, it needs to be fine-tuned.

Starting with ADO.Net 2.0, all this jugglery need not to be done. ExecutePageReader is the new API for the SqlClient that allows you to page SQL query results without even writing a single line of T-SQL. ExecutePageReader is quite similar to ExecuteReader except that it takes in as arguments, the starting row, and the number of rows from the starting row that need to be returned.

Once it returns, the DataReader returned will allow you to loop through the specified set of rows, with the paging already done for you. Easy, isn't it? And it has been fine-tuned to be fast enough when paging through tables with large number of rows.

MARS



MARS, or Multiple Active Result-sets, is built over Yukon. Essentially, this feature allows the data developers to have more than one pending request per connection. 

As of today, if one attempts to open another DataReader against a connection, an exception is thrown saying that another DataReader is already associated with the connection and needs to be closed for another one to be opened against the same connection.

MARS removes this limitation. Multiple active DataReaders are possible against a single connection, there could be batches between two Read method calls, and per command object, there can be one DataReader. But with MARS, you could have multiple queries against the single connection, without waiting to close the previous query.

Native XML support



ADO.Net 2.0 will have native XML support, keeping in sync with the SQL Server support for the same. Now, a column, variable or a parameter could have its data type as XML. Not only that, there will be validation checks in place, with support for an optional XML schema enforcement, if the developer requires it to be present.

ADO.Net 2.0 also allows for an XML index to be created on XML columns. And these indexes could be created on tags, values and paths.

Also, XQuery, XML Query mechanism, based upon the standards W3C draft is supported to query XML documents and data. And not ending there, support for XML views is also present, that can be customized by the user. Of course, all this requires Yukon.

Bulk copy support



SqlBulkCopyOperation class has been introduced that allows the developer to write applications to bulk load data into SQL Server. Bulk copy is faster than inserting data using T-SQL. 

Using the SqlBulkCopyOperation, its now possible to do the following:

• A single bulk copy operation
• Multiple bulk copy operations
• A bulk copy operation within a dedicated transaction
• A bulk copy operation as part of an existing transaction
• As of the moment, this bulk copy operation is supported by the managed SQL provider.
• Enumerating data providers and SQL server instances

ADO.Net 2.0 also supports some utility functionality support, where in its now possible to enumerate the installed data providers and running SQL Server instances.

The DbProviderFactories.GetFactoryClasses allows the developer to enumerate the installed providers on a given machine and get their details. Likewise, the same can used to enumerate the running SQL Server instances.

There is a lot more that comes with ADO.Net 2.0. Generic data classes, DB* classes to be precise, ObjectSpaces just to name a few. In the coming months, we will take a look at some of these new enhancements and how to code against them.

Kumar Gaurav Khanna