ADO.NET is the .NET enhanced version of ADO that we all know and love.
ADO.NET aims to address some of the deficiencies of traditional ADO and present
a model that provides type safety, OOP, and efficiency.
This article will demonstrate the most common task when accessing a database:
querying for data and traversing that data from start to finish in order to
display the contents (or subset thereof) of a table.
The DataReader class
ADO.NET replaces the concept of data rows with the
object. This essentially provides us with full access to a given
database, including all rows, tables and relationships in an object oriented and
type-safe manner. It is, however, total overkill for the simple query and
traversals that are most often performed on databases.
For this simple case .NET provides us with the
SqlDataReader) that is essentially a type safe read only, forward
only rowset. The differences between the various flavours of
DataReaders is in which data access library they use.
SqlDataReader works best with SQL Server, while the others work
best with ODBC and OleDB data srouces.
All we need to do is open a connection to a database, send an SQL command,
then traverse through the resultant
DataReader using the
Read command and process the results.
The easiest way to illustrate this is to show you some code. This snippet
opens an Access database, reads all the information from a table, then populates
a List View control with the data inside.
A few notes on the code:
RichTextBox controls declared as
private System.Windows.Forms.RichTextBox fileName;
private System.Windows.Forms.RichTextBox StatusText;
listView is a list view control declared as
listView = new System.Windows.Forms.ListView ();
The list view has been placed in details mode using
listView.View = System.Windows.Forms.View.Details;
OleDbConnection Connection = new OleDbConnection ();
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileName.Text + ";" +
"Persist Security Info=False;";
OleDbCommand command = new OleDbCommand();
command.Connection = Connection;
command.CommandText = "SELECT * FROM Authors";
dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
int nFields = dataReader.FieldCount;
for (int i = 0; i < nFields; i++)
int nRow = 0;
String  subitems = new String[nFields];
for (int i = 0; i < nFields; i++)
subitems[i] = dataReader[i].ToString();
ListViewItem item = new ListViewItem(subitems, -1);
StatusText.Text = nFields.ToString() + " columns, " +
nRow.ToString() + " rows read";
catch (Exception ex)
StatusText.Text = "Error: " + ex.Message;
if (Connection.State == System.Data.ConnectionState.Open)
That's all there is to it. We have closed the database connection but since
we are using managed code there is no need (or way) to delete the objects and
memory we allocated.
23 Aug 2003 - Updated to .NET 1.0/1.1. Previous version was for .NET beta
Chris is the Co-founder, Administrator, Architect, Chief Editor and Shameless Hack who wrote and runs The Code Project. He's been programming since 1988 while pretending to be, in various guises, an astrophysicist, mathematician, physicist, hydrologist, geomorphologist, defence intelligence researcher and then, when all that got a bit rough on the nerves, a web developer. He is a Microsoft Visual C++ MVP both globally and for Canada locally.
His programming experience includes C/C++, C#, SQL, MFC, ASP, ASP.NET, and far, far too much FORTRAN. He has worked on PocketPCs, AIX mainframes, Sun workstations, and a CRAY YMP C90 behemoth but finds notebooks take up less desk space.
He dodges, he weaves, and he never gets enough sleep. He is kind to small animals.
Chris was born and bred in Australia but splits his time between Toronto and Melbourne, depending on the weather. For relaxation he is into road cycling, snowboarding, rock climbing, and storm chasing.