The Dynamic Database Class Based on ATL/OLE DB Technology

Sample Image

Environment: VC++ 6.0, NT 4.0,Win2000, Win95/98

Introduction

Imagine the advantages of being able to dynamically access a database from the client-side. As a few possibilities, you could dynamically query a database from client-side, dynamically populate a drop-down select list, or dynamically update certain fields of a table. In fact, you can do this, with the help of the CSQLQuery object.

This article describes the dynamic class CSQLQuery which based on ATL/OLE DB library and offer several samples.

Submitting an SQL statement and retrieving data

To submit an SQL statement, you simply instantiate a CSQLQuery object and then call the ExecuteSQL member function passing the SQL string as argument.

CSQLQuery query(GetSession());
query.ExecuteSQL("SELECT MAX(UnitPrice) FROM Products");

Retrieving data is easy like 1,2,3. You just need to define variable and get the data by using overloaded operator like >>.

double dblUnitPrice = 0;  query
>>  dblUnitPrice;

Note: in this case you have to take care about data mapping. For example, SQL Server data like int should to map in C++ long type.

To retrieve any data to string and avoid mapping you can use operator > like here:

CString str; query > str.

In this case you don't have to take care about data mapping. All types of data will be map to the CString object automatically.

You can also use overloaded operators Like << to pass a parameters

double dblMaxPrice = 2.8;
CSQLQuery query(GetSession());
query << "SELECT ProductName,UnitPrice FROM Products \
WHERE UnitPrice >  " << dblMaxPrice;
query.ExecuteSQL();

The execution of stored procedure with parameters may looks like here:

double dblPar1 = 2.8, dblPar2 = 1.8;
CSQLQuery query(GetSession());
query << "EXECUTE sp_MyProcedure " << dblPar1 << " , " << dblPar2;
query.ExecuteSQL();

The SQL update statement will not complicated as well:

CSQLQuery query(GetSession());
query << "UPDATE Orders SET  ShipName = 'MyName' WHERE EmployeeID = 5";
query.ExecuteSQL();

An Example Using the Object

An example of list box population

In this example the function PoulateListBox is making population depends from the value of argument dblMaxPrice. The list box will populated with data having UnitPrice more than dblMaxPrice. You can extend that idea for any condition to retrieve data.

void PoulateListBox(CListBox& box,double dblMaxPrice)
{
  box.ResetContent();
  CSQLQuery query(GetSession());
  query << "SELECT ProductID,ProductName FROM \
Products WHERE UnitPrice >  " << dblMaxPrice;
  if(!query.ExecuteSQL())
    return;
  while(!query.eof())
  {
    LONG lProductID = 0; CString strProductName; 
    query >> 
    lProductID  >>  strProductName;
    int idx = box.AddString(strProductName);
    box.SetItemData(idx,lProductID);
  }
}

En example of CListCtrl population

This sample shows how you can populate CListCtrl with columns and data.

void CQueryView::ShowQuery(const CString strTableName) 
{
  // Clear List control
  m_listCtrl.DeleteAllItems();
  while(m_listCtrl.DeleteColumn(0)) {}

  CSQLQuery query(GetSession());
  query << " SELECT TOP 100 * FROM " << strTableName;
  if(!query.ExecuteSQL())
    return;

  // Show columns
  int cols = query.GetColumnsCount();
  for( int nCol = 0; nCol < cols; nCol++)
  {
    CString strColName = query.GetColumnName(nCol);
    m_listCtrl.InsertColumn(nCol,strColName,LVCFMT_LEFT,80);
  }

  // Show data
  int nItem = 0;
  while(!query.eof())
  {
    CString str; query > str;
    m_listCtrl.InsertItem(nItem,str);
    for( int nSubItem = 1; nSubItem < cols; nSubItem++)
    {
      CString str; query > str;
      m_listCtrl.SetItemText(nItem,nSubItem,str);
    }
    nItem++;
  }
}

Is this not easy?

About GetSession()

The CSQLQuery object is using pointer to the CSession class. A CSession object represents a single database access session defined in ATL library. To create a new CSession for a CDataSource simply instantiate an objects:

CDataSource m_datasource;
CSession    m_session;

And create one using the following function:

