Introduction to ADO.NET

Interested in .NET? Don't miss SitePoint's .NET Feature Guide -- it's an excellent resource!

The advent of ASP back in late 1996 when ADO 1.0 was released represented a new way of dynamically retrieving data from a database. Though ADO was at its very infancy then, and was something of an offshoot of DAO and RDO, nevertheless it represented a new bold direction. Each subsequent version of the technology leads us one step closer to ADO 2.6, when development came to an end. At this point, emerging from the shadows came the most revolutionary framework to date - .NET, with it the very powerful and mature ADO.NET.

This new data component, introduced with .NET, presented an exciting new approach to data access. Though the techniques, and logic used to connect to databases with ADO.NET weren't startlingly different from those used with its predecessor, ADO.NET had a lot to offer. What was unique about this technology was the architecture beneath it all, its powerful approach to data management, and the flexibility in the next level of data-presenting devices.

ADO, for its time, was certainly successful. Although the re-releases of the technology hadn't heralded dramatic change for ADO, it achieved what it had to within its own architecture. However, it was continually plagued by problems that surrounded its handling of disconnected data stores, and functioning properly and concisely with XML. This is where ADO.NET stepped in - these are the two things the new technology easily manages, as XML is the core component of the entire .NET Framework! ADO.NET was simply created to remedy all the deficiencies found in ADO, and provide developers with the power to achieve more with less.

In this article we'll dive right into accessing data in .NET, and demonstrate the variety of options available to the ADO.NET developer for data access and presentation. This article is intended as a concise introduction to data access in .NET that'll have you up to speed on the technology in no time.

Namespaces

With classic ASP (or other database accessing methods), you need to first connect to your data store before you can even begin to contemplate any data retrieval. However, in ADO.NET there are two core objects that allow developers to work with data initially: the DataReader and the DataSet. We'll soon learn all about them.

In any .NET data access page, before you connect to a database, you first have to import all the necessary namespaces that will allow you to work with the objects required. As we're going to work with SQL Server, we'll first import the namespaces we need. Namespaces in .NET are simply a neat and orderly way of organizing objects, so that nothing becomes ambiguous.

<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

Note: If we were using a database other than SQL, for instance, MS Access, we would then replace the SQLClient with OleDb. If we use Oracle, .NET v 1.1 provides the System.Data.OracleClient namespace, and for any ODBC data source it provides the System.Data.Odbc namespace. You'll find detailed information on all the available methods and objects we'll discuss in the .NET SDK Framework documentation.

The Connection

After we import all the necessary namespaces, we're ready to connect to our database. Now, whether you implement the DataReader or Dataset, your initial database connection will still be as follows:

SqlConnection objConnect = new SqlConnection (Your Connection String);

objConnect.Open();

Above, we set up our SQLConnection Connection object with our database connection information, and then we opened it. Listed below are the common connection object methods we could work with:

• Open - Opens the connection to our database
• Close - Closes the database connection
• Dispose - Releases the resources on the connection object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Close method as well.
• State - Tells you what type of connection state your object is in, often used to check whether your connection is still using any resources. Ex. if (ConnectionObject.State == ConnectionState.Open)

As far as opening a database connection goes, that's really the extent of it. Now we have to decide which object to use in order to achieve the end results you wish to present. We now have to choose whether to work with a Datareader or the Dataset. Let's begin by looking at the DataReader.