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 directionwhile (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#

Share

About the Author

Nish Nishant is the Principal Software Architect/Consultant for Ganymede Software Solutions LLC, and is based out of Columbus, Ohio. He has over 17 years of software industry experience in various roles including Lead Software Architect, Principal Software Engineer, and Product Manager. Nish was a Microsoft Visual C++ MVP between 2002 and 2015.

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 : If you are interested in hiring Nish as a consultant, you can reach him via his google email id voidnish.

I'm developing an application to Windows Mobile 5, using Visual Studio 2005, and my ideia is to connect from that application to a remote MySQL database.
This article shows how to do it for .NET applications.
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1288&lngWId=10
My question is, it can be done for Windows Mobile as well? Because it don't seems to support OLE DB reference.
Thanks in advance,

how do i pass the arguments to s i tryed this maybe you could help me out i cant get it to accept input to the array to add to the DB im new to programming so maybe you could help me out?

using System.Data.OleDb;
using System;

class nish
{
public static void Main(string[] s)
{

Console.WriteLine("Enter the name please");
s[0] = Console.ReadLine();
Console.WriteLine("Enter the Age now please");
s[1] = Console.ReadLine();
//check and see if they have entered the two arguments
//if they have then proceed
if(s.Length==2)
{
//create the OleDb connection object
OleDbConnection conn=new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb");

//Open the OleDb connection
conn.Open();

//Declare a command object so we can issue SQL commands to the connection
OleDbCommand cmd;

//construct the command object passing the SQL and the connection object as parameters
cmd=new OleDbCommand(sql,conn);

//Now execute the SQL
cmd.ExecuteNonQuery();

//Close the connection
conn.Close();
}
else //if they have entered less than 2 arguments or more than 2 arguments
//show the following error messageam
{
Console.WriteLine("Wrong number of arguments!");
Console.WriteLine("Use :- dbinsert [name] [age]");
}