Lab 10: Implementing the UploadStockData Component

In this lab, you will provide the implementation for UploadStockData, the component you created for Lab 9. You will add code to implement the ConnectToDatabase(), Disconnect(), and UploadRecord() methods of the IUploadStockData interface. These methods use the ADO library to connect to the Stocks database. To refresh your memory about ADO, refer back to Lesson 3 of Chapter 7.

In this Lab, you will also write code to implement the Upload command on the Data menu of the STUpload application. The user will select this command to upload the data in the currently loaded document to the Stocks database.

This Lab assumes that you have installed SQL Server and have set up the Stocks database as directed in the "Getting Started" section of the introduction.

Creating a Data Link File

The UploadStockData component creates an ADO Connection object to connect to the Stocks database. You provide the Connection object with information about which provider, database, and security settings to use by specifying the details in a connection string. For example, the connection string used by the ADO data control in my version of the STUpload application is as follows:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security  Info=False;Initial Catalog=Stocks;Data Source=(local)

When distributing an application, it is not advisable to hard-code connection information into the application source code because this might mean that changes to the database and network configuration will require you to recompile and redistribute your application. To avoid problems of this kind, you can specify the connection information in a special disk file known as a data link file. A data link file has a .udl extension. When OLE DB is installed on your computer, you can configure the data link file using a simple user interface. After the data link file has been configured, you simply set the connection string to refer to the file as follows:

File Name=C:\DataLinks\STLink.udl

The configuration in the data link file can easily be redistributed or reconfigured on the client computer if the database or network configuration changes.

To create a data link file

1. Using Windows Explorer, create a new folder beneath the root of your hard drive. Name the folder DataLinks.
2. Click the DataLinks folder in the left pane of Windows Explorer. Right-click in the right pane and on the New menu, click Microsoft Data Link.
3. Rename the new data link file STLink.udl.
4. Double-click the STLink.udl file to edit the configuration information.
5. On the Provider page of the Data Link Properties dialog box, click Microsoft OLE DB Provider for SQL Server.
6. On the Connection page (shown in Figure 10.4), select your local computer name as the Server name. Click Use Windows NT Integrated security, or enter a SQL Server account name and password as appropriate.



Figure 10.4 Editing the data link file

7. Click the Stocks database on the server. Test your connection by clicking Test Connection.
8. Click OK to close the message box, and OK again to save the connection information to the data link file.

Implementing the IUploadStockData Interface Methods

In this section of the lab, you will return to the STLoadData project and implement the ConnectToDatabase(), Disconnect(), and UploadRecord() methods of the IUploadStockData interface exposed by the UploadStockData component. In the course of completing these exercises, you will learn how a client can use the COM interfaces supplied by ADO to work with an OLE DB data provider.

The first step is to import the ADO type library to make the GUID definitions available to the project, and to generate smart pointers for the ADO interfaces.

To import the ADO type library

1. Open the STUpload.dsw workspace file in Visual Studio. Make sure that the full Build toolbar is displayed and then click the STLoadData project in the drop-down list.
2. In FileView, expand the Header Files folder under the STLoadData Files item. Double-click the StdAfx.h file.
3. At the end of the file, just before the //{{AFX_INSERT_LOCATION}} comment, add the following line:

#import "C:\Program Files\Common Files\System\ado\msado15.dll" \     no_namespace rename("EOF", "adoEOF")

The path to the msado15.dll file might be different on your computer. Check that the version in your code points to the correct location. Note, also, that in the example just given, a continuation character \ is used to break the statement over two lines.

4. Save and close the StdAfx.h file.
5. In FileView, right-click the StdAfx.cpp file in the Source Files folder. On the shortcut menu, click Compile StdAfx.cpp.

The compiler processes the #import statement in the StdAfx.h file and creates the msado15.tlh and msado15.tli files in your Debug folder. These files contain the GUID definitions and smart pointer declarations for the ADO interfaces, which are now available for use in the STLoadData project.

The UploadStockData component will connect to the database through a single ADO Connection object, which will be used by successive calls to the UploadRecord() method. This Connection object will be implemented as a member variable of the component class and will be opened and closed by the ConnectToDatabase() and Disconnect() methods.

To implement the Connection object

1. Add the following protected data member to the definition of the CUploadStockData class in the UploadStockData.h file:

_ConnectionPtr m_pConnection;

