Data Access with ADO.NET

If you are anything like I was when you began your search for knowledge on Microsoft’s .NET technology, you were quite confused when first confronted with ADO.NET and how to implement it within your ASP.NET pages. This series of articles will first be a simple overview of what ADO.NET is, and second, will be a look at the specific objects and functionality that make up ADO.NET as a whole. My hope is that this series of articles will be beneficial to intermediate as well as advanced developers who are moving to .NET. Any prior knowledge of ADO and ASP.NET will be particularly helpful. So let’s get started!

We should begin by noting that ADO.NET is not an upgrade to ADO 2.6, it is a complete rewrite, similar to the Microsoft .NET Platform itself. The move toward .NET technologies involves many new concepts and thought processes behind web applications development, and ADO.NET is certainly no exception. With ADO 2.6, developers only had three objects to work with when accessing and manipulating data: the connection, command, and recordset objects. While these objects were innovative at the time of their release, they now pose inherent problems as we strive for a distributed model of systems development. One major problem, for example, is that a recordset is a large COM object that is not easily passed around a network. Other problems also exist. ADO recordset objects are difficult to share across computing platforms and cannot penetrate firewalls. Additionally, if a recordset is created as a result of a JOIN query, the original data sources can be difficult to update.

So you can see that ADO, while innovative, has areas that require improvement. Now we enter the .NET era of application development. ADO.NET has been created with a multitude of objects that are designed to carry out very specific functional tasks and solve the problems listed above. These objects come in two varieties: SQL Server optimized or Ole-DB. This is of particular significance to developers who are using a full suite of Microsoft tools to build their applications. If SQL Server is your database of choice, ADO.NET provides you with a set of objects that bypass the Ole-DB provider and directly access SQL Server’s tabular data stream. This direct access to SQL Server’s proprietary API provides noticeable performance gains. If you are accessing MS Access, Oracle, Sybase, etc., you have a completely different set of classes that encompass the same functionality as the SQL Server optimized objects, but through an Ole-DB provider.

To access the ADO.NET functionality, you must import namespaces, which house the data objects we need to carry out specific functions, into the pages you create. The following is a listing of the main namespaces required to manage data in the .NET environment and a brief explanation of the functionality they expose.

System.Data

Exposes objects used to access and store relational data. The DataSet, DataReader, and DataRelation objects are used to create relational data stores in virtual memory. These objects are independent of data source, meaning that relational SQL Server data, XML, or array data, for example, can be accessed using the same type of properties and methods. This namespace should most likely be imported into every .NET page that accesses data. Some objects of this namespace include:

  • DataSet
  • DataTable
  • DataRow
  • DataRelation

System.Data.OleDb

Contains the objects that allow us to access data through an Ole-DB provider. These objects, as mentioned earlier, have the same properties and methods as the SQLClient namespace that I will cover next. Objects include:

  • OleDbConnectin
  • OleDbCommand
  • OleDbDataSet
  • OleDbDataReader
  • OleDbDataAdapter

System.Data.SQLClient

Encapsulates all the functionality that we need to do any type of data reading or manipulation from Microsoft’s SQL Server. Objects include:

  • SQLConnection
  • SQLCommand
  • SQLDataSet
  • SQLDataReader
  • SQLDataAdapter

As you can tell by comparing the names of each object in the OleDb and SQLClient namespaces, the objects are built exactly alike for different access methods. This is a distinction that you as a developer will not have to be concerned with once you choose your data source.

Just from the onset, it is very obvious that the expanded classes can provide us with much more functionality than we have ever had in the past. In my next article, I will be discussing the most basic of ADO.NET objects: SQLConnection, SQLCommand, and SQLDataReader for reading database information onto a web page with ASP.NET (remember that the "SQL" and "OleDb" prefixes to object names are completely interchangeable).

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles