Click here to Skip to main content
11,719,480 members (87,358 online)
Click here to Skip to main content

OLE DB - First steps

, 1 Dec 2001 CPOL 276.5K 2.7K 23
Rate this:
Please Sign up or sign in to vote.
Basic introduction to using OLE DB to insert, update and read records from a database

Introduction

This article intends to introduce you to using OLE DB for database access. It does this by showing you how to use OLE DB to insert records into a database and then read back those records. Before you start there are some things you need to do first, like creating an MDB file.

Things to do first...

  • First create a new MS Access database called test.mdb and create a single table and call it 'main'.
  • Now add two fields to 'main' called 'Name' of type 'Text' and 'Age' of type 'Number'.
  • Copy test.mdb to d:\

Some concepts

In .NET, connections to databases and queries are achieved through data providers. The OLE DB .NET data provider is implemented through various classes within the System::Data::OleDb namespace. In this article we only examine three of these classes - OleDbConnection, OleDbCommand and OleDbDataReader. The OleDbConnection object represents a database connection. The OleDbCommand object wraps an SQL command that is performed on a database connection.  When we are making an INSERT or an UPDATE query on a database table, those two are the only classes we'll need. But when we are retrieving data from a table, we'll also need to use the OleDbDataReader class. This class allows us to browse through a row of records in a forward-only direction.

Inserting records code snippet

//Create the OleDbConnection object 
//and associate it with our database
OleDbConnection* conn = new OleDbConnection(
    "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb");

//Open the database connection
conn->Open();

//Create an OleDbCommand object and
//pass it the SQL command and the OleDbConnection
//object to use to connect to the database
OleDbCommand* cmd = new OleDbCommand(sqlstr,conn);

//Execute the SQL command
cmd->ExecuteNonQuery();

//Close the connection to the database
conn->Close();

Inserting records is the simpler of the two processes. We create a connection using the OleDbConnection object, create an OleDbCommand object and associate it with the OleDbConnection object. Now we call the ExecuteNonQuery method, which will execute the SQL command we had passed to the OleDbCommand  constructor. We then close the connection.

Reading records code snippet

//Create the OleDbConnection object 
//and associate it with our database
OleDbConnection* conn = new OleDbConnection(
    "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb");

//Open the database connection
conn->Open();

//Create an OleDbCommand object and
//pass it the SQL read query and the connection to use
OleDbCommand* cmd = new OleDbCommand(sqlstr,conn);

//Procure the OleDbDataReader object to browse the recordset 
OleDbDataReader* rdr = cmd->ExecuteReader();

//Keep reading records in the forward direction
while (rdr->Read())
{
   //Use one of the various methods available to read the data
   //Eg:- GetValue, GetValues, Item etc.
    . . .
    . . .    
}

//Close the connection to the database
conn->Close();

This is basically the same as far as creating the OleDbConnection and OleDbCommand  objects are concerned. But instead of calling ExecuteNonQuery directly, we call ExecuteReader which will return a OleDbDataReader object. We can use this OleDbDataReader object to browse through the recordset. Keep calling Read which will return false when it has finished the whole recordset. There are several ways to read from a recordset but I prefer get_Item which allows you to specify a field name opposed to other functions like GetValue which require us to pass the index of the field in the table which is a bad method in my opinion.

Console::WriteLine(rdr->get_Item("FullName"));

There is a sequel to this article on the use of bound controls with OLE DB which you can find here.

Revision History

  • Jul 04 2002 - Did a full redo of the article, added a sample project and now uses MC++ instead of C#
  • Dec 02 2001 - Changed the program for .Net beta 2.0

License

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

Share

About the Author

Nish Nishant
United States United States
Nish Nishant is a Software Architect/Consultant based out of Columbus, Ohio. He has over 15 years of software industry experience in various roles including Lead Software Architect, Principal Software Engineer, and Product Manager. Nish is a recipient of the annual Microsoft Visual C++ MVP Award since 2002 (13 consecutive awards as of 2014).

