Overview of ADO.NET 2.0 New Features

If you think about ADO.NET 1.x DataSet, the major problem which comes to our mind is DataSet Serialization. Microsoft has worked lots on this part and they have improved the performance of Serialization a lot. In ADO.NET 1.x, Serialization of DataSet will happen in XML format. Even in ADO.NET 2.0, by default it happens in XML fomat. But there is an option to change the Serialization format to Binary using property called "SerializationFormat". Look at the following code.

      Dim format As New Binary.BinaryFormatter
        Dim ds As New DataSet
        ds = DataGridView1.DataSource
        Using fs As New FileStream("c:\sar1.bin", FileMode.CreateNew)
            ds.RemotingFormat = SerializationFormat.Binary

            'Other option is SerilaizationFormat.XML
            format.Serialize(fs, ds)
        End Using

In this code snippet, we are serializing the dataset into filestream. If you look at the file size difference between XML and Binary formating, XML formating is more than three times bigger than Binary formating. If you see the perfomance of Remoting of DataSet when greater than 1000 rows, the binary formating is 80 times faster than XML formating.

DataSet and DataReader Transfer

In ADO.NET 2.0, you can load DataReader directly into DataSet or DataTable. Similarly you can get DataReader back from DataSet or DataTable. DataTable is now having most of the methods of DataSet. For example, WriteXML or ReadXML methods are now available in DataTable also. A new method "Load" is available in DataSet and DataTable, using which you can load DataReader into DataSet/DataTable. In other way, DataSet and DataTable is having method named "getDataReader" which will return DataReader back from DataTable/DataSet. Even you can transfer between DataTable and DataView. Check out the following example,

            Dim dr As SqlDataReader
            Dim conn As New SqlConnection(Conn_str)
            conn.Open()
            Dim sqlc As New SqlCommand("Select * from Orders", conn)
            dr = sqlc.ExecuteReader(CommandBehavior.CloseConnection)
            Dim dt As New DataTable("Orders")
            dt.Load(dr)

Data Paging

Custom paging is one of the major requirements in ASP.NET. Similarly if you take windows application also, paging is an important feature that is required. In previous releases, we need to write stored procedure for doing paging in our applications. But in ADO.NET, you can do is very simply. An new API "ExecutePageReader" in SQLCommand will do all the stuff for you and return only the required records. This method is very similar to ExecuteReader but it will accept two extra parameter. One is "Starting row number" and other one is for "number of rows". This will also return datareader. For example, check out the following code snippet.

            Dim dr As SqlDataReader
            Dim conn As New SqlConnection(Conn_str)
            conn.Open()
            Dim sqlc As New SqlCommand("Select * from Orders", conn)
            dr = sqlc.ExecutePageReader(CommandBehavior.CloseConnection, 10, 10)

Asynchronous Data Access

In ADO.NET 1.x commands like ExecuteReader,ExecuteScalar and ExecuteNonQuery will synchronously execute and block the current thread. Even when you open connection to the database, current thread is blocked. But in ADO.NET 2.0, all of these methods comes with Begin and End methods to support asynchronous execution. This asynchrounous methodology is very similar to our .NET framework asynchronous methodology. Even you can have callback mechanism using this approach. This Asynchrounous Data Access is currently only supported in SQLClient, but complete API support is available for other providers to implement this mechanism.

Batch Updates

In previous versions of ADO.NET, if you do changes to DataSet and update using DataAdapter.update method. It makes round trips to datasource for each modified rows in DataSet. This fine with few records, but if there is more than 100 records in modified. Then it will make 100 calls from DataAccess layer to DataBase which is not acceptable. In this release, MicroSoft have changed this behaiour by exposing one property called "UpdateBatchSize". Using this we can metion how we want to groups the rows in dataset for single hit to database. For example if you want to group 50 records per hit, then you need to mention "UpdateBatchSize" as 50.

Common Provider Model

In our application if want to implement provider independent DataAccess, then we need to write our own factory classes for returning the required objects like connection, command. And for implementing this feature only provider independent interface were available in the previous releases. But in ADO.NET 2.0, you have separate factory classes for managing common provider model. A new class "DbProviderFactory" is included in this release which has two methods. One method "GetFactoryClasses" to get all the provider installed in that machine and other one "GetFactory" will be used to get provider specific object by providing provider name as paramter.

Check out the following example, in which without knowing which provider we are going to work on we are fetching values from database. We need to pass only "Providername" which can configurable and which can change.

            Dim pf As DbProviderFactory
            pf = DbProviderFactories.GetFactory(providername)
            Using dbc As DbConnection = pf.CreateConnection
                dbc.ConnectionString = Conn_str
                dbc.Open()
                Dim comm As DbCommand = dbc.CreateCommand
                comm.CommandText = "Select * from orders"
                Dim dr As DbDataReader = comm.ExecuteReader(CommandBehavior.CloseConnection)
                Dim ldt As New DataTable("Orders")
                ldt.Load(dr)
            End Using

Bulk Copy

If you think of bulk copy i.e. if you want to move some data from one datasource to another datasource. If will simply think of doing this in database, since we dont have much options in the previous release. But in ADO.NET 2.0, we can do this from DataAccess Layer itself. New class called "SQLBulkCopy" is included in this release which will do this work for us. Using this class we can metion which datasource you want to copy and to which destination table you want to copy. You can even map the columns between tables, by default it will copy columns to columsn. Check out the following example,

            Dim dr As SqlDataReader
            Dim conn As New SqlConnection(Conn_str)
            Dim conn1 As New SqlConnection(Conn_str1)
            conn.Open()
            conn1.Open()
            Dim sqlc As New SqlCommand("Select * from Orders", conn)
            'dr = sqlc.ExecutePageReader(CommandBehavior.CloseConnection, 10, 10)

            dr = sqlc.ExecuteReader(CommandBehavior.CloseConnection)
            Dim dt As New DataTable("Orders")
            Dim bulkcopy As New SqlBulkCopy(conn1)
            bulkcopy.DestinationTableName = "MVPOrders"
            bulkcopy.WriteToServer(dr)

Multiple Active ResultSets

Using this feature we can have more than one simultaneous pending request per connection i.e. multiple active datareader is possible. Previously when a DataReader is open and if we use that connection in another datareader, we used to get the following error "Systerm.InvalidOperationException: There is already an open DataReader associated with this connection which must be closed first". This error wont come now, as this is possible now because of MAR's. This feature is supported only in Yukon.

Conclusion

Here i have mentioned only few new features. I will cover about other features like ObjectSpaces, Native XML Support and XML Support for Yukon, Serverside Cursors in my next article.

Overview of ADO.NET 2.0(Whidbey) New Features

Improved Performance for DataSet Remoting