The first thing that happens on running our DBManager application is that this fragment of code gets executed at the end of DBManagerDlg's OnInitDialog():
.
.
.
HRESULT hResult = CoInitialize (NULL);
if (FAILED (hResult))
{
Log (hResult, "Initialize COM");
return TRUE;
}
ListHostages ();
ListAccounts ();
return TRUE; // return TRUE unless you set the focus to a control
}
Looking at DBManagerDlg.h, we need the following private methods for our code:
// DBManagerDlg.h : header file
...
#include <comdef.h>
#define ADO_SMARTPTR_TYPEDEF(x) \
typedef _com_ptr_t<_com_IIID<x, &IID_I##x> > x##Ptr;
ADO_SMARTPTR_TYPEDEF(ADOConnection);
ADO_SMARTPTR_TYPEDEF(ADORecordset);
ADO_SMARTPTR_TYPEDEF(ADOFields);
ADO_SMARTPTR_TYPEDEF(ADOField);
ADO_SMARTPTR_TYPEDEF(ADOErrors);
ADO_SMARTPTR_TYPEDEF(ADOError);
/////////////////////////////////////////////////////////////////////////
// CDBManagerDlg dialog
...
// Implementation
private:
ADOConnectionPtr OpenDatabase (CString csDatabase);
ADORecordsetPtr OpenRecordset (ADOConnection *pConnection, CString csTable);
ADORecordsetPtr FindRecordset (ADOConnection *pConnection, CString csTable, CString csField, CString csValue);
void ListHostages ();
void ListAccounts ();
void Log (HRESULT hResult, CString csLocation);
void Log (HRESULT hResult, CString csLocation, ADOConnection *pConnection);
Let’s start by taking a look at the member function, ListHostages(). It uses the OpenDatabase() and OpenRecordset() functions, to which we'll come shortly.
void CDBManagerDlg::ListHostages ()
{
CListBox *pList = static_cast<CListBox *> (GetDlgItem (IDC_HOSTAGES));
pList->ResetContent ();
ADOConnectionPtr pConnection;
pConnection = OpenDatabase ("kidnap1");
if (pConnection == NULL)
return;
ADORecordsetPtr pRecordset;
pRecordset = OpenRecordset (pConnection, "hostages");
if (pRecordset == NULL)
return;
// Check for end of file
VARIANT_BOOL bEOF;
HRESULT hResult = pRecordset->get_EOF (&bEOF);// Scroll through all records in recordset
while ((SUCCEEDED (hResult)) && (bEOF == VARIANT_FALSE))
{
ADOFieldsPtr pFields;
hResult = pRecordset->get_Fields (&pFields);
long count;
hResult = pFields->get_Count (&count);
CString csHostage = "";
CString csRansom = "";
CString csState = "";
// Search through fields for the data we need
for (long field = 0; field < count; field++)
{
ADOFieldPtr pField;
hResult = pFields->get_Item (COleVariant (field), &pField);
BSTR bstrName;
hResult = pField->get_Name (&bstrName);
CString csName = bstrName;
SysFreeString (bstrName);
_variant_t vValue;
hResult = pField->get_Value (&vValue);
CString csValue = "";
long lValue;
switch (vValue.vt)
{
case VT_BSTR:
csValue = V_BSTR (&vValue);
break;
case VT_I4:
lValue = V_I4 (&vValue);
break;
case VT_BOOL:
if (vValue.boolVal == VARIANT_TRUE)
csValue = "free";
else
csValue = "held";
}
if (csName == "name")
csHostage = csValue.Left (20);
else if (csName == "ransom")
csRansom.Format ("%ld", lValue);
else if (csName == "free")
csState = csValue;
}CString csData;
csData.Format ("%s %s (%s)", static_cast<LPCSTR> (csHostage),
static_cast<LPCSTR> (csRansom),
static_cast<LPCSTR> (csState));
pList->AddString (csData);
// Move on to the next record
pRecordset->MoveNext ();
pRecordset->get_EOF (&bEOF);
}
}
This opens up a connection to our kidnap1 database (using the OpenDatabase() method, which we’ll see shortly), opens the hostages recordset, and simply scrolls through all the records that it finds there, formatting a display string containing the data from the fields in the record. Notice that, once we’ve got a connection to the database, one call to OpenRecordset() (which we’ll also see soon) is all we need to get hold of a recordset containing all the records in the table that we want.
Here’s the code to open a connection to a database passed in as a parameter:
ADOConnectionPtr CDBManagerDlg::OpenDatabase(CString csDatabase)
{
// Create connection object
ADOConnectionPtr pConnection;
HRESULT hResult = CoCreateInstance (CLSID_CADOConnection, NULL,
CLSCTX_INPROC_SERVER,
IID_IADOConnection,
reinterpret_cast<void**>(&pConnection));
if (FAILED (hResult))
{
Log (hResult, "Create connection");
return NULL;
}
// Format the connection string
CString csConnection;
csConnection.Format("FILEDSN=WPSamples;Database=%s;UID=sa;PWD=;",
static_cast<LPCSTR> (csDatabase));
_bstr_t bstrConnection = csConnection.AllocSysString ();
// Open the database
// Note that MTS 2.0 and OLEDB SDK include files, adoid.h and
// adoint.h, and their corresponding library, are a more modern
// version of ADO than that in Visual C++ 5.
// We've used the MTS version of ADOConnection's Open() method, with
// four parameters
hResult = pConnection->Open(bstrConnection, NULL, NULL, NULL);
if (FAILED (hResult)) {
Log (hResult, "Open connection", pConnection);
return NULL;
}
return pConnection;
}
In the string that gets passed to the Open() method, we’ve specified that our FileDSN is WPSamples (remember setting that up in the ODBC configuration earlier on in the chapter?). The database is set to whatever we passed in (kidnap1 or kidnap2), and the UID for SQL Server is set to sa, which is set up by default as the SQL Server administrator UID (so we know it’ll work — I’m not too bothered about SQL Server security here).
Here’s how we open up our recordset:
ADORecordsetPtr CDBManagerDlg::OpenRecordset(ADOConnection *pConnection, CString csTable)
{
// Create a recordset object
ADORecordsetPtr pRecordset;
HRESULT hResult = CoCreateInstance(CLSID_CADORecordset, NULL,
CLSCTX_INPROC_SERVER,
IID_IADORecordset,
reinterpret_cast<void**>(&pRecordset));
if (FAILED(hResult))
{
Log(hResult, "Create recordset");
return NULL;
}
_variant_t vTable(csTable);
_variant_t vConnection(pConnection);
hResult = pRecordset->Open(vTable, vConnection, adOpenKeyset,
adLockPessimistic, adCmdTable);
if (FAILED (hResult))
{
Log (hResult, "Open recordset", pConnection);
return NULL;
}
return pRecordset;
}
Notice here that we’re not actually issuing a command to the database to populate our recordset. Instead, we instantiate a recordset independently of the connection, and then invoke its Open() method to attach it to the connection, specifying the required table (that’s what the adCmdTable parameter is saying — later on we’ll see another way of opening a recordset, by issuing a command).
We can now use the recordset that we’ve just defined to manipulate the records in the table. We’ve already seen how we can scroll through them to list them, in the ListHostages() function; but we can also update the table, for example, by adding a new record. Let’s take a look at how that happens.
Here’s the code that gets triggered when the user hits the Add button in the hostage section of the dialog:
void CDBManagerDlg::OnAddHostage()
{
// Get data from dialog fields
CString csHostage;
CEdit *pHostage = static_cast<CEdit *> (GetDlgItem (IDC_HOSTAGE));
pHostage->GetWindowText (csHostage);
CString csRansom;
CEdit *pRansom = static_cast<CEdit *> (GetDlgItem (IDC_RANSOM));
pRansom->GetWindowText (csRansom);
long lRansom = atol (static_cast<LPCSTR> (csRansom));
CString csState;
CComboBox *pState = static_cast<CComboBox *> (GetDlgItem (IDC_STATE));
pState->GetWindowText (csState);
VARIANT_BOOL bFree = VARIANT_FALSE;
if (csState == "free")
bFree = VARIANT_TRUE;
// Open up recordset
ADOConnectionPtr pConnection;
pConnection = OpenDatabase ("kidnap1");
if (pConnection == NULL)
return;
ADORecordsetPtr pRecordset;
pRecordset = OpenRecordset (pConnection, "hostages");
if (pRecordset == NULL)
return;
// Set up variant array containing details of new hostage
COleSafeArray arrField;
arrField.CreateOneDim(VT_VARIANT, 3);
COleVariant varField;
long rgIndices[1];
varField = L"name"
rgIndices[0] = 0;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varField));
varField = L"ransom"
rgIndices[0] = 1;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varField));
varField = L"free"
rgIndices[0] = 2;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varField));
COleSafeArray arrValue;
arrValue.CreateOneDim(VT_VARIANT, 3);
COleVariant varValue;
varValue = csHostage.AllocSysString();
rgIndices[0] = 0;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varValue));
varValue = lRansom;
rgIndices[0] = 1;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varValue));
varValue = bFree;
rgIndices[0] = 2;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varValue));
// Add new record to recordset
HRESULT hResult = pRecordset->AddNew (arrField, arrValue);
if (FAILED (hResult))
Log (hResult, "Add new record", pConnection);
}
The key call in this code segment is the invocation of the AddNew() method on the recordset object. Notice, incidentally, that nowhere have we incorporated anything in the code that is specific to SQL Server; such is the power of ADO. Actually, there’s just one line of code in this application that is specific to SQL Server, and that’s coming up in a minute.
Here’s what happens when the user clicks on the Set button in the hostage section of the dialog:
void CDBManagerDlg::OnSetHostage()
{
// Get data from dialog fields
CString csHostage;
CEdit *pHostage = static_cast<CEdit *> (GetDlgItem (IDC_HOSTAGE));
pHostage->GetWindowText (csHostage);
CString csRansom;
CEdit *pRansom = static_cast<CEdit *> (GetDlgItem (IDC_RANSOM));
pRansom->GetWindowText (csRansom);
long lRansom = atol (static_cast<LPCSTR> (csRansom));
CString csState;
CComboBox *pState = static_cast<CComboBox *> (GetDlgItem (IDC_STATE));
pState->GetWindowText (csState);
VARIANT_BOOL bFree = VARIANT_FALSE;
if (csState == "free")
bFree = VARIANT_TRUE;
// Find recordset for this hostage
ADOConnectionPtr pConnection;
pConnection = OpenDatabase ("kidnap1");
if (pConnection == NULL)
return;
ADORecordsetPtr pRecordset;
pRecordset = FindRecordset (pConnection, "hostages", "name", csHostage);
if (pRecordset == NULL)
return;
// Set up variant array containing details of change to hostage
COleSafeArray arrField;
arrField.CreateOneDim(VT_VARIANT, 2);
COleVariant varField;
long rgIndices[2];
varField = L"ransom";
rgIndices[0] = 0;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varField));
varField = L"free";
rgIndices[0] = 1;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varField)); COleSafeArray arrValue;
arrField.CreateOneDim(VT_VARIANT, 2);
COleVariant varValue;
varField = lRansom;
rgIndices[0] = 0;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varField));
varField = bFree;
rgIndices[0] = 1;
arrField.PutElement(rgIndices, reinterpret_cast<void*>(&varField));
// Update record
HRESULT hResult = pRecordset->Update (vFields, vValues);
if (FAILED (hResult))
Log (hResult, "Update record", pConnection);
}
This time, we’re finding a recordset that relates to a specific individual record, so we call a different member function, FindRecordSet() (which we’ll see next). Once we’ve got the record, we update it using the Update() method on the recordset object. Here’s our FindRecordSet() function:
ADORecordsetPtr CDBManagerDlg::FindRecordset(ADOConnection *pConnection, CString csTable, CString csField, CString csValue)
{
ADORecordsetPtr pRecordset;
HRESULT hResult = CoCreateInstance (CLSID_CADORecordset, NULL,
CLSCTX_INPROC_SERVER,
IID_IADORecordset,
reinterpret_cast<void**>(&pRecordset));
if (FAILED (hResult))
{
Log (hResult, "Create recordset");
return NULL;
}
CString csCommand;
csCommand.Format ("SELECT * FROM %s WHERE %s = '%s'",
static_cast<LPCSTR> (csTable),
static_cast<LPCSTR> (csField),
static_cast<LPCSTR> (csValue));
_variant_t vCommand(csCommand);
_variant_t vConnection(pConnection);
hResult = pRecordset->Open (vCommand, vConnection, adOpenKeyset, adLockPessimistic, adCmdText);
if (FAILED (hResult))
{ Log (hResult, "Open recordset", pConnection);
return NULL;
}
return pRecordset;
}
Whoa there! SQL alert! This is the one provider-specific line that I mentioned earlier. Actually, this is a very simple piece of SQL, which fetches all the records for which the specified field has the specified value. Because the field we’ve specified (“name”) is a unique key, we’re guaranteed to get a maximum of one record. Notice that this time, we’re specifying open by command rather than open by table — that’s what the adCmdText parameter is doing.
I won’t bore you rigid by showing you the code for the rest of DBManager; we’ve covered as much ADO as we’re going to need for now, and it’s time to move on.
Having defined our resources, we now need to ask what our application logic is going to look like. This is where it gets interesting, and the answer is going to strike at the heart of our preconceptions about object-oriented programming. Let’s step back a bit, and think back to what we were taught when we first starting using objects.