Working with ADO and List View in MFC

  ADO stands for ActiveX Data Object.ADO provides an object-oriented programming interface for accessing a data source using the OLE-DB data provider. It is the successor to DAO and RDO object models and combines the best features DAO and RDO.

   This is where ADO really shines. ADO is a high level interface to OLE-DB that is based on COM interfaces. Thus any application that supports COM can implement ADO.

   In the ADO model, we'll be using three main types of objects:

• Connection
• Command
• Recordset

   The Connection object sets up a connection to the data source. First, the data source name, its location, user ID, password etc is stored in a ConnectionString object, which is passed to the Connection object to establish a 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.

ODBC Configuration:

  ODBC connections are configured in the System --> Control Panel. ODBC connections can be made against any data source for which an ODBC driver has been installed. Visual C++ 6.0 or later ships drivers for text files, Access, FoxPro, Paradox, dBase, Excel, SQL Server, and Oracle. When you create an ODBC connection, it automatically receives a data source name (DSN). The DSN is subsequently used to identify connections in data controls, such as ADO data control and RDO RemoteData control.

To configure an ODBC data source

1. Click Start, click Settings, and then click Control Panel.
2. In Control Panel, select 32bit ODBC (Windows 95 or 98) or ODBC (Windows NT or 200).
3. Select the User DSN or System DSN tab. User DSN lets you create user-specific data source names and System DSN lets you create data sources available to all users.
4. Click Add to display a list of locally installed ODBC drivers.
5. Select the driver corresponding to the type of indexed sequential access method (ISAM) or database you want to connect to and click Finish.
6. Follow the instructions specific to the driver. After closing, the DSN is now available for use.

When generating a DSN for some ODBC driver types, you need to know the location of the actual file. For example when creating an Access DSN, you need to know the location of the .mdb file. Also, you should have a valid user name and password. For example, the system username for most Access systems is admin.

Creating the Project and Application

1. To create the application, follow these steps:

In MFC AppWizard Step 1 of 1, choose Single Document (SDI).

    a)  Accept all other defaults in Steps 2, 3, 4.

    b)  In MFC AppWizard Step 5, refuse the Comments.

    c)  In MFC AppWizard Step 6, base your CAdoConn on the CListView class.

    d)  The New Project Information presents a preamble to you to examine and
accept or modify.

Press Enter to accept.

Add the following code in AdoConnView.h:  

        #import <msado15.dll> rename("EOF", "ADOEOF") using namespace ADODB;

   This step will help the Visual C++ compiler to understand the ADO objects defined in the type library, MSADO15.DLL. The rename_namespace function renames the namespace into which the DLL has been imported to the specified name. The rename option has been used to rename the EOF keyword to EndOfFile, because EOF is already defined in the standard header files.

Initialize the COM library :

CoInitialize(NULL);

To connect to a data source, first declare a Connection object pointer by passing the ID of the coclass.

    _ConnectionPtr Connect (__uuidof(Connection));  

Now call the Open function to establish a connection to the data source.

Connect->Open(_T("Provider=SQLOLEDB.1; Data Source=SQLServer; Catalog=Customer"),_T("user1"),_T(""),adOpenUnspecified);

   The Open function takes four parameters. The first one is the connection string, which contains the name of the provider and name of Oracle/ MSAccess Database for connection. The second and third parameters are the user name and the password to establish the connection. The fourth parameter is the type of cursor to be used. The _T macro ensures UNICODE compatibility of the strings.

   To pass the SQL command, create a command object pointer by passing the CLSID of the Command object.

    _CommandPtr Source (__uuidof(Command));  

Create a Recordset object and specify the Command object as the source of the records as follows:

    _RecordsetPtr Rs(__uuidof(Recordset));

Now open the Recordset using the Open method of the Recordset object as :

    Rs->Open(Source,Connect,adOpenDynamic,adLockOptimistic,adCmdText);

The Open method takes five parameters. The first and the second parameter is the data source name and the active connection to use respectively. The third parameter specifies the cursor type to use followed by the locking parameter. The fifth parameter specifies how the database should evaluate the command being sent.  

