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
OleDbConnection* conn = new OleDbConnection(
"PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb");
conn->Open();
OleDbCommand* cmd = new OleDbCommand(sqlstr,conn);
cmd->ExecuteNonQuery();
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
OleDbConnection* conn = new OleDbConnection(
"PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb");
conn->Open();
OleDbCommand* cmd = new OleDbCommand(sqlstr,conn);
OleDbDataReader* rdr = cmd->ExecuteReader();
while (rdr->Read())
{
. . .
. . .
}
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
Nish Nishant is a technology enthusiast from Columbus, Ohio. He has over 20 years of software industry experience in various roles including Chief Technology Officer, Senior Solution Architect, Lead Software Architect, Principal Software Engineer, and Engineering/Architecture Team Leader. Nish is a 14-time recipient of the Microsoft Visual C++ MVP Award.
Nish authored C++/CLI in Action for Manning Publications in 2005, and 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 experienced in technology leadership, solution architecture, software architecture, cloud development (AWS and Azure), REST services, software engineering best practices, CI/CD, mentoring, and directing all stages of software development.
Nish's Technology Blog :
voidnish.wordpress.com