Frequently Used Enhancements in Ado.net 2.0 / New in Ado.net 2.0

In version 2.0 of ado.net there are lot more features to discuss or to list. I am here listing some the changes which we are using frequently while using the ado.net.

1) DataTable

DataTable supports two new methods Load () & Save () from earlier version

Load () – Load DataReader instance directly into DataTable.

Save () – Store Datatable instance to Storage.

Copy () – Copy DataTable into another keeping schema & data intact.

DataTable dtTable2 = dtTable1.Copy ()

– Now we can Read XML & Xml Schema in DataTable Object Using new methods ReadXml, ReadXmlSchema.

– Also if we want to write DataTable to XML then also two New methods are added WriteXml, WriteXmlSchema.

– DataTable is having new method Merge () for merging two tables.

Suppose dtTable1 & dtTable2 are two tables then we can merge table2 With table1

dtTable1.Merge (dtTable2);

– In 2.0 Datatable class Supports Serialization, supports RemotingFormat property.

The available Serialization Format options are Binary and Xml.

DataTable dtTable = new DataTable ();

dtTable.RemotingFormat = SerializationFormat.Binary;

Detail Ref MSDN

2) DataTableReader

In 2.0 it supports CreateDataReader method. The CreateDataReader method creates an instance of the DataTableReader. DataTableReader is created from a DataSet or a DataTable’s CreateDataReader method, is Forward only container for Datatable(s) data.

The DataTableReader contains all of the rows from the container object, other than deleted rows. The DataTableReader is a lighter weight object than the DataTable and, unlike the DataReader (SqlDataReader), the DataTableReader is disconnected. And Looping through DataTableReader is easy using NextResult ()

In short

-Populate a DataTable/DataSet from a DataReader

Load from DataReaders

-OLEleDbDataReader

-SqlDataReader

-DataTableReader

3) Batch Updates

In .Net 1.1 when are using DataAdapter to update database each command was sent to the database one at a time, causing a lot of roundtrips to the database.

ADO.NET 2.0 has introduced the concept of Batch Updates, which allows us to decide number of commands sent to the database at a given time ,this can increase the performance of your data access layer by reducing the number of roundtrips to the database. DataAdapter.UpdateBatchSize Property – The DataAdapter has an UpdateBatchSize Property that allows you to set the number of commands that will be sent to the database with each request.

UpdateBatchSize = 1, disables batch updates

UpdateBatchSize = X where X > 1, sends x statements to the database at a time

UpdateBatchSize = 0, sends the maximum number of statements at a time allowed by the server

Command.UpdatedRowSource Property When using batch mode, the UpdatedRowSource property of the command can only be set to either UpdatedRowSource.None or UpdatedRowSource.OutputParameters

Detail Refernce – David Hyden explains Batch Updates in Detail

4) DataView.ToTable() –

Create a DataTable from a DataView

-avoiding building from iteration or a new database query as such in 1.1

-DistinctRows parameter

5) More Control Over RowState

DataRow.RowState is no longer read only as it was in previous version 1.1.Two new states are added SetAdded & SetModified. We can use these states to set the RowState of an unchanged row.

6)Asynchronous Data Access – In previous version of ADO.NET ExecuteReader, ExecuteScalar and the ExecuteNonQuery methods are only working in Synchronous mode ,used to block the current executing thread by waiting process to continue.Whereas in ADO.NET 2.0 supports asynchronous data access mode,these methods come with Begin and End methods that support asynchronous execution.

Which helps to reduce delays and improved data access efficiently while using web apps. Example.

IAsyncResult result = Command.BeginExecuteReader()

while (! result.IsCompleted)

{

// until command is getting excuted use this time to use some other activity.

}

DataReader reader = Command.EndExecuteReader(result )

Asynchronous command objects are also provided

SqlDataReaders – BeginExecuteReader & EndExecuteReader

XMlDataReaders – BeginExecuteXmlReader & EndExecuteXmlReader

7) Sql Bulk Copy

In the previous version of ADO.NET, copying a large chunks of data from a source table to a destination table in SQL database had performance drawbacks due to the repeated database accesses/Server roundtrips.

The SqlBulkCopy enables you to perform fast bulk inserts using the .NET Data Provider for SQL Server.This class can be used to specify the source and the target data sources for this copy operation.

Using the SqlBulkCopy feature , we can perform:

A single bulk copy operation , Multiple bulk copy operations, A bulk copy operation within a transaction

Lets consider single bulk copy operation for example.

SqlConnection conn1 = new SqlConnection(Settings.ConnectionString);

conn1.Open();

SqlCommand sqlCommand = new SqlCommand(“Select * from Employee”, conn1);

SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

DataTable dt = new DataTable(“TestTable”);

SqlConnection conn2 = new SqlConnection(Settings.ConnectionString1);

conn2.Open();

SqlBulkCopy sqlBulkcopy = new SqlBulkCopy(conn2);

// Assign destination table

bulkcopy.DestinationTableName = “TestTable”;

// Copying data to table

bulkcopy.WriteToServer(sqlDataReader);

8) Provider / Connection Statistics –

In .net 2.0 for sqlconnection object is provieded with “Statistics” data facility By default this feature is disabled, we can enable this by usng StatisticsEnabled property to ttrue after connection is getting created we can capture “Statistics” stored in the form of IDictionary using “RetrieveStatistics()” of the connection object. Statistics include ConnectionTime,ExecutionTime,Prepares,BuffersSent,SelectRows,ServerRoundTrips etc. important data usefull for determining the effenciency of the application.

If RetrieveStatistics is called without StatisticsEnabled having been called first, the values retrieved are the initial values for each entry. If you enable statistics, run your application for a while, and then disable statistics, the values retrieved will reflect the values collected up to the point where statistics were disabled. All statistical values gathered are on a per-connection basis. Detail Ref – MSDN – Provider Statistics

9) Ado.net 2.0 supports new datatypes – varchar(max), nvarchar(max), varbinary(max) in Sql Sever 2005, also ado.net 2.0 gone one step ahead it is now also providing support for User defined data types (UDTs) in SQl Server 2005

You can also find the detailed list of the new changes in ado.net at – New in Ado.net 2.0

Advertisements

One thought on “Frequently Used Enhancements in Ado.net 2.0 / New in Ado.net 2.0”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s