Putting It All Together

You ll build a simple ATL Server application that displays some of the data in the Products table in the Northwind sample database provided by Microsoft.

The first step is to change your OLE DB Consumer Template so that it can be used with the ATL Server data source cache. You can do this by simply removing the db_source attribute. You ll want to do this so that your OLE DB Consumer Template won t create a data source on its own; rather, you want it to use the data source you provide. Your Products.h file now looks like Listing 16-3.

Listing 16.3: Modifications to the Generated OLE DB Consumer Template Code

 1.  #pragma once  2.  [db_table(L"dbo.Products")]  3   class CProducts  4.  {  5.  public:  6.      [ db_column(1,  7.                  status=m_dwProductIDStatus,  8.                  length=m_dwProductIDLength) ]   LONG m_ProductID;  9.      [ db_column(2,  10.                 status=m_dwProductNameStatus,  11.                 length=m_dwProductNameLength) ] TCHAR m_ProductName[41];  12.     [ db_column(3,  13.                 status=m_dwSupplierIDStatus,  14.                 length=m_dwSupplierIDLength) ] LONG m_SupplierID;  15.     [ db_column(4,  16.                 status=m_dwCategoryIDStatus,  17.                 length=m_dwCategoryIDLength) ] LONG m_CategoryID;  18.     [ db_column(5,  19.                 status=m_dwQuantityPerUnitStatus,  20.                 length=m_dwQuantityPerUnitLength) ]  21                  TCHAR m_QuantityPerUnit[21];  22.     [ db_column(6,  23.                 status=m_dwUnitPriceStatus,  24.                 length=m_dwUnitPriceLength) ] CURRENCY m_UnitPrice;  25.     [ db_column(7,  26.                 status=m_dwUnitsInStockStatus,  27.                 length=m_dwUnitsInStockLength) ] SHORT m_UnitsInStock;  28.     [ db_column(8,  29.                 status=m_dwUnitsOnOrderStatus,  30.                 length=m_dwUnitsOnOrderLength) ] SHORT m_UnitsOnOrder;  31.     [ db_column(9,  32.                 status=m_dwReorderLevelStatus,  33.                 length=m_dwReorderLevelLength) ] SHORT m_ReorderLevel;  34.     [ db_column(10,  35.                 status=m_dwDiscontinuedStatus,  36.                 length=m_dwDiscontinuedLength) ]  37.                 VARIANT_BOOL m_Discontinued;  38.  39.     // The following wizard-generated data members contain status  40.     // values for the corresponding fields. You  41.     // can use these values to hold NULL values that the database  42.     // returns or to hold error information when the compiler returns  43.     // errors. See Field Status Data Members in Wizard-Generated  44.     // Accessors in the Visual C++ documentation for more information  45.     // on using these fields.  46.  47.  48.     DBSTATUS m_dwProductIDStatus;  49.     DBSTATUS m_dwProductNameStatus;  50.     DBSTATUS m_dwSupplierIDStatus;  51.     DBSTATUS m_dwCategoryIDStatus;  52.     DBSTATUS m_dwQuantityPerUnitStatus;  53.     DBSTATUS m_dwUnitPriceStatus;  54.     DBSTATUS m_dwUnitsInStockStatus;  55.     DBSTATUS m_dwUnitsOnOrderStatus;  56.     DBSTATUS m_dwReorderLevelStatus;  57.     DBSTATUS m_dwDiscontinuedStatus;  58.  59.     // The following wizard-generated data members contain length  60.     // values for the corresponding fields.  61.     // NOTE: For variable-length columns, you must initialize these  62.     //       fields before setting/inserting data!  63.  64.     DBLENGTH m_dwProductIDLength;  65.     DBLENGTH m_dwProductNameLength;  66.     DBLENGTH m_dwSupplierIDLength;  67.     DBLENGTH m_dwCategoryIDLength;  68.     DBLENGTH m_dwQuantityPerUnitLength;  69.     DBLENGTH m_dwUnitPriceLength;  70.     DBLENGTH m_dwUnitsInStockLength;  71.     DBLENGTH m_dwUnitsOnOrderLength;  72.     DBLENGTH m_dwReorderLevelLength;  73.     DBLENGTH m_dwDiscontinuedLength;  74.  75.  76.     void GetRowsetProperties(CDBPropSet* pPropSet)  77.     {  78.          pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS,  79.                                true,  80.                                DBPROPOPTIONS_OPTIONAL);  81.          pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS,  82.                                true,  83.                                DBPROPOPTIONS_OPTIONAL);  84.     }  85. };

The next step is to instantiate CProducts with a data source that you get from the ATL Server data source cache. You can do this in the ValidateAndExchange method in your ATL Server application request handler class. The code for your ATL Server application so far looks like Listing 16-4.

Listing 16.4: Reading Data from the Data Source

 1.  #pragma once  2.  3.  #define CONNECTION_STRINGW L"Provider=SQLOLEDB.1;\  4.                               Integrated Security=SSPI;\  5.                               Persist Security Info=False;\  6.                               Initial Catalog=Northwind;\  7.                               Data Source=testing\vsdotnet;\  8.                               Use Procedure for Prepare=1;\  9.                               Auto Translate=True;\  10.                              Packet Size=4096;\  11.                              Workstation ID=TestMachine;\  12.                              Use Encryption for Data=False;  14.  15. #define CONNECTION_STRINGT _T("Provider=SQLOLEDB.1;\  16.                               Integrated Security=SSPI;\  17.                               Persist Security Info=False;\  18.                               Initial Catalog=Northwind;\  19.                               Data Source=testing\vsdotnet;\  20.                               Use Procedure for Prepare=1;\  21.                               Auto Translate=True;\  22.                               Packet Size=4096;\  23.                               Workstation ID=TestMachine;\  24.                               Use Encryption for Data=False;  25.  26. [ request_handler("Default") ]  27. class CDataAccessHandler  28. {  29. private:  30.     CProducts m_products;  31.  32. public:  33.     HTTP_CODE ValidateAndExchange()  34.     {  35.         m_HttpResponse.SetContentType("text/html");  36.  37.         CDataConnection dataConnection;  38.         if( FAILED( GetDataSource( m_spServiceProvider,  39.                                    CONNECTION_STRINGT,  40.                                    CONNECTION_STRINGW,  41.                                    &dataConnection ) ) )  42.         {  43.             return HTTP_S_FALSE;  44.         }  45.  46.         if( FAILED( m_products.Open( dataConnection ) ) )  47.         {  48.             return HTTP_S_FALSE;  49.         }  50.  51.         if( FAILED( m_products.MoveFirst() ) )  52.         {  53.             return HTTP_S_FALSE;  54.         }  55.  56.         return HTTP_SUCCESS;  57.     }  58. };

Most of this code has been assembled from what you ve looked at throughout this chapter. At this point, if you haven t run into any errors, your ATL Server application will have a member, m_products , that will use a data connection from your cache and provide you access to the database specified by the CONNECTION_STRINGT connection string. This access will be made using a cached connection. The first time you access this ATL Server application, you ll create the connection and cache it. Subsequent requests to this application will use the cached data connection and not establish a new one. Thus, you suffer the overhead of connecting to a database only once.

Now that you have access to your database, you ll look at one way of displaying data from that connection. ATL Server s SRF syntax gives you a very convenient way of iterating over data.

The first tag that you add will determine whether or not you have data to display. Define the tag as follows and add it to your ATL Server application:

 1. [tag_name( "HasMoreData" )]  2. HTTP_CODE OnHasMoreProducts()  3. {  4.  if( m_products.MoveNext() == DB_S_ENDOFROWSET )  5.  {  6.      return HTTP_SUCCESS;  7.  }  8.  else  9.  {  10.     return HTTP_S_FALSE;  11. }  12. }

Let s look at this code line-by-line :

Line 1: This attribute associates the SRF tag {{HasMoreData}} with this method. You can use this method in a {{while}} {{endwhile}} loop that will keep executing as long as you have rows in the database table.
Line 4: The MoveNext method is used to iterate over the rows in a given result set. In this case, you re iterating over the rows in the Products table in your Northwind database. In your application, you may be iterating over the results returned by a stored procedure. In any case, this method will return DB_S_ENDOFROWSET when there are no more results. As long as there are more results, you want to return HTTP_SUCCESS .

In your SRF file, you can use this tag as follows:

 {{while HasMoreResults}}  {{endwhile}}

This loop will allow you to iterate over each row in your table. This isn t very interesting yet because you haven t displayed any data from your table. Now introduce the following two methods into your ATL Server application:

 [tag_name( "ProductName" )]  HTTP_CODE OnProductName()  {      m_HttpResponse << m_products.m_ProductName;      return HTTP_SUCCESS;  }  [tag_name( "ProductPrice" )]  HTTP_CODE OnProductPrice()  {      m_HttpResponse << m_products.m_UnitPrice;      return HTTP_SUCCESS;  }

These two methods demonstrate the power and simplicity of OLE DB Consumer Templates. Recall that the db_column attributes in the definition of the CProducts class determine which database columns are bound to which members.

In this case, the m_ProductName and m_UnitPrice members contain the product name and unit price of the current row in the Products table. When you call m_products.MoveNext() , the values of these member variables will be automatically updated to the values of the columns in the next row in your table. All of the work of binding these variables to these values is done by the OLE DB Consumer Templates library.

By putting each value in its own tag, you have a great deal of flexibility in the SRF file to decide how you want to display the data. For example, you can display your data in a table with the following SRF syntax:

 <table border=1>  {{while HasMoreResults}}      <tr><td>{{ProductName}}</td><td>${{UnitPrice}}</td></tr>  {{endwhile}}  </table>

This produces output that looks like that shown in Figure 16-8.

Figure 16-8. Displaying data using SRF

Without changing any code, you can use the following SRF syntax to display your data in a list:

 <ul>  {{while HasMoreResults}}      <li>{{ProductName}}( ${{UnitPrice}} )  {{endwhile}}  </ul>

This code produces output that looks like that shown in Figure 16-9.

Figure 16-9. Sample output in a list

Breaking out each column value into its own SRF tag gives you the freedom to display your data in a number of ways without having to change code. In the real world, doing this allows you to give your Web designers a great deal of artistic freedom without having to change any code.