DataStream

LANGUAGES: ADO.NET | XML

ASP.NETVERSIONS: 1.0 | 1.1

EnhanceYour Data Access With ADO.NET 2.0

ADO.NET2.0 introduces a slew of new data access capabilities to improve theperformance, scalability, and maintainability of your applications.

By BrianNoyes

At theend of October, Microsoft unveiled a host of new technologies at theProfessional Developers Conference (PDC) that will have significant impact ofthe future of development for the Windows platform. The main topics of PDCincluded Longhorn (the next version of Microsoft Windows), Whidbey (version 2.0of the .NET Framework and the next version of Visual Studio), and Yukon (thenext version of SQL Server). .NET 2.0 will include significant enhancements forjust about every kind of application you are developing for Windows and the Webtoday. The December issue of asp.netPRO has extensive coverage of thenew features of ASP.NET 2.0. In this article, I want to introduce you brieflyto some of the new features coming in ADO.NET 2.0 that you can use to enhanceyour data access architectures and code.

TwoIntersecting Worlds of Data

As usualwith .NET, there are two major axes to the data access improvements - thosefocusing on relational data access and those focusing on XML data access. Someof the enhancements in both arenas are just improvements to the existingclasses in the .NET Framework to make them more flexible or better for advanceddata access scenarios. A lot of the changes have to do with taking advantage ofsome of the new capabilities provided by Yukon, the next version of SQL Server,that will be released at about the same time as .NET 2.0. The changes arepretty widespread, so all I have room for here is a quick overview and summaryof the changes to the ADO.NET relational side of things. I'll be covering thechanges in the XML arena in future articles, but wanted to mention them here towhet your appetite. Figure 1 summarizes the significant areas of change in therelational and XML data access functionality in .NET 2.0.

Figure1. Summary of newdata access features in .NET 2.0.

Thereare several thrusts I want to cover on the relational front. The first has todo with improvements to enable the use of managed languages within the SQLServer environment to write user-defined types, stored procedures, user-definedfunctions, and triggers. There has already been a lot of discussion andcontroversy surrounding this capability and a lot of debate as to whether thisis really a good thing. I am a firm believer in keeping your stored proceduresas simple as possible for maintainability in enterprise applications. Youshould keep your business logic encapsulated in objects in your middle tier,unless you are forced to push some of that logic down into the database fornetwork bandwidth reasons. It is much easier to scale out and reuse businessobjects in the middle tier than business logic in the database.

With theability to write code in the database using managed languages, there is goingto be a natural temptation to put more and more business logic into thedatabase. I think people should resist that urge, but there are still manyscenarios where you need to have some logic in the database, and the ability towrite that code in the managed language of your choice instead of with T/SQLwill definitely lead to more maintainable code in the data tier. When youcouple that with the features that Visual Studio Whidbey brings for steppinginto your managed code running within the SQL Server environment, the code yourun within SQL Server suddenly becomes much more understandable andmaintainable.

SoADO.NET 2.0 includes a set of classes that form the layer between your managedcode and the SQL Server environment. There are modifications and additions tothe classes you know and love, such as SqlCommand, SqlDataReader, andSqlParameter. There are also a number of new classes related to connecting tothe data environment within SQL Server and executing commands. There is a newclass to encapsulate query results called SqlResultSet that gives youserver-side cursor capability that you may have been missing in thedisconnected world of ADO.NET data access outside the database. There is alsodirect support for settings and receiving SQL Server notifications, allowing youto design more event driven data applications.

Commandsare from Venus, Queries are from MARS

Anothernew capability in ADO.NET 2.0 is focused on improving the performance andscalability of your data access code through asynchronous execution of commands.The first part of this is something called Multiple Active Result Sets (MARS),which lets you execute multiple queries simultaneously on an open connection.With ADO.NET in version 1.X of the Framework, you could only have one activequery on an open connection, such as an open SqlDataReader. MARS addresses thatand allows you to have more than one open cursor in the database while you areiterating through the results. There is also an asynchronous execution patternwrapped around the SqlCommand object now, so that you can issue a query andhave a callback method executed when the query is completed.

Anothercommon requirement that was lacking in ADO.NET 1.x was the ability to easilyand efficiently ship data from one source to another programmatically. ADO.NET2.0 includes a SqlBulkCopyOperation class that allows you to performprogrammatic transfer of data from one source to another in a fashion similarto that achieved using the bcp.exe utility. The code to do this is as simple asthat shown in Figure 2.

public voidCopyCustomers()

{

   SqlConnection connSrc = newSqlConnection(m_connStrSrc);

   SqlConnection connDest = newSqlConnection(m_connStrDest);

   SqlCommand cmdGet = newSqlCommand("SELECT * FROM Customers", connSrc);

   SqlBulkCopyOperation bulkCopy = newSqlBulkCopyOperation(connDest);

   SqlDataReader readerSrc = null;

   try

   {

      connSrc.Open();

      connDest.Open();

      SqlDataReader readerSrc =cmdGet.ExecuteReader();

      bulkCopy.DestinationTableName ="Customers";

      bulkCopy.WriteDataReaderToServer(readerSrc);

   }

   finally

   {

      bulkCopy.Close();

      if (readerSrc != null)

      {

         readerSrc.Close();

      }

      connSrc.Close();

      connDest.Close();

   }

}

Figure2. The newSqlBulkCopyOperation class allows you to push data from one source to anotherin bulk.

If youperformed updates using .NET 1.X with DataSets and SqlDataAdapters with largenumbers of modified rows, you may have noticed a significant performance hit.This is because the way the SqlDataAdapter works in .NET 1.X is to perform aseparate update, insert, or delete query for each modified row in a DataSetthat is passed to the SqlDataAdapter, resulting in a separate round trip to thedatabase for each row. The SqlDataAdapter has been modified to allow batchedupdates, meaning it can group those update queries into a batch and only makeone round trip to the database for each batch. You can change the batch sizejust by setting a property on the SqlDataAdapter.

Thefinal new relational capability I wanted to highlight is a newobject-relational mapping engine called ObjectSpaces. ObjectSpaces forms alayer between a relational data source and your business objects to allow youto retrieve and store object state without having to use any explicit dataaccess code (see Figure 3). You provide a set of XML schemas that define therelational schema, the object schema, and a mapping between the two. There aretools that will be available as part of Visual Studio and the Framework to helpyou create these schema files. Using these schemas, the ObjectSpaces engineallows you to load and save the state of the objects into an underlying set oftables in the database without the application code needing to know anythingabout what that underlying relational storage looks like. I'll be writing anarticle covering all the details on programming with ObjectSpaces in the Marchissue of C#PRO.

Figure 3. ObjectSpaces forms a layer betweena relational data source and your business objects to allow you to retrieve andstore object state without having to use any explicit data access code.

As youcan see, the new features in ADO.NET are more than cosmetic improvements. Youexisting ADO.NET code should continue to work just fine, but for futureapplications, there are some significant new capabilities you can takeadvantage of to build more robust, maintainable, and scalable data-drivenapplications.

Brian Noyes is a consultant, trainer, speaker, andwriter with IDesign, Inc. (http://www.idesign.net),a .NET focused architecture and design consulting firm. Brian specializes indesigning and building data-driven distributed applications. He has more than12 years experience in programming, engineering, and project management, and isa contributing editor for C#PRO, asp.netPRO, and other publications. Contacthim at mailto:brian.noyes@idesign.net.