Differences between ADO and ADO.NET Connections

What is a Connection?

An ADO.NET connection provides a conduit for communication between the data consumer (say, the ASP.NET code that you write, executed on IIS) and the source from which you need to retrieve data. This conduit, though, does more than just relay information - it can also perform translation of the data as it moves from the store to the ASP.NET page.

If you've had any dealings with 'classic' versions of ADO (ADO 2.0, ADO 2.1, and so on), you may be wondering about the differences between ADO.NET and ADO. With regard to connection objects, there are five basic changes:

• Classic ADO connections were usually used to fill a Recordset object. In ADO.NET, the role of Recordset is taken by DataSet, while data reader objects replace the old read-only, forward-only semantics that the Recordset object embodied.
• DSNs and UDLs have gone away. In ADO.NET, we simply specify the data provider and the location of the data as properties of the connection object. (Although this could be done with the old connection object, it was not the most common technique.)
• In classic ADO, OLE DB providers and ODBC drivers were used. Now, the preferred way of accessing data is with the .NET data providers, which we'll discuss below.
• In ADO.NET, we cannot execute an SQL statement directly from a connection object. At a minimum, we must use a command object too.
• Classic ADO enables a connection to persist in an open state. In almost all cases, ADO.NET works with disconnected data.

If you haven't dealt with ADO before, don't worry: in ADO.NET, many of the complexities alluded to above have been ironed out. In the pages to come, we'll be covering all that you need to know in order to use ADO.NET with confidence.

Database Connections Available in ADO.NET

one of the most important advances from classic ADO to ADO.NET is the creation of .NET data providers. When you install the .NET Framework, two such providers come along with it: the OLE DB .NET data provider, and the SQL Server .NET data provider. A third - the ODBC .NET data provider - may be downloaded from Microsoft (http://www.microsoft.com/downloads; search for 'ODBC .NET data provider'). It's likely that other specialized .NET data providers will become available in the future, not necessarily from Microsoft.

The SQL Server .NET data provider is highly optimized for connections to just one type of data source: Microsoft SQL Server 7, or higher. It isn't even able to talk to other SQL-language RDBMSs (such as Oracle, or IBM's DB2). The SQL Server .NET data provider connects to the RDBMS at the level of the Tabular Data Stream (TDS), and thus completely avoids the OLE DB layer. If you're using Microsoft SQL Server, as we'll spend most of our time doing in this book, then the SQL Server data provider is by far your best choice.

TDS is an internal data transfer protocol for Microsoft SQL Server that can be exposed to allow communication between different operating systems, using a variety of different network transport technologies.

For almost all other data sources, we can use the OLE DB .NET data provider. This works with any OLE DB-compliant data source, including SQL Server, but in that particular case it's slower than the SQL Server .NET data provider we just mentioned. The OLE DB .NET data provider's versatility is such that it can be used with non-relational data such as Excel spreadsheets, right up to enterprise-strength relational systems such as Oracle and IBM's DB2.

Our third alternative here - the ODBC .NET data provider - has two uses. First, we can employ it to connect to data at the ODBC level - this is 'lower down' than OLE DB, and thus slightly faster. Second, there are some old data stores for which only an ODBC service is available, such as Borland's Paradox. Unless your situation requires ODBC, however, it's better to work with the supplied OLE DB .NET data provider.