Accessing data source using ADODB

• Download dll.zip - 32.3 KB
• Download UseDB.zip - 16.7 KB

Introduction

Hey guys,

I have been working from last few years on projects in which we have to access different databases. I found it so difficult for me to access database on the very first time when I was handed over the project, as I didn't have the knowledge of using ADO and COM. So I am writing this article for those who are new to ADO and accessing ADO using "msado15.dll" i.e by using COM.

I have created a Dll for doing this in a simplified manner. Basically I have wrappered some portion of "msado15.dll" into my classes for easy access.

Background

What is ADO?

Programming OLEDB in C++ is easy. However, for languages like Visual Basic, that do not support pointers and many C++ features, implementing OLEDB is difficult.ADO is a high level interface to OLEDB that is based on COM interfaces.

ADO Objects.

ADO model basically has 3 types of objects: -

1. Connection
2. Command
3. Recordset

The Connection object creates connection with data source using connection string, which differs for different databases (you can get connection strings at http://www.codeproject.com/database/connectionstrings.asp link),
which is passed to the Connection object to establish connection to the data source.

The Command object is used to execute SQL commands, queries and stored procedures. When a query is executed, it returns results that are stored in the Recordset object. Data in a recordset can be manipulated
and then updated to the database.

Using the code

Using this dll you can access the data source in an easy way.

There are 3 basic steps of accessing data source using this dll that are as follows: -

• connect to the data source
• execute the SQL commands
• return the Recordset object

There are 2 independent classes: -

• Conn_ADO class is basically use to connect (you can connect with 3 kinds of databases oracle/MS Access/MS SQLServer), disconnect, fetching rowset, closing rowset, and execute SQL (for executing SQLs that does not returns rowset like insert, update, delete etc.)
• ADO_ROWSET class can be use to access the rowset, retrive the values for perticular field usnig the functions like Get*, and traverse the rowset using Move* like functions.

Below are some steps using which you can use this dll: -

• Connect to the data source

// HRESULT hResult = objADO.ConnectDatabase(m_cstrDSN.AllocSysString()

// ,m_cstrUserName.AllocSysString(),m_cstrPass.AllocSysString(),Conn_ADO::connOracle);

// for connecting to Oracle database

// if(hResult != 0)

// {

// AfxMessageBox("Database not connected");

// }

// else

// {

// AfxMessageBox("Database Connected");

// }

You can also use connSQLServer, connAccess to connect to these databases.

• Executing the SQL commands and return the Recordset object

// sQuery = "SELECT * FROM TABLE_NAME";

// objAdo.FetchRowSet(sQuery.AllocSysString(),&ado_row_set);

// if(ado_row_set.IsBeginOfFile() || ado_row_set.IsEndOfFile())

// {

// objADO.CloseRecordSet(ado_row_set);

// AfxMessageBox("Table or view does not exist");

// return;

// }

// else

// {

// sCol_name = "COLUMN_NAME";

// ado_row_set.MoveToFirst();

// while(!ado_row_set.IsEndOfFile())

// {

// ArrColumns.Add(ado_row_set.GetStringValue(sCol_name.AllocSysString()));

// i++;

// ado_row_set.MoveToNext();

// }

// objADO.CloseRecordSet(ado_row_set);

// }

Here "objAdo" is an object to class Conn_ADO and "ado_row_set" is an onject to class

ADO_ROWSET.

You have to import msado15.dll in the project where you use this dll this is the limitation of this and you can fine msado15.dll at "c:\Program Files\Common Files\System\ADO\msado15.dll"

Points of Interest

While writing the dll I had to use COM for accessing data source, so if you go for traditional way of using COM you have to follow many steps like allocating memory etc. So for simplication I used the Smart pointers which did most of the work on there own like memory allocation and deallocation etc.

You can also learn from the code how to use smart pointers.

Another intresting point to note is that you can not only use this dll for MFC but can also use it in anyway i.e without using MFC because I have used the most basic types for input/output to/from function.

Suggestions

Suggestions are always welcome for any change that I forgot or improvement in this dll.