Nish is an industry acknowledged expert in the Microsoft technology stack. He authored
C++/CLI in Action for Manning Publications in 2005, and had previously co-authored
Extending MFC Applications with the .NET Framework for Addison Wesley in 2003. In addition, he has over 140 published technology articles on CodeProject.com and another 250+ blog articles on his
WordPress blog. Nish is vastly experienced in team management, mentoring teams, and directing all stages of software development.

Contact Nish : You can reach Nish on his google email id voidnish.

Website and Blog

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
TheHelenLee9-Feb-11 14:36
memberTheHelenLee9-Feb-11 14:36 
GeneralUsing Windows mobile 5 application - connect MySQL db Pin
infinit020-Apr-07 7:37
memberinfinit020-Apr-07 7:37 
QuestionUsing this with Windows Mobile 5? Pin
AlexEvans2-Apr-07 21:37
memberAlexEvans2-Apr-07 21:37 
AnswerRe: Using this with Windows Mobile 5? Pin
Nishant Sivakumar3-Apr-07 2:33
mvpNishant Sivakumar3-Apr-07 2:33 
GeneralRe: Using this with Windows Mobile 5? Pin
AlexEvans3-Apr-07 11:18
memberAlexEvans3-Apr-07 11:18 
GeneralGreat Example Pin
kstrat200128-Aug-06 9:43
memberkstrat200128-Aug-06 9:43 
GeneralRe: Great Example Pin
Nishant Sivakumar28-Aug-06 10:02
staffNishant Sivakumar28-Aug-06 10:02 
Generalif the *.mdb file is on other machine Pin
Ritesh Kumar Verma16-Jun-03 0:30
memberRitesh Kumar Verma16-Jun-03 0:30 
GeneralRun time exception Pin
Anonymous15-Jun-02 20:49
memberAnonymous15-Jun-02 20:49 
GeneralRe: Run time exception Pin
Nishant S19-Jul-02 2:27
subeditorNishant S19-Jul-02 2:27 
Questionhow do i pass the arguments to the s Pin
Anonymous12-Jun-02 6:32
memberAnonymous12-Jun-02 6:32 
AnswerRe: how do i pass the arguments to the s Pin
Nish - Native CPian12-Jun-02 14:15
memberNish - Native CPian12-Jun-02 14:15 
QuestionWhat is the advantage of this code? Pin
a reader2-Dec-01 10:34
membera reader2-Dec-01 10:34 
AnswerRe: What is the advantage of this code? Pin
Nish [BusterBoy]2-Dec-01 19:11
memberNish [BusterBoy]2-Dec-01 19:11 
GeneralUpdated for beta 2.0 Pin
Nish [BusterBoy]1-Dec-01 22:37
memberNish [BusterBoy]1-Dec-01 22:37 
GeneralADO namespace Pin
Anonymous81819-Nov-01 18:42
memberAnonymous81819-Nov-01 18:42 
GeneralRe: ADO namespace Pin
Nish [BusterBoy]1-Dec-01 22:38
memberNish [BusterBoy]1-Dec-01 22:38 
GeneralObsolete Pin
MN15-Oct-01 5:47
memberMN15-Oct-01 5:47 
GeneralRe: Obsolete Pin
Nish [BusterBoy]1-Dec-01 22:39
memberNish [BusterBoy]1-Dec-01 22:39 
Generalgood job Pin
Jingo11-Oct-01 1:24
memberJingo11-Oct-01 1:24 
GeneralRe: good job Pin
Nish [BusterBoy]11-Oct-01 2:57
memberNish [BusterBoy]11-Oct-01 2:57 
GeneralYaar, you are the best Pin
Amita Buch13-Oct-01 12:32
memberAmita Buch13-Oct-01 12:32 
GeneralException handling Pin
Jason Douglas11-Oct-01 1:10
memberJason Douglas11-Oct-01 1:10 
GeneralRe: Exception handling Pin
Nish [BusterBoy]11-Oct-01 3:00
memberNish [BusterBoy]11-Oct-01 3:00 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150901.1 | Last Updated 2 Dec 2001
Article Copyright 2001 by Nish Nishant
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid