Chapter 9

ADO.NET and LINQ

What's in this chapter?

The architecture of ADO.NET

How ADO.NET connects to databases

Using ADO.NET to retrieve data

Using ADO.NET to update databases

Creating and using transactions

Wrox.com Code Downloads for this Chapter

The wrox.com code downloads for this chapter are found at www.wrox.com/remtitle.cgi?isbn=9781118314456 on the Download Code tab. The code is in the chapter 9 download and individually named according to the code file names throughout the chapter.

ADO.NET 1.x was the successor to ActiveX Data Objects 2.6 (ADO). The goal of ADO.NET 1.x was to enable developers to easily create distributed, data-sharing applications in the .NET Framework. The goals of ADO.NET today are to improve the performance of existing features in ADO.NET 1.x, to provide easier use, and to add new features without breaking backward compatibility.

ADO.NET 1.x was built upon industry standards such as XML and XSD, and it provided a data-access interface to communicate with data sources such as SQL Server and Oracle. ADO.NET 4.5 continues to build upon these concepts, while increasing performance. Applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update data. ADO.NET 4.5 does not break any compatibility with ADO.NET 2.0 or 1.x; it only adds to the stack of functionality.

In solutions that require disconnected or remote access to data, ADO.NET uses XML to exchange data between programs or with Web pages. Any component that can read XML can make use of ADO.NET components. A receiving component does not even have to be an ADO.NET component if a transmitting ADO.NET component packages and delivers a data set in an XML format. Transmitting information in XML-formatted data sets enables programmers to easily separate the data processing and user interface components of a data-sharing application onto separate servers. This can greatly improve both the performance and the maintainability of systems that support many users.

For distributed applications, ADO.NET 1.x proved that the use of XML data sets provided performance advantages relative to the COM marshaling used to transmit disconnected data sets in ADO. Because transmission of data sets occurred through XML streams in a simple text-based standard accepted throughout the industry, receiving components did not require any of the architectural restrictions required by COM. XML data sets used in ADO.NET 1.x also avoided the processing cost of converting values in the Fields collection of a Recordset object to data types recognized by COM. Virtually any two components from different systems can share XML data sets, provided that they both use the same XML schema for formatting the data set. This continues to be true in ADO.NET 4.5, but the story gets better. The XML integration in ADO.NET today is even stronger, and extensive work was done to improve the performance of the DataSet object, particularly in the areas of serialization and memory usage.

ADO.NET also supports the scalability required by Web-based data-sharing applications. Web applications must often serve hundreds, or even thousands, of users. By default, ADO.NET does not retain lengthy database locks or active connections that monopolize limited resources. This enables the number of users to grow with only a small increase in the demands made on the resources of a system.

One of the issues some developers experience when working with ADO.NET and various databases is that you need to leverage at least two languages: Visual Basic and the version of SQL used by the database. To reduce this separation, Microsoft developed LINQ (Language Integrated Query). With LINQ, you can include the query within your Visual Basic code, and the query you add to your code is translated into the specific query language of the data store. One of the most common uses for LINQ is in working with databases (you will also see LINQ used in querying XML in Chapter 8) in its form as a “better” SQL.

The use of LINQ and SQL Server leads to one point of confusion: While LINQ can be used to query any database (or set of objects, XML, or other LINQ provider), there is also a specific technology known as LINQ to SQL. This is an SQL Server–specific query tool that uses LINQ as its query mechanism.

In this chapter, you will see that ADO.NET is a very extensive and flexible API for accessing many types of data, and because ADO.NET 4.5 represents an incremental change to the previous versions of ADO.NET, all previous ADO.NET knowledge already learned can be leveraged. In fact, to get the most out of this chapter, you should be fairly familiar with earlier versions of ADO.NET and the entire .NET Framework.

This chapter demonstrates how to use the ADO.NET object model in order to build flexible, fast, and scalable data-access objects and applications. Specifically, it covers the following:

• The ADO.NET architecture
• Some of the specific features offered in ADO.NET, DataSet performance improvements, and asynchronous processing
• Working with the common provider model