What is a Connection?

Chapter 3 - Connecting to a Data Source
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

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.

The word "connection" can be used in many ways, but in this chapter (and in this book in general) we mean a data connection between Internet Information Server (IIS) and a data store. We are not discussing the HTTP connection between the web browser and IIS.

In a superficial way, we can think of the connection as a layer between the data and the page that's eventually displayed on the user's screen. More specifically, it is the layer between the provider that interacts directly with the data source, and the ADO.NET objects that use the data (for example, the DataSet object). These objects (with help from command objects, DataTables, and others) then pass information to ASP.NET controls for display.

Like so much else in the .NET Framework, an ADO.NET connection is presented to the programmer as an object. In fact, there are a number of different connection objects; which one you find yourself using will depend upon the data source you're dealing with. Once you've created a connection object, however, the range of operations that you can perform with it is for the most part independent of the data source. The following diagram shows where the connection object fits in the layers of communication that exist between a data source and an ASP.NET page:

As this discussion develops, it's important to keep in mind two things that a connection object doesn't do. First, a connection object doesn't specify what data (what fields and what records) to get from a specified source - as we'll see later, this is done by a command object, working in conjunction with a data reader or data adapter object. Second, the connection object doesn't directly cause the data store to perform reading or writing on the data. The connection simply starts a data session that's supported by ADO.NET at the data consumer end, and by the database engine (or some other data source) at the data provider end.

The word "session" is used in two different contexts, and it is important to distinguish them properly. While you may be familiar with the term as it refers to a visitor's interaction with the pages of a web site, a session may also be understood in the context of establishing a connection with a source of data. A data session is created between the web server and the data source when a connection is established, and exists for the duration of that connection.

Differences between ADO and ADO.NET Connections

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

As mentioned briefly above, 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.

Note 

In fact, there's a fourth kind of 'connection', which takes place when we work with data in XML format. However, since XML data resides in files without a data management system, we don't actually use a connection object. Instead, a DataSet can be used to read the XML file directly. We'll investigate how to do this later in the chapter.