What Is ADO.NET?

ADO.NET is a collection of .NET classes that you can use to access data in different kinds of data store.

ADO.NET uses data source-specific providers to abstract the underlying database and provides a wide range of data access.

ADO.NET Enhancements in the .NET Framework 2.0

ADO.NET 2.0 includes enhancements for SQL Server client application developers. Understanding these enhancements will help you design and implement effective client applications on the .NET Framework 2.0 platform.

Asynchronous data access

ADO.NET 2.0 includes support for asynchronous data access operations, making it possible to build highly scalable client applications.

You can open connections and execute commands asynchronously, using a programming model similar to that used in the .NET Framework for file or network operations.
The SqlConnection class provides the following methods for asynchronous operations:
  • BeginOpen.
  • EndOpen.

The SqlCommand class provides the following methods for asynchronous operations:

  • BeginExecuteNonQuery.
  • BeginExecuteReader.
  • BeginExecuteXmlReader.
  • EndExecuteNonQuery.
  • EndExecuteReader.
  • EndExecuteXmlReader.
You can specify a delegate function as a callback when calling a Begin method so that your program is notified when the operation is complete, and then you can call the matching End method in the callback function.

Alternatively, you can call the Begin method, perform any other client-side tasks that are needed, and then call the End method to block your application until the data access operation completes.

You have to enable your database to support asynchronous processing, by setting "Asynchronous Processing" parameter in your connection string to true.

Example:

string connectionString = "Data Source=(local);Integrated Security=SSPI;Initial Catalog=AdventureWorks; Asynchronous Processing=true";
SqlConnection connection = new SqlConnection(connectionString);
private void RunAsyncCommand()
{

string commandText = "WAITFOR DELAY '0:0:05';" +
"UPDATE Production.Product SET ReorderPoint = ReorderPoint + 1 " +
"WHERE ReorderPoint Is Not Null;" +
"UPDATE Production.Product SET ReorderPoint = ReorderPoint - 1 " +
"WHERE ReorderPoint Is Not Null";

SqlCommand command = new SqlCommand(commandText, connection);
connection.Open();
AsyncCallback callback = new AsyncCallback(HandleCallback);
command.BeginExecuteNonQuery(callback, command);

}
private void HandleCallback(IAsyncResult result)
{

SqlCommand command = (SqlCommand)result.AsyncState;
command.EndExecuteNonQuery(result);
connection.Close();

}

Multiple Active Results Sets (MARS)

SQL Server 2005 supports multiple active result sets (MARS). In previous releases of ADO.NET, you had to close a SqlDataReader before you could reuse its connection for another operation. MARS allows you to use a connection, even if it is being used by an open SqlDataReader object. To enable or disable MARS SqlConnection objects, you need to set MultipleActiveResultSets value to true or false in the connection string. MARS feature is disabled by default.

Example:

private void ReadData()
{

int vendorID;
SqlDataReader productReader = null;
string vendorSQL = "SELECT VendorId, Name FROM Purchasing.Vendor";
string productSQL = "SELECT Production.Product.Name FROM Production.Product " +
"INNER JOIN Purchasing.ProductVendor " + "ON Production.Product.ProductID = " + "Purchasing.ProductVendor.ProductID " + "WHERE Purchasing.ProductVendor.VendorID = @VendorId";
string connectionString = "Data Source=MSSQL1;" +
"Initial Catalog=AdventureWorks;Integrated Security=SSPI" +
"MultipleActiveResultSets=True";
using (SqlConnection awConnection = new SqlConnection(connectionString))
{

SqlCommand vendorCmd = new SqlCommand(vendorSQL, awConnection);
SqlCommand productCmd = new SqlCommand(productSQL, awConnection);
productCmd.Parameters.Add("@VendorId", SqlDbType.Int);
awConnection.Open();
using (SqlDataReader vendorReader = vendorCmd.ExecuteReader())
{

while (vendorReader.Read())
{

Console.WriteLine(vendorReader["Name"]); vendorID = (int)vendorReader["VendorId"]; productCmd.Parameters["@VendorId"].Value = vendorID;
// The following line of code requires // a MARS-enabled connection.
productReader = productCmd.ExecuteReader();
using (productReader)
{

while (productReader.Read())
{

Console.WriteLine(" " + productReader["Name"].ToString();

}

}

}

}
Console.WriteLine("Press any key to continue");
Console.ReadLine();

}

}

For more information about MARS visit MSDN

Batch Updates

In previous releases of ADO.NET, calling the Update method on a SqlDataAdapter resulted in an update operation for each modified row in the DataSet being updated. In ADO.NET 2.0, you can set the UpdateBatchSize property to perform multiple updates in a single step. This can improve the efficiency of an application in many cases. The default value for UpdateBatchSize is 1, making the default behavior consistent with previous releases of ADO.NET.

This is all for this part. Next part we will talk about more enhancements in ADO.Net 2.0 for SQL Server Data Provider.