The .NET Platform
Development Tools
COM & COM+
Data Access
Web Development
XML Technologies
Windows Servers
Wireless & Mobile
Security issues
Design & Process
Career Development
Analysis & Comment
Disposable Objects
We found this book useful!
You are not logged in: login here to access all areas.
What's new in ADO.NET 2.0
For .NET Framework 2.0, Microsoft has added a host of new features to facilitate the access of relational data. Alex Homer looks at the enhancements in ADO.NET 2.0
Author: Alex Homer
Last updated: May 2005
In Version 2.0, the primary technology for relational data access, namely ADO.NET, gains a raft of new features as well as improved performance and better usability in many existing classes. There are too many new features to examine in depth, but the following sections overview the major changes and enhancements. SqlClient Data Provider Enhancements Implementing asynchronous execution is not a trivial task, and there are several ways that you can approach it. One way, used in the example shown above, is to start the three commands executing and then use the Windows WaitHandle class to cause the thread to sleep until the commands complete. Each connection and command is created in the usual way, and then executed using the new BeginExecuteReader method: SqlConnection con1 = new SqlConnection("connection-string"); Then an array of WaitHandle instances is created, using the IAsyncResult instances returned from each BeginExecuteReader method call: WaitHandle[] handles = new WaitHandle[3]; Now the code can call the Static WaitAny method of the WaitHandle class to cause the thread to sleep until any one of the commands completes. However, to ensure that the code reacts to all three commands completing, this must be done within a for loop so that the WaitAny method is called again for each command in the WaitHandle array: for (int i = 0; i < handles.Length; i++) As each command completes, a switch statement uses the index value within the WaitHandle array (this index is returned from the WaitAny method) to call the EndExecuteReader method on the appropriate command. This returns a DataReader that can be used to populate a table in a DataSet using the new Load method (described later). The table is then bound to the DataGridView control in the page, and the Refresh method is called on it to display the rows: switch (index) Promotable Transactions and the System.Transactions Classes Provider-independent Data Access Enhancements to the DataSet and DataTable Other New Features in ADO.NET 2.0
While it's always been possible to write code that executes asynchronously in .NET, version 2.0 of ADO.NET makes it much easier to perform asynchronous data access using the SqlConnection class. This class gains a series of Begin and End methods that allow you to start a command executing against the data store, and then execute other code, or just wait until a response is received from the data store. You can execute multiple commands asynchronously, and use a range of techniques to cause the thread to sleep while these commands execute. Alternatively, you can arrange for a callback to be executed when each command completes.
Asynchronous command execution can dramatically reduce delays when you need to access more than one data source, improving responsiveness in Windows Forms applications and giving faster load times for Web pages. As an example, you can handle each command to fill list controls or display data as it completes. The simple application shown below fetches three rowsets and displays them. When run using synchronous execution, it takes 15 seconds to populate all three lists. When executing the commands asynchronously, each rowset is displayed as soon as the data is returned and the whole process is completed in the time it takes for the slowest response to be received, which is only 8 seconds.
SqlCommand cmd1 = new SqlCommand("query-statement", con1);
con1.Open();
IAsyncResult ar1 =
cmd1.BeginExecuteReader(CommandBehavior.CloseConnection);
... and repeat for other two commands ...
handles[0] = ar1.AsyncWaitHandle;
handles[1] = ar2.AsyncWaitHandle;
handles[2] = ar3.AsyncWaitHandle;
{
// start wait for any of the three Commands to complete
// allow 20 seconds for them all to complete
int index = WaitHandle.WaitAny(handles, 20000, true);
{
// see which of the Commands completed, and get DataReader
case 0:
reader = cmd1.EndExecuteReader(ar1);
DataSet ds = new DataSet();
ds.Load(reader, LoadOption.OverwriteRow, new String[] {
"table1" });
datagridview1.DataSource = ds.Tables[0];
datagridview1.Refresh();
break;
... same for other two index values ...
}
Another useful feature of the SqlClient namespace in version 2.0 which can significantly improve performance is ‘promotable transactions’. This allows you to write code that uses only a local ADO.NET connection-based transaction, but will automatically create a new distributed transaction, through Windows Component Services, if you then open and do work on another connection within the same scope.
This functionality is enabled through new classes in the System.Transactions namespace. These classes make it easy to manage distributed transactions more easily, and can be used not only with ADO.NET connections, but also with other services and applications that have the appropriate distributed transaction co-coordinator (DTC) available. An example is the Message Queue Service in Windows, which you can access from managed code using classes in the System.Messaging namespace. Effectively, the classes in the System.Transactions namespace provide a managed code layer over the Windows COM-based distributed transaction management system, and allow you to configure transactions and develop transacted applications more easily.
The two main classes in the System.Transactions namespace are TransactionScope and Transaction. The TransactionScope class is used to define the block of code that is transactional. You just have to create an instance of the class at the start of your code block, and then dispose of it afterwards. (As it supports the IDispose interface, you can take advantage of the Using statement to ensure that it is correctly disposed.) The Complete method can be called at any time by your code to indicate that the transaction should be considered to be successful, and all the updates committed. You can also enlist existing transactions into a distributed transaction.
The Transaction class is a Static class that you can use to create new transactions. It also exposes a Current property that allows you to access the current transaction.
When faced with writing data access code that must target different types of database, developers have found ways to get round the provider-dependent nature of ADO.NET, where there are separate namespace classes for each data access type, such as SqlClient, OleDb and Odbc, by using classes from the System.Runtime.Remoting namespace. This is no longer necessary in version 2.0. Instead, Microsoft has added a data factory capability to the System.Data namespace classes. You can create a DataFactory instance and then use it to generate instances of the specific base classes you require to perform data access.
For example, you can write your applications so that they can be configured during installation via a configuration file, or at runtime to use the provider specific classes suitable for the database that the application has to work with. This feature is ideal for tool or IDE developers, where they need to support multiple databases. However, it is not designed to be a general solution, as there is a minor performance hit involved in generating the data factory.
Provider-independent code also brings with it the problem of creating the correct connection string and adhering to the appropriate parameter naming convention. To assist there is a new class in version 2.0 called ConnectionStringBuilder that makes it easy to generate a connection string at runtime by adding the required name/value pairs for that connection string.
Probably the most useful class for general data manipulation and persistence in ADO.NET is the DataSet. There are performance issues with very large DataSet instances in version 1.x which have been addressed in version 2.0. New internal indexing features reduce the time taken to access, insert and delete rows by a significant margin once you have more than a few thousand rows in a DataSet. There is also an option in version 2.0 to persist the contents of a DataSet in binary rather than XML format, which can reduce network round-trip times for large DataSet instances by more than 70 percent.
The DataSet also gains the ability to perform updates to the data store in batches in version 2.0, which can improve performance by reducing network round-trips to the database. Plus, to make it easier to manage the way that existing rows in a DataSet are merged with incoming rows during a Fill method call, a new property on the DataAdapter allows you to specify whether the current and/or original values in each matching row are replaced using a value from the new LoadOption enumeration. If you want to allow users to reconcile update failures when concurrent updates cause errors, this feature removes the need to save copies of the DataSet and merge them together afterwards.
Microsoft has also attempted to make it easier to use single rowsets in ADO.NET version 2.0 by surfacing the DataTable class as a full-featured data access object. As well as using the Fill and Update methods to populate and push changes back from an individual DataTable, you can now use other methods such as ReadXml, ReadXmlSchema, WriteXml, WriteXmlSchema, Clear, Clone, Copy, Merge and GetChanges, which were previously only available on the DataSet class.
The DataSet and DataTable gain a new method named Load that takes a DataReader instance, such as SqlDataReader or OleDbDataReader, and streams the data into a table (or in the case of the DataSet, into one or more tables). This method uses the same LoadOption enumeration values as the Fill method mentioned earlier.
Finally, there is a new class added to ADO.NET 2.0 called DataTableReader. As the name suggests, this class implements a ‘reader’ that works just like SqlDataReader and OleDbDataReader but takes as its data source a DataTable instance so that you can stream rows out of a DataTable. It implements the same IDataReader interface as the other readers which means you can use it as the source for the Load method and so stream data from one DataSet to another, or from one DataTable to another. For example, the following shows how you can copy tables from one DataSet (where they are named Table, Table1 and Table2), into a new DataSet, and give them new names:
ADO.NET 2.0 adds several more features to the developer's armory. A new class called SqlBulkCopy allows you to build applications that perform bulk loading of an SQL Server database from a DataReader, a DataTable or an array of data rows. You can specify the associations between the source and target columns to map data across disparate table schemas, and handle events to monitor the process.
There is also a new method named GetSchema on the Connection class in version 2.0 that allows you to obtain full details of the data store to which you are connecting. This includes lists of tables, columns, data types, users and much more. For developers building tools and IDE-based applications, this makes it much easier to display useful information to the user, and to confirm that the database in use exposes the objects and methods you require in your code.
The Connection class has also been extended in version 2.0 to support the retrieval of connection statistics during the execution of commands, which can assist in debugging applications. Along the same lines, the classes in ADO.NET expose enhanced tracing capabilities and no longer generate the useless generic error messages that are seen in version 1.x. Instead, much more succinct error details are returned.
Further enhancements specific to SQL Server 2005 are covered in a separate article.
Find out more
Check out Alex Homer’s book ADO.NET & System XML v2.0 - The Beta Version, which is available from Computer Manuals and other suppliers, for more information.
For more on asynchronous processing, see http://msdn.microsoft.com/library/en-us/dnvs05/html/async2.asp.
For a more detailed description of distributed transactions, see http://msdn.microsoft.com/library/en-us/cossdk/htm/pgdtc_dev_3lrn.asp. You will also find details of the Promotable Transactions feature at http://msdn.microsoft.com/msdnmag/issues/05/02/DataPoints/default.aspx.
More details of provider-independent data access code can be found at http://msdn.microsoft.com/library/en-us/dnvs05/html/vsgenerics.asp.
More on the updates to the DataSet can be found at http://msdn.microsoft.com/library/en-us/dnadonet/html/datasetenhance.asp.
For more on the schema API in ADO.NET 2.0, see http://msdn.microsoft.com/library/en-us/dnvs05/html/adonet2schemas.asp.
More on the tracing features in ADO.NET 2.0, and how to use them, can be found at http://msdn.microsoft.com/library/en-us/dnadonet/html/tracingdataaccess.asp.
Click here for our Privacy Statement. Copyright © Matt Publishing. All rights reserved. No part of this site may be reproduced without the prior consent of the copyright holder.
Introduction to .NET Framework 2.0 Working with XML data in .NET Framework 2.0