Click here to Skip to main content
15,867,686 members
Articles / Desktop Programming / ATL
Article

The Dynamic Database Class Based on ATL/OLE DB

Rate me:
Please Sign up or sign in to vote.
4.96/5 (19 votes)
23 Apr 20022 min read 124K   4.4K   54   21
A class to dynamically manipulate databases data using ATL/OLE DB technology

Sample Image - CSQLQuery.jpg

Introduction

Imagine the advantages of being able to dynamically access a database from the client-side. You could dynamically query a database from client-side, or dynamically populate a drop-down select list, or dynamically update certain fields of a table just as a few possibilities. 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. For do that 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

En 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);
  }
}

An 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 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. I believe, to connect to another type of database, for example to Oracle, it is enough to change Function Connect.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralI got Good Luck! Pin
ekklesia2-Dec-08 20:34
ekklesia2-Dec-08 20:34 
QuestionHow can i use stored procedure? Pin
jkYoo1-Oct-08 22:42
jkYoo1-Oct-08 22:42 
AnswerRe: How can i use stored procedure? Pin
ekklesia2-Dec-08 20:38
ekklesia2-Dec-08 20:38 
QuestionConecting to MSAccess??? Pin
mapharo28-Apr-08 6:01
mapharo28-Apr-08 6:01 
AnswerRe: Conecting to MSAccess??? Pin
victorsow12-Jun-08 23:07
victorsow12-Jun-08 23:07 
GeneralRe: Conecting to MSAccess??? Pin
mapharo13-Jun-08 5:23
mapharo13-Jun-08 5:23 
GeneralWindows CE Support Pin
Frontier17-Jun-06 22:23
Frontier17-Jun-06 22:23 
Hello,

Do you plan on supporting Windows CE and SQL Server Mobile?
I'm doing some testing regarding the ATL OLE DB use of this library, but there are some incompatibilities due to it's design.

Many thanks in advance.

---
Best Regards,
Manos S. Pappás
GeneralRe: Windows CE Support Pin
James, Lu Zuheng25-Feb-07 21:33
James, Lu Zuheng25-Feb-07 21:33 
GeneralProblem while getting the error msg! Pin
xifan12-Sep-05 22:43
xifan12-Sep-05 22:43 
Generalvarchar size error while selecting Pin
BAIJUMAX23-Jun-04 20:17
professionalBAIJUMAX23-Jun-04 20:17 
Generalthis cannot oracle Pin
Anonymous9-May-04 21:30
Anonymous9-May-04 21:30 
GeneralISequentialStream Pin
Urban Olars12-Mar-04 10:49
Urban Olars12-Mar-04 10:49 
GeneralRe: ISequentialStream Pin
CodeStylite8-Apr-04 21:50
CodeStylite8-Apr-04 21:50 
GeneralRe: ISequentialStream [modified] Pin
ekklesia18-Aug-09 8:31
ekklesia18-Aug-09 8:31 
GeneralIt doesn't work under win9x! Pin
bigscholar27-Sep-03 21:57
bigscholar27-Sep-03 21:57 
GeneralPlease don't abuse operator &gt; ! Pin
Don Clugston24-Sep-03 13:57
Don Clugston24-Sep-03 13:57 
QuestionHow to built this class to a .dll? Pin
fox_hawk10-Jun-03 21:19
fox_hawk10-Jun-03 21:19 
GeneralRetrieving the root table of a column Pin
Shiva N. Kumar1-May-03 10:23
Shiva N. Kumar1-May-03 10:23 
Generalthanks for your demo. Pin
xds20008-Apr-03 21:59
xds20008-Apr-03 21:59 
GeneralGood work Pin
Denis_B8-Apr-03 21:29
Denis_B8-Apr-03 21:29 
GeneralJust What I Needed Pin
Ed Gadziemski24-Apr-02 5:31
professionalEd Gadziemski24-Apr-02 5:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.