ADO.NET Enhancements

In addition to the new CLR and T-SQL features, the SQL Server 2005 release also provides some extensive improvements on the client side by bundling an update to ADO.NET. As ADO.NET continues to mature, it’s finally getting those missing features that were present in its predecessor, the COM-based ADO, along with a couple of brand-new features. As you’ll see as I describe the new features that Microsoft has added to ADO.NET, many of them expose some of the new capabilities that have been added to the SQL Server database engine. In addition to the major new features listed in the sections that follow, as you might expect, the new ADO.NET also supports new T-SQL varchar(max) and XML data types.

Server Cursor Support Using the SqlResultSet

One of the most important new features provided in the new ADO.NET is support for server-side cursors. This is one of the areas where ADO.NET was missing features found in COM-based ADO. Prior versions of ADO.NET supported only client-side cursors, such that the client platform had to do the work of maintaining the result set. With server-side cursors, you can shift that work to the server. Microsoft added this new feature chiefly to support the new in-process SQL Server .NET Data Provider, which runs on the server. Microsoft added this feature for in-process server-side requirements because on the server, there’s a need to dynamically scroll through the results of short-lived result sets and there’s little user interaction. Server-side cursors do hold state, which decreases scalability and increases the need for round-trips to the server. However, because of scalability issues Microsoft added this to only the server-side System.Data.SqlServer namespace. Server-side cursor support is not part of the client-side System.Data.SqlClient namespace.

With ADO.NET 2.0, the new SqlResultSet object exposes server-side cursors to your application. Theses cursors are both updatable and dynamically scrollable. The new server-side cursors are instantiated by the new ExecuteResultSet method in the System.Data.SqlServer SqlCommand object. The following example demonstrates the use of the ADO.NET SqlResultSet object:

using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; public partial class StoredProcedures {     [SqlProcedure]     public static void GetProductName()     {         SqlPipe myPipe = SqlContext.GetPipe();         myPipe.Send("GetProductName: Opening server cursor");         SqlCommand cmd = SqlContext.GetCommand();         cmd.CommandType = CommandType.Text;         cmd.CommandText = "SELECT Name FROM Production.Product WHERE MakeFlag = 1";         SqlResultSet resultset = cmd.ExecuteResultSet             (ResultSetOptions.Scrollable |             ResultSetOptions.Updatable);         if (resultset.HasRows == true)         {             while (resultset.Read())             {                 myPipe.Send(resultset.GetString(0));                 // You could optionally update with                 //resultset.Update();                 // or scroll back using                 //resultset.ReadLast();             }             resultset.Close();         }         myPipe.Send("GetProductName: Server cursor closed");     } };

Here, you can see the new CLR stored procedure called GetProductName. First, the SqlPipe object is used to send a progress message to the client. Then, the new SqlCommand object is instantiated that will retrieve the contents of the Name column on the Production.Product table of the sample AdventureWorks database. Next, an instance of the SqlResultSet object is created. Much like SqlDataReader, the SqlResultSet object is instantiated using the SqlCommand object. In this case, a scrollable, updatable cursor is opened using the ExecuteResultSet method. After the cursor is opened, your application can scroll forward, scroll backward, and make updates. Since server-side cursors hold state and consume server resources for as long as they’re open, it’s very important to be sure to close them when they’re no longer needed.

Asynchronous Support

Another feature that was present in ADO that was missing in the earlier releases of ADO.NET is support for asynchronous queries. Asynchronous queries provide client applications the ability to submit queries without blocking the user interference. In the middle tier of applications, the new ADO.NET asynchronous support provides the ability for server applications to issue multiple database requests on different threads without blocking the threads. This new asynchronous support will also work with prior versions of SQL Server, including SQL Server 7 and 2000. With SQL Server 2005, ADO.NET provides asynchronous support for both opening a connection and executing commands. The implementation is the same as other asynchronous operations found in the .NET Framework. The asynchronous operation is started using the object’s BEGINxxx method and is ended using the ENDxxx method. The IAsyncResult object is used to check the completion status of the command.

SqlConnection cn = new SqlConnection  ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"    + "DATABASE=AdventureWorks;async=True"); SqlCommand cmd = new SqlCommand("SELECT * FROM Production.Product", cn); cmd.CommandType = CommandType.Text; try {     cn.Open();     IAsyncResult myResult = cmd.BeginExecuteReader();     while (!myResult.IsCompleted)     {         // Perform other code actions     }     // Process the contents of the reader     SqlDataReader rdr = cmd.EndExecuteReader(myResult);     // Close the reader     rdr.Close(); } catch (Exception ex) {     MessageBox.Show(ex.Message); } finally {     cn.Close(); }

ADO.NET 2.0’s asynchronous support is implemented in the client namespaces such as the System.Data.SqlClient namespace. The first important point to notice in this example is the connection string. In order to implement asynchronous support, the connection string must contain the ASYNC=true keywords. Next, note the IAsynchResult object within the Try block. The SqlCommand object’s BeginExecuteReader method is used to start an asynchronous query that returns all of the rows in the Production.Product table. Control is returned to the application immediately after the statement is executed. The application doesn’t need to wait for the query to finish. Next, a While loop is used to check the status of the IAsyncResult object. When the asynchronous command completes, the IsCompleted property is set to true. At this point, the While loop completes and the EndExecuteReader command is used to assign the asynchronous query to a SqlDataReader for processing.

Multiple Active Result Sets (MARS)

The ability to take advantage of SQL Server 2005’s new multiple active result sets (MARS) feature is another enhancement found in the new ADO.NET version. In prior versions of ADO.NET and SQL Server, you were limited to one active result set per connection. And while COM-based ADO and OLE DB had a feature that allowed the application to process multiple result sets, under the covers that feature was actually spawning new connections on your behalf in order to process the additional commands. The new MARS feature in ADO.NET takes advantage of SQL Server 2005’s capability to have multiple active commands on a single connection. In this model, you can open a connection to the database, then open the first command and process some results, then open the second command and process results, and then go back to the first command and process more results. You can freely switch back and forth between the different active commands. There’s no blocking between the commands, and both commands share a single connection to the database. The feature provides a big performance and scalability gain for ADO.NET 2.0 applications. Since this feature relies on a SQL Server 2005 database, it can be used only with SQL Server 2005 databases and doesn’t work with prior versions of SQL Server. The following example illustrates using MARS:

SqlConnection cn = new SqlConnection   ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"     + "DATABASE=AdventureWorks"); SqlCommand cmd1 =   new SqlCommand("SELECT * FROM HumanResources.Department", cn); cmd1.CommandType = CommandType.Text; try {     cn.Open();     SqlDataReader rdr = cmd1.ExecuteReader();     while (rdr.Read())     {         if (rdr["Name"].ToString() == "Production")         {             SqlCommand cmd2 = new SqlCommand               ("SELECT * FROM HumanResources.Employee "                 + "WHERE DepartmentID = 7", cn);             cmd2.CommandType = CommandType.Text;             SqlDataReader rdr2 = cmd2.ExecuteReader();             while (rdr2.Read())             {                 // Process results             }             rdr2.Close();         }     }     rdr.Close(); } catch (Exception ex) {     MessageBox.Show(ex.Message); } finally {     cn.Close(); }

In this example, you can see that both cmd1 and cmd2 share the same SqlConnection object, named cn. The cmd1 object is used to open a SqlDataReader that reads all of the rows from the HumanResources.Department table. When the Department named Production is found, the second SqlCommand object, named cmd2, is used to read the contents of the HumanResources.Employee table. The important point to note is that the SqlCommand named cmd2 is able to execute using the active SqlConnection object that is also servicing the cmd1 object.

Paging

Integrated support for paging is another welcome new feature found in the SQL Server 2005 version of ADO.NET. Paging is always a difficult area to work with in client applications, and the new ADO.NET provides the basic support for paging by enabling the application to select and bind to a range of rows from a result set. For scalability, the new paging implementation doesn’t hold any state on the server. However, this also means that it’s possible for the membership of the paging set to change between executions. This means that the new paging feature is best suited to data that’s fairly stable and doesn’t change frequently. The paging support in the new ADO.NET is ordinal based, and to use it, you must specify a starting row in the result set and the number of rows to include in the page. The rows of the page set are read using the standard DataReader. The following example PageProductsTable subroutine illustrates using the new ADO.NET paging function:

private SqlDataReader PageProductsTable(int nStartRow, int nPageSize) {      SqlConnection cn = new SqlConnection        ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"          + "DATABASE=AdventureWorks");      SqlCommand cmd =        new SqlCommand("SELECT * FROM Production.Product", cn);      cmd.CommandType = CommandType.Text;      cn.Open();      return cmd.ExecutePageReader        (CommandBehavior.Default, nStartRow, nPageSize); }

In this example, the PageProductsTable method takes as arguments two integers that define the starting position and the paging size of the rows to be read. It returns an ADO.NET SqlDataReader object. Inside the routine, the SqlConnection and SqlCommand objects are created as normal. After the SqlConnection object is opened, the SqlCommand object’s ExecutePageReader method is called to retrieve a page of results. The first argument of the ExecutePageReader method is the CommandBehaviorDefault enumerator that tells the SqlCommand object how to handle the connection when the operation is finished. The second argument is an ordinal that identifies the starting row. The third argument specifies the number of rows to be returned. You can use ADO.NET 2.0’s paging capabilities, as you can see in the following example:

DataTable dt = new DataTable("Products"); dt.Load(PageProductsTable(10, 10)); dataGridView1.DataSource = dt;

Here, a new DataTable object is created, and then the Load method is used to pipe the results of the paged SqlDataReader into the DataTable. The DataTable is then bound to a dataGridView object.

Bulk Insert

Another significant enhancement in ADO.NET 2.0 is the new SqlBulkCopy object. The SqlBulkCopy object provides a high-performance method for transferring objects between different databases or different SQL Server systems. The following example illustrates how the new SqlBulkCopy object is used:

// Create source & destination connections SqlConnection cnSource = new SqlConnection   ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"     + "DATABASE=AdventureWorks"); SqlConnection cnDest = new SqlConnection   ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"     + "DATABASE=AdventureWorks2"); cnSource.Open(); cnDest.Open(); // Read the source data SqlCommand cmd = new SqlCommand   ("SELECT * FROM Sales.SpecialOffer", cnSource); SqlDataReader rdr = cmd.ExecuteReader(); // Create SqlBulkCopy object and write the destination data SqlBulkCopy bulkData = new SqlBulkCopy(cnDest); bulkData.DestinationTableName = "SpecialOffers"; bulkData.WriteToServer(rdr); bulkData.Close(); cnSource.Close(); cnDest.Close();

In this example, two connection objects are created that point to different databases in the same system. The first connection object uses the AdventureWorks database, and the second object uses a copy called AdventureWorks2. Both connection objects are opened, and then a SqlDataReader is used to read the data from the source connection. Next, a SqlBulkCopy object is created and attached to the destination connection object. Then, the SqlBulkCopy object’s WriteToServer method is called using the SqlDataReader that’s attached to the source connection. The WriteToServer method copies the data from the source to the destination. It’s important to note that the target object must exist on the destination connection. There are additional methods in the SqlBulkCopy object that you can use to perform custom schema mapping between the source and the destination tables.

Common Connection Model

One of the problems that ADO.NET 1.0 also had was the fact that it was not provider agnostic. In other words, you needed to use a specific provider to connect to a specific target database platform. For example, the SqlClient could connect only to SQL Server systems, not to Oracle systems. Likewise, the OracleClient could connect only to Oracle systems, not to SQL Server systems. While you could build your code to load the correct provider on the fly, the result wasn’t elegant, and it was certainly not convenient. ADO.NET 2.0 solves this problem by adding a new Provider Factory capability that is capable of instantiating the appropriate provider at run time. The following example illustrates how the new Provider Factory is used:

DbDataReader rdr; DbProviderFactory provider =     DbProviderFactories.GetFactory("System.Data.SqlClient"); using (DbConnection cn = provider.CreateConnection()) {     using (DbCommand cmd = provider.CreateCommand())     {         cmd.CommandText = "SELECT * FROM Production.Location";         cmd.Connection = cn;         cn.ConnectionString =            ("SERVER=TECA-YUKON;INTEGRATED SECURITY=True;"               + "DATABASE=AdventureWorks");         cn.Open();         rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);         DataTable dt = new DataTable("Product Locations");         dt.Load(rdr);         dataGridView1.DataSource = dt;     } }

Here, you can see that the GetFactory method is used to create an instance of the System.Data.SqlClient data provided at run time. Then, a DbCommand object is used to execute a command to retrieve the contents of the Production.Location table, which are passed to the DbDataReader. Finally, the results of the DbDataReader are loaded to a DataTable that’s bound to a dataGridView object.

Note 

You need to include the System.Data.Common namespace to use the DbProvider objects.