2. In the same file, add the following line to the CUploadStockData class constructor:

m_pConnection = NULL;

3. In the UploadStockData.cpp file, locate the CUploadStockData:: ConnectToDatabase() function. Add code to the function so that it appears as follows:

STDMETHODIMP CUploadStockData::ConnectToDatabase() {     // Test to see if we're connected already     if(m_pConnection) return S_OK;     HRESULT hr = m_pConnection.CreateInstance(__uuidof(Connection));     if(FAILED(hr)) return hr;     hr = m_pConnection->Open(L"File Name=C:\\STLink.UDL",          L"", L"", -1);     if(FAILED(hr)) return hr;     return S_OK; }

Remember that the m_pConnection variable is a _com_ptr_t smart pointer type that points to the interface exposed by the ADO Connection object.

4. Locate the CUploadStockData::Disconnect() function. Add code to the function so that it appears as follows:

STDMETHODIMP CUploadStockData::Disconnect() {     if(m_pConnection)     {         m_pConnection->Close();         m_pConnection = NULL;     }     return S_OK; }

You can now add code to implement the IUploadStockData::UploadRecord() method.

To implement the UploadRecord() method

1. On the Project menu, click Settings. Make sure the STLoadData project is selected in the left pane. On the C/C++ page of the Project Settings dialog box, click C++ Language in the Category drop-down list. Click the Enable exception handling option and click OK.
2. In the UploadStockData.cpp file, locate the CUploadStockData:: UploadRecord() function. Add code to the function so that it appears as follows:



(This code can be found in CH10_03.cpp, installed from the companion CD.)

STDMETHODIMP CUploadStockData::UploadRecord(BSTR fund, DATE date,       double price, BSTR uplBy, DATE uplDate) {      // Test for live connection to data source      if(m_pConnection == NULL)           return E_FAIL;      // Create recordset      _RecordsetPtr pRecordset;      HRESULT hr = pRecordset.CreateInstance(__uuidof(Recordset));      if(FAILED(hr)) return hr;      try      {           // Open recordset           _variant_t vConnection = m_pConnection.GetInterfacePtr();           hr = pRecordset->Open(L"pricehistory", vConnection,                 adOpenForwardOnly, adLockOptimistic, adCmdTableDirect);           if(FAILED(hr)) return hr;           // Add new record, set fields to new values and update           hr = pRecordset->AddNew();           if(FAILED(hr)) throw_com_error(hr);           pRecordset->Fields->GetItem(L"ph_fund")->Value = fund;           pRecordset->Fields->GetItem(L"ph_date")->Value = date;           pRecordset->Fields->GetItem(L"ph_price")->Value = price;           pRecordset->Fields->GetItem(L"ph_uploadedby")->Value = uplBy;           pRecordset->Fields->GetItem(L"ph_uploaddate")->                Value = uplDate;           hr = pRecordset->Update();           if(FAILED(hr)) throw_com_error(hr);      }           catch(_com_error e)           {                // very unsophisticated error handling                try           {                pRecordset->Close();           }           catch(...) // Close() may throw another exception           {           }           return E_FAIL;      }      pRecordset->Close();      return S_OK; }

Note that the existing Connection object m_pConnection is passed as an argument to the Recordset Open() method. For details of the other arguments to Open(), look up "ADO" in the Visual C++ Help file.

Note, too, that the AddNew() and Update() methods of the ADO Recordset object are used together to add a new record. A new record is added to the recordset with AddNew(), the field values (accessed through the Recordset object's Fields collection) are set to the new values, and the Update() method is called to update the database.

3. You can now build the STLoadData project to re-create and re-register the STLoadData.dll COM server.

Implementing the Upload Data Command for the STUpload Application

In the next section of the lab, you will implement the Upload command on the Data menu of the STUpload application. You will also add a user-interface update command handler to make the menu and toolbar command available when a document is currently loaded into the application.

To implement the Upload Data command

1. Close all documents currently displayed in the Visual Studio editor.
2. On the Build toolbar, click the STUpload project in the drop-down list.
3. Press CTRL+W to open ClassWizard. On the Message Maps tab, click the CSTUploadDoc class in the Class name drop-down list.
4. Create COMMAND and UPDATE_COMMAND_UI handlers named OnDataUpload() and OnUpdateDataUpload() for the ID_DATA_UPLOAD ID. Click the OnUpdateDataUpload() function in the Member functions list and click Edit Code to jump to the function implementation.
5. Implement the CSTUploadDoc::OnUpdateDataUpload() function as follows:



(This code can be found in CH10_04.cpp, installed from the companion CD.)

void CSTUploadDoc::OnUpdateDataUpload(CCmdUI* pCmdUI) {     // Enable the UploadData command only if there is     // data on file and a fund currently selected for viewing     BOOL bEnable = GetCurrentFund().IsEmpty() ? FALSE : TRUE;     pCmdUI->Enable(bEnable); }

6. At the top of the STUploadDoc.cpp file, add the following directives:

#include <comdef.h>   // for Compiler COM support #include <lmcons.h>   // for the UNLEN constant #include ".\STLoadData\STLoadData.h" #include ".\STLoadData\STLoadData_i.c"

7. Locate the CSTUploadDoc::OnDataUpload() function and add code to implement the function as follows:

void CSTUploadDoc::OnDataUpload() {      if(AfxMessageBox("Upload current file to database?",            MB_OKCANCEL) == IDCANCEL)           return;      ::CoInitialize(NULL);      _COM_SMARTPTR_TYPEDEF(IUploadStockData,            __uuidof(IUploadStockData));      IUploadStockDataPtr pServer;      HRESULT hr = pServer.CreateInstance(CLSID_UploadStockData);      if(SUCCEEDED(hr))           hr = pServer->ConnectToDatabase();      if(SUCCEEDED(hr))      {           try           {                POSITION pos = m_DocList.GetHeadPosition();                while(pos)                {                     CStockData sd = m_DocList.GetNext(pos);                     BSTR fund = sd.GetFund().AllocSysString();                     DATE date = sd.GetDate().m_dt;                     double price = sd.GetPrice();                     DWORD dwLen = UNLEN + 1;                     TCHAR cUser[UNLEN + 1];                     ::GetUserName(cUser, &dwLen);                     CString strUser(cUser);                     BSTR uplBy = (strUser.Left(10)).AllocSysString();                     COleDateTime dtToday =                           COleDateTime::GetCurrentTime();                     DATE uplDate = dtToday.m_dt;                     HRESULT hr = pServer->UploadRecord(fund,                          date, price, uplBy, uplDate);                     ::SysFreeString(fund);                     ::SysFreeString(uplBy);                     if(FAILED(hr))                     {                          CString strPrompt = "Upload of:\n";                          strPrompt += sd.GetAsString();                          strPrompt += "\nfailed";                          if(AfxMessageBox(strPrompt,                                MB_OKCANCEL) == IDCANCEL)                               break;                     }                }                if(!pos) // We got to the end of the loop                     AfxMessageBox("Upload completed successfully");           }           catch(_com_error e)           {                ::MessageBox(NULL, e.ErrorMessage(), NULL, MB_OK);           }           pServer->Disconnect();      }      ::CoUninitialize(); }

Look through the code to make sure you understand how the code creates and uses an instance of the UploadStockData component to add to the database records contained in the document's m_DocList member. Note that the Windows API function GetUserName() is used to get the name of the currently logged-on user. The first 10 characters of this name are placed in the ph_uploadedby field in the pricehistory table of the Stocks database.

You can now build the STUpload application.

To test the Upload Data command

1. Run the STUpload application. On the Data menu, click Import, and load the Ch10Test.dat file from the Chapter 10\Data folder.
2. Click any fund in the Select Fund window to make the Upload Data command available.
3. On the Data menu, click Upload. Click OK to upload the data in the file to the pricehistory table of the Stocks database. When the upload has completed, click OK.
4. Re-run the Upload command from the Data menu. Notice now that each record in the file will fail. This is because the Primary Key constraint on the pricehistory table will not allow duplicate records to be added. Click Cancel to abort the upload process. Close the STUpload application.
5. Click the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and open the Query Analyzer application. Connect to your (local) database.
6. Click Stocks in the DB drop-down list on the toolbar.
7. In the Query Analyzer main window, type the following:

select * from pricehistory

8. Press CTRL+E to execute the SQL query.

The results of the query will appear in the lower half of the Query Analyzer window as shown in Figure 10.5. Check to see that the rows that you added appear at the bottom of the table with your user name and the current date in the ph_uploadedby and ph_uploaddate columns.

Figure 10.5 The Query Analyzer displaying the pricehistory table