bool Connect(ATL::CDataSource* pDataSource,
             ATL::CSession* pSession) {
   ASSERT(pDataSource);
   CComBSTR bstrServer(m_strServerName);
   CComBSTR bstrUser(m_strLoginName);
   CComBSTR   bstrPassword(m_strPassword);
   CComBSTR bstrDatabase(m_strDatabaseName);
   if(pSession && pSession->m_spOpenRowset != NULL)
       pSession->m_spOpenRowset.Release();

   CDBPropSet dbinit(DBPROPSET_DBINIT);
   dbinit.AddProperty(DBPROP_AUTH_PASSWORD, bstrPassword);
   dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
   dbinit.AddProperty(DBPROP_AUTH_USERID, bstrUser);
   dbinit.AddProperty(DBPROP_INIT_CATALOG, bstrDatabase);
   dbinit.AddProperty(DBPROP_INIT_DATASOURCE, bstrServer);
   dbinit.AddProperty(DBPROP_INIT_LCID, (long)1049);
   dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
   if(FAILED(pDataSource->Open(_T("SQLOLEDB.1"), &dbinit))){
      pDataSource->Close(); return
      false;
   }
   else
   { 
      if (pSession &&pSession->Open(*pDataSource) != S_OK)
      {
        return false;
      }
   }
   return true;
}
So, GetSession() will be like here:
CSession* GetSession()
{
  return &m_session;
}

Notes about demo

The demo project of this article was tested on SQL Server 7.0/8.0 and database Northwind.To connect to another type of database such as Oracle, I believe you should only need to change Function Connect.

Downloads



Comments

  • WJPalF Um JS PgF HPEN Eu

    Posted by RYTuKTcKKt on 02/02/2013 08:26am

    buy soma online do soma bras fit - soma no prescription overnight delivery

    Reply
  • arabic text problems

    Posted by Legacy on 10/14/2003 12:00am

    Originally posted by: sinan swedani

    how i can get arabic text as column header, and when i query arabic text i got no results at all

    Reply
  • how to get the content of image type field ?

    Posted by Legacy on 01/01/2003 12:00am

    Originally posted by: zz

    my database server is sql Server,and my table has a OLE field(such as .Doc,.Xls,.Jpg file be embbed in the field),
    how to get the content of field and display on IE?

    Reply
  • very good, but I have one problem

    Posted by Legacy on 10/07/2002 12:00am

    Originally posted by: Alex

    Thanks for code. It helps me with my troubles.

    But I have one problem with memo data type in MS-Access's file (*.mdb). If this field is empty it's ok but if there is some data function ToVariant couldn't check type of column. And reading stops with any error.

    Have you any idea? Thanks, Alex.

    Reply
  • Very nice code

    Posted by Legacy on 10/05/2002 12:00am

    Originally posted by: Kalyan

    I wonder why Microsoft did not include such a class in MFC. Without such a class it is very tedious to do simple things too with Databases. Keep up the good work. Its very nice...

    Reply
  • Should apply this bug fix if you are running Unicode Mode..

    Posted by Legacy on 09/22/2002 12:00am

    Originally posted by: Jianwei Sun

    http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q238539&LN=EN-US&SD=gn&FR=0 has a bug fix for
    dbtype DBTYPE_WSTR to solve the insufficient data storge for unicode string problem..

    Overall,this is very cool code..

    Jianwei

    Reply
  • Found some bugs

    Posted by Legacy on 08/08/2002 12:00am

    Originally posted by: Gerald Dalley

    I attempted to execute the following select statement using VC7, WinXPPro hitting a SQLServer DB:
    
    SELECT COUNT(*) FROM TemplateDB..Templates
    I was returned a negative number due to incorrect usage of the internal VARIANTs. Here are the changes that I made to fix the problem:

    Line 121: Change "iVal" to "lVal"
    Lines 230-234: replace those lines with:
    case VT_I2:
    value.Format("%d",m_value.iVal);
    break;
    case VT_I4:
    value.Format("%d",m_value.lVal);
    break;

    As far as I can tell, VT_I2 is stored as a SHORT and VT_I4 should be stored as a LONG. I don't know if this is OS dependent or not. Hope this helps

    Reply
  • very cool

    Posted by Legacy on 05/08/2002 12:00am

    Originally posted by: ramakrishna

    very nice article with elegant code.
    keep up the good work.

    Reply
  • good

    Posted by Legacy on 04/20/2002 12:00am

    Originally posted by: 吴波

    it's very good

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • VMware vCloud® Government Service provided by Carpathia® is an enterprise-class hybrid cloud service that delivers the tried and tested VMware capabilities widely used by government organizations today, with the added security and compliance assurance of FedRAMP authorization. The hybrid cloud is becoming more and more prevalent – in fact, nearly three-fourths of large enterprises expect to have hybrid deployments by 2015, according to a recent Gartner analyst report. Learn about the benefits of …

Most Popular Programming Stories

More for Developers

RSS Feeds