Declare these variables in CAdoConnView.cpp:

/* Global Variables */

static ChkFlag=0;
static ChkRun=0;
static row=0;
static col=0;

OnConnect Function:  This function is used for SELECT query and gets the records in Recordset object .

void CADOConnView::OnConnect()
{

 _ConnectionPtr ptr;
 _RecordsetPtr   Rs1 = NULL;
 Fields *flds;
 Field *fld;
 BSTR FldName;
 long FldCount=0, Columns=0;int i=0;
 CStringArray strFldNames;
 
 _variant_t vFieldValue[300];
   
 HRESULT hr;

 ChkFlag=0;

 USES_CONVERSION;
 CoInitialize(NULL);  // Initializes the COM Runtime

 try
 {
 hr=ptr.CreateInstance(__uuidof(Connection));
 
 if(SUCCEEDED(hr))
 {
  HRESULT  hr = S_OK;
    
  CString strTmp;
  CString strConnString=pSets.GetConnString();   // Get the Connection String
  CString strQuery=pQry.GetQuery();    // Get the Query

    _bstr_t Connect(strConnString);
  _bstr_t Source (strQuery );
  
  try
  {
  
   hr = Rs1.CreateInstance( __uuidof(Recordset ) );  // Get the RecordSet
   Rs1->Open( Source, Connect,adOpenDynamic,adLockReadOnly, -1 );// Open the Recordset
 
   flds=Rs1->GetFields();
   FldCount=flds->GetCount();
   Columns=FldCount;

   
   while(FldCount>0)
   {
   fld=flds->GetItem( COleVariant((long)i));
   fld->get_Name(&FldName);
   strTmp=W2A(FldName);               // Converts BSTR to String
   strFldNames.Add(strTmp);    // Get the Field Labels from the table
   i++;
   FldCount--;
   }
  
   if (!Rs1->GetADOEOF())
   {
    int tmp=0;
    _variant_t FldCaption[50];
               
   for(tmp=0;tmp<i;tmp++)
   {
    FldCaption[tmp].SetString(strFldNames.GetAt(tmp));
   }
   ListItems(Columns,FldCaption);        // Setting the Labels on the ListCtrl
   
   while (!Rs1->GetADOEOF())
            {
    for(tmp=0;tmp<i;tmp++)
    { 
     vFieldValue[tmp] = Rs1->GetCollect(FldCaption[tmp]);

                }
    ListItems(Columns,vFieldValue);   // Setting the Values on the ListCtrl
    tmp=0;
                Rs1->MoveNext();
            }
   }
  
  Rs1->Close();  // Closing the RecordSet
  Rs1 = NULL;
  }
  catch(_com_error e)
  {
   AfxMessageBox("Failed in Creating Record Set");
   Rs1->Close();  // Closing the RecordSet
   Rs1 = NULL;
  }

 }
 }
 catch(_com_error e)
 {

 }

CoUninitialize(); // Uninitialize COM Runtime
}

OnQuery Function: This function sets the Labels on the ListCtrl which are retrieved from Database.

void CADOConnView::OnQuery(CListCtrl &ListItem,long NoofItems, _variant_t *Items)
{
 USES_CONVERSION;
 LV_COLUMN lvc;
 ListItem.SetImageList(&m_ImageList,LVSIL_NORMAL);
 lvc.mask=LVCF_FMT|LVCF_WIDTH|LVCF_TEXT|LVCF_SUBITEM;
 ModifyStyle(LVS_TYPEMASK,LVS_REPORT,LVS_TYPEMASK);
 for(int i=0;i<NoofItems;i++)
 {
  lvc.iSubItem=i;
  lvc.pszText=W2A(Items[i].bstrVal);
  lvc.cx=colWidth[i];
  lvc.fmt=colFormat[i];
  ListItem.InsertColumn(i,&lvc);
 }
}

ListItems Function:This functions sets the Values on the ListCtrl

void CADOConnView::ListItems(long NoofItems, _variant_t *Items)
{
 int i=0,j=0;
 USES_CONVERSION;
 LV_ITEM lvi; 
 CListCtrl &ListCtrl=GetListCtrl();
 if(ChkFlag==0)
 { 
  if(ChkRun>0)
  {
   int nColumnCount = ListCtrl.GetHeaderCtrl()->GetItemCount();
   for (int i=0;i < nColumnCount;i++)
   {
    ListCtrl.DeleteColumn(0);
   }
   ListCtrl.DeleteAllItems();
  }

  OnQuery(ListCtrl,NoofItems,Items);
  ChkFlag++;
  ChkRun++;
 }
 else
 {
  lvi.mask=LVIF_TEXT|LVIF_IMAGE|LVIF_STATE;
  lvi.iItem=row;
  lvi.iSubItem=0;
  lvi.pszText=W2A(Variant2BSTR(Items[row]));
  lvi.iImage=row;
  lvi.stateMask=LVIS_STATEIMAGEMASK;
  lvi.state=INDEXTOSTATEIMAGEMASK(row);
  ListCtrl.InsertItem(&lvi);
 
  for(col=1;col<NoofItems;col++)
   ListCtrl.SetItemText(row,col,W2A(Variant2BSTR(Items[col])));
  }
}

AllQueries Function:This functions used for Inserting,modifying and deleting records

void CADOConnView::AllQueries()
{
 CoInitialize(NULL);
 _ConnectionPtr ptr;
 _RecordsetPtr   Rs = NULL;
 CStringArray strFldNames;
 HRESULT hr;
 USES_CONVERSION;
 ChkFlag=0;
 
 try
 {
  hr=ptr.CreateInstance(__uuidof(Connection));

  if(SUCCEEDED(hr))
  {
   HRESULT  hr = S_OK;
   CString strTmp;
   CString strConnString=pSets.GetConnString();   // Get the Connection String
   CString strQuery=pQry.GetQuery();      // Getting the Query  
     _bstr_t Connect(strConnString);
   _bstr_t Source (strQuery );
  
   try
   {
    hr = Rs.CreateInstance( __uuidof(Recordset ) );
    Rs->Open( Source, Connect,adOpenDynamic,adLockReadOnly, -1 );
    AfxMessageBox("Query Successfully Executed");
   
   }
   catch(_com_error e)
   {
    AfxMessageBox("Error while Executing the Query");
    Rs->Close();
   }
   hr=Rs->Close();
  }
 }
 catch(_com_error e)
 {
 }
}

Variant2BSTR Function : This function converts the variant to BSTR.

BSTR CADOConnView::Variant2BSTR(_variant_t Source)
{
 USES_CONVERSION;

 if(Source.vt==VT_BSTR)
  VariantChangeType(&Source, &Source, 0, VT_BSTR);
 
 if(Source.vt==VT_DECIMAL)
  VariantChangeType(&Source, &Source, 0, VT_BSTR);
 
 if(Source.vt==VT_DATE)
  VariantChangeType(&Source, &Source, 0, VT_BSTR);
 
 if(Source.vt==VT_R4)
  VariantChangeType(&Source, &Source, 0, VT_BSTR);
 if(Source.vt==VT_BOOL)
  VariantChangeType(&Source, &Source, 0, VT_BSTR);
 
 if(Source.vt==VT_I1)
  VariantChangeType(&Source, &Source, 0, VT_BSTR);

 if(Source.vt==VT_I4)
  VariantChangeType(&Source, &Source, 0, VT_BSTR);

 if(Source.vt==VT_CY)
  VariantChangeType(&Source, &Source, 0, VT_BSTR);

 if(Source.vt==VT_NULL)
  Source.bstrVal=L"";
 
 return Source.bstrVal;
}

Steps to run the application: This application is mainly concentrated on ADO and CListCtrl class.

1. Create a DSN name through ODBC.

2. Using Settings dialog save the DSN name , username and password.

3. Using Query dialog and save the Query to be executed.

4. Execute the query.