Click here to Skip to main content
15,861,125 members
Articles / Programming Languages / C#
Article

The ADO.NET OleDbDataReader class

Rate me:
Please Sign up or sign in to vote.
4.87/5 (19 votes)
22 Aug 2003CPOL 304.2K   3.9K   59   23
Accessing database information using the DataReader class from ADO.NET in C#

Sample Image - ADONET_datareader.gif

Introduction

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

DataSet 
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 DataReader class (OleDbDataReader, OdbcDataReader and 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:

  • StatusText and fileName are RichTextBox controls declared as
    private System.Windows.Forms.RichTextBox fileName;
    private System.Windows.Forms.RichTextBox StatusText;
  • listView is a list view control declared as
    System.WinForms.ListView listView;
    listView = new System.Windows.Forms.ListView ();

    The list view has been placed in details mode using

    listView.View = System.Windows.Forms.View.Details;

The Code

C#
OleDbConnection Connection = new OleDbConnection ();

try
{
    // Open a connection to the database
    Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                  "Data Source=" + fileName.Text + ";" +
                                  "Persist Security Info=False;";
    Connection.Open();

    // Create an OleDb command, 
    OleDbCommand command = new OleDbCommand();
    command.Connection = Connection;
    command.CommandText = "SELECT * FROM Authors";

    // Execute and return the rows in the data reader object
    OleDbDataReader dataReader;
    dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

    int nFields = dataReader.FieldCount;

    // Setup the columns in the listview using the fields in the table
    listView.Clear();
    for (int i = 0; i < nFields; i++)
    {
        listView.Columns.Add(dataReader.GetName(i), 100, 
                             HorizontalAlignment.Left);
    }

    // Fill the rows in the listview using the data in the rows
    int nRow = 0;
    while (dataReader.Read())
    {
        // Create an array of subitems for quick insertion
        // The subitems will be all fields in the row except for 
        // the first field
        String [] subitems = new String[nFields];
        for (int i = 0; i < nFields; i++)
        {
            subitems[i] = dataReader[i].ToString();
        }

        // Insert a new item into the listview, and add the subitems at 
        // the same time. The item will be the first field in the row
        ListViewItem item = new ListViewItem(subitems, -1);
        listView.Items.Add(item);

        ++nRow;
    }    
    dataReader.Close();

    // Set the status text
    StatusText.Text = nFields.ToString() + " columns, " + 
          nRow.ToString() + " rows read";
}
catch (Exception ex)
{
    // If an error occured alert the user
    StatusText.Text = "Error: " + ex.Message;
}
finally
{
    // Close the connection if necessary
    if (Connection.State == System.Data.ConnectionState.Open)
        Connection.Close();
}

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.

History

23 Aug 2003 - Updated to .NET 1.0/1.1. Previous version was for .NET beta 1.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Founder CodeProject
Canada Canada
Chris Maunder is the co-founder of CodeProject and ContentLab.com, and has been a prominent figure in the software development community for nearly 30 years. Hailing from Australia, Chris has a background in Mathematics, Astrophysics, Environmental Engineering and Defence Research. His programming endeavours span everything from FORTRAN on Super Computers, C++/MFC on Windows, through to to high-load .NET web applications and Python AI applications on everything from macOS to a Raspberry Pi. Chris is a full-stack developer who is as comfortable with SQL as he is with CSS.

In the late 1990s, he and his business partner David Cunningham recognized the need for a platform that would facilitate knowledge-sharing among developers, leading to the establishment of CodeProject.com in 1999. Chris's expertise in programming and his passion for fostering a collaborative environment have played a pivotal role in the success of CodeProject.com. Over the years, the website has grown into a vibrant community where programmers worldwide can connect, exchange ideas, and find solutions to coding challenges. Chris is a prolific contributor to the developer community through his articles and tutorials, and his latest passion project, CodeProject.AI.

In addition to his work with CodeProject.com, Chris co-founded ContentLab and DeveloperMedia, two projects focussed on helping companies make their Software Projects a success. Chris's roles included Product Development, Content Creation, Client Satisfaction and Systems Automation.

Comments and Discussions

 
Questionusing System.Data.ADO; Pin
gs_virdi7-Jul-11 20:25
gs_virdi7-Jul-11 20:25 
GeneralOleDbDataReader Item parameters Pin
Coder_20071-Nov-06 9:59
Coder_20071-Nov-06 9:59 
GeneralOleDbDataReader with parameters Pin
salaikumar24-Oct-06 4:45
salaikumar24-Oct-06 4:45 
Hi i have a code like this...
it works fine without any conditions(parameters). But when i give the parameters it returns null. but if I copy the query (at runtime) and run it, it returns rows in oracle...pls help whats wrong with my code

ConnStr = "Provider=oraOLEDB.oracle.1;User ID=ss;Password=ss;Data Source=ss";
cn->ConnectionString = ConnStr;
cn->Open();
strSql1= String::Concat(S"Select * from TEST WHERE TEST.CREATEDdate BETWEEN ? AND ?");
cmd->CommandType=CommandType::Text;
cmd->Connection=cn;
cmd->CommandText = strSql1;
OleDbParameter *p1 = new OleDbParameter();
OleDbParameter *p2 = new OleDbParameter();
cmd->Parameters->Add(p1);
cmd->Parameters->Add(p2);

p1->Value = S"27-Apr-2006";
p2->Value = S"28-Apr-2006";
OleDbDataReader *Rdr = cmd->ExecuteReader();

Rdr->get_RecordsAffected();
while(Rdr->Read())
{
textBox1->Text = Rdr->GetValue(1)->ToString();
}
Rdr->Close();


Please help its annoying...


Salai

GeneralADO.Net RadioButton.... Pin
tellek_liberty31-Jul-04 14:43
tellek_liberty31-Jul-04 14:43 
GeneralUse for function Pin
Anonymous30-Mar-04 11:16
Anonymous30-Mar-04 11:16 
Questionhow to know about complete database Pin
xprtguro15-Dec-03 23:25
xprtguro15-Dec-03 23:25 
GeneralDataReader is slooow... Pin
Braulio Dez16-Sep-03 4:02
Braulio Dez16-Sep-03 4:02 
GeneralRe: DataReader is slooow... Pin
Quentin Pouplard18-Sep-03 4:24
Quentin Pouplard18-Sep-03 4:24 
GeneralRe: DataReader is slooow... Pin
N a v a n e e t h6-Mar-07 23:56
N a v a n e e t h6-Mar-07 23:56 
GeneralADO .Net Pin
caddsman5-Sep-03 9:48
caddsman5-Sep-03 9:48 
GeneralRe: ADO .Net Pin
Kannan Kalyanaraman6-Sep-03 1:03
Kannan Kalyanaraman6-Sep-03 1:03 
GeneralRe: ADO .Net Pin
caddsman8-Sep-03 8:20
caddsman8-Sep-03 8:20 
GeneralRe: ADO .Net Pin
KhSergey6-Sep-03 12:53
KhSergey6-Sep-03 12:53 
Generalreturning multiple &quot;recordsets&quot; Pin
3alei1-Jul-03 2:48
3alei1-Jul-03 2:48 
GeneralRe: returning multiple &quot;recordsets&quot; Pin
Mike.NET24-Aug-03 14:41
Mike.NET24-Aug-03 14:41 
QuestionADODataReader? Pin
Paul Selormey7-Nov-02 22:01
Paul Selormey7-Nov-02 22:01 
AnswerRe: ADODataReader? Pin
Vicente Gras8-Jan-03 6:42
Vicente Gras8-Jan-03 6:42 
GeneralRe: ADODataReader? Pin
Paul Selormey8-Jan-03 13:12
Paul Selormey8-Jan-03 13:12 
GeneralMultiple DataReaders Pin
16-Jan-02 18:26
suss16-Jan-02 18:26 
GeneralRe: Multiple DataReaders Pin
Chris Maunder16-Jan-02 19:03
cofounderChris Maunder16-Jan-02 19:03 
GeneralRe: Multiple DataReaders Pin
lizhill8-Aug-02 23:05
lizhill8-Aug-02 23:05 
GeneralAdoDataReader and SqlData Reader objects Pin
Ron Teal11-Jul-01 5:09
Ron Teal11-Jul-01 5:09 
QuestionWhat about huge data amounts Pin
21-Dec-00 6:55
suss21-Dec-00 6:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.