Chapter 16: Database Access with ATL Server

IF YOU RE WRITING WEB applications with ATL Server, accessing a database is probably one of the most common tasks you ll perform. If your Web application is going to access a database, in many cases, the dominant factor in determining the performance of your Web application in general will be the time it takes to make a connection to the database. Because of this, ATL Server provides a data source cache that you can use to store database connections. In this chapter you ll focus on using this cache.

The data source cache in ATL Server is geared toward working with OLE DB Consumer Templates. Before we examine the data source cache directly, we ll take a quick look at OLE DB Consumer Templates.

Using OLE DB Consumer Templates

The OLE DB Consumer Templates library does an excellent job of catering to developers with both sophisticated and simple database access needs. Visual C++ also has a wizard that makes generating OLE DB Consumer Template classes for your particular database very easy. Unless you have specific needs not addressed by OLE DB Consumer Templates, you should consider making this library your preferred way of accessing databases.

Let s take a quick look at how you can use the Visual C++ ATL OLE DB Consumer Wizard to generate code to access databases in your application. You should consult the Microsoft Developer Network (MSDN) documentation for a complete overview of OLE DB Consumer Templates.

If you re using Microsoft Visual C++ .NET, you can run the OLE DB Consumer Wizard from any type of project by choosing the ATL OLE DB Consumer icon from the dialog box shown in Figure 16-1 ( assuming you re working in a new or existing ATL Server project).

Figure 16-1. Selecting the ATL OLE DB Consumer icon

In this section we ll walk you through this wizard and look at its options. First, you ll see the wizard s Welcome screen, as shown in Figure 16-2.

Figure 16-2. The Welcome screen of the ATL OLE DB Consumer Wizard

The Attributed option is checked by default. Attributes are new in Visual C++ .NET and are used to keep the generated code clean and easy to read.

The type of template you ll generate for this example is a table, so select the Table radio button in the Type section. You re accessing your database by table only in this example; in a production application, you ll most likely use the Command option to generate a template that will allow you to access a stored procedure.

Next , click the Data Source button to select the database your template will target. Doing so brings up the Data Link Properties dialog box, as shown in Figure 16-3.

Figure 16-3. The Data Link Properties dialog box

In this example, you ll access a SQL Server, so choose the Microsoft OLE DB Provider for SQL Server option. As you can see, OLE DB allows you to access a wide range of data formats. Each format is called an OLE DB Provider. Click the Next button to continue once you ve chosen your OLE DB provider.

The Data Link Properties dialog box enables you to specify the database you want to access and how to authenticate with it. For this example, you ll access the Northwind example database, as shown in Figure 16-4.

Figure 16-4. Selecting the Northwind database

For security reasons, you should always try to design your application so that you can use Windows NT Integrated security for authentication, so make to sure to select that radio button. Clicking the Test Connection button at the bottom of the dialog box will allow you to determine if the authentication information you ve provided is valid.

Once you ve entered a database and the proper authentication information, click the OK button to move on to the Select Database Object dialog box, as shown in Figure 16-5.

Figure 16-5. The Select Database Object dialog box

This dialog box allows you to choose the database object your template will access. In this example, you ll access the Products table. Click OK and you re taken back to the original dialog box (see Figure 16-6).

Figure 16-6. Returning to the Welcome screen

The wizard now has all of the information it needs to generate your OLE DB Consumer Template. Feel free to change the class name and/or filename to suit your taste. Click Finish to generate your template.

The new file, Products.h, will be added automatically to your project. Listing 16-1 presents this file.

Listing 16.1: The Products.h File

 1. #pragma once  2.  3. [  4. #error Security Issue: The connection string may contain a password  5. // The connection string below may contain plain text passwords and/or  6. // other sensitive information. Please remove the #error after reviewing  7. // the connection string for any security related issues. You may want to  8. // store the password in some other form or use a different user authentication.  9.  db_source(L"Provider=SQLOLEDB.1;\  10.             Integrated Security=SSPI;\  11.             Persist Security Info=False;\  12.             Initial Catalog=Northwind;\  13.             Data Source=testing\vsdotnet;\  14.             Use Procedure for Prepare=1;\  15.             Auto Translate=True;Packet Size=4096;\  16.             Workstation ID=TestMachine;\  17.             Use Encryption for Data=False"),  18. db_table(L"dbo.Products")  19. ]  20. class CProducts  21. {  22. public:  23.     [ db_column(1,  24.                 status=m_dwProductIDStatus,  25.                 length=m_dwProductIDLength) ] LONG m_ProductID;  26.     [ db_column(2,  27.                 status=m_dwProductNameStatus,  28.                 length=m_dwProductNameLength) ] TCHAR m_ProductName[41];  29.     [ db_column(3,  30.                 status=m_dwSupplierIDStatus,  31.                 length=m_dwSupplierIDLength) ] LONG m_SupplierID;  32.     [ db_column(4,  33.                 status=m_dwCategoryIDStatus,  34.                 length=m_dwCategoryIDLength) ] LONG m_CategoryID;  35.     [ db_column(5,  36.                 status=m_dwQuantityPerUnitStatus,  37.                 length=m_dwQuantityPerUnitLength) ]  38.                 TCHAR m_QuantityPerUnit[21];  39.     [ db_column(6,  40.                 status=m_dwUnitPriceStatus,  41.                 length=m_dwUnitPriceLength) ] CURRENCY m_UnitPrice;  42.     [ db_column(7,  43.                 status=m_dwUnitsInStockStatus,  44.                 length=m_dwUnitsInStockLength) ] SHORT m_UnitsInStock;  45.     [ db_column(8,  46.                 status=m_dwUnitsOnOrderStatus,  47.                 length=m_dwUnitsOnOrderLength) ] SHORT m_UnitsOnOrder;  48.     [ db_column(9,  49.                 status=m_dwReorderLevelStatus,  50.                 length=m_dwReorderLevelLength) ] SHORT m_ReorderLevel;  51.     [ db_column(10,  52.                 status=m_dwDiscontinuedStatus,  53.                 length=m_dwDiscontinuedLength) ] VARIANT_BOOL m_Discontinued;  54.  55.     // The following wizard-generated data members contain status  56.     // values for the corresponding fields. You  57.     // can use these values to hold NULL values that the database  58.     // returns or to hold error information when the compiler returns  59.     // errors. See Field Status Data Members in Wizard-Generated  60.     // Accessors in the Visual C++ documentation for more information  61.     // on using these fields.  62.     // NOTE: You must initialize these fields before setting/inserting data!  63.  64.     DBSTATUS m_dwProductIDStatus;  65.     DBSTATUS m_dwProductNameStatus;  66.     DBSTATUS m_dwSupplierIDStatus;  67.     DBSTATUS m_dwCategoryIDStatus;  68.     DBSTATUS m_dwQuantityPerUnitStatus;  69.     DBSTATUS m_dwUnitPriceStatus;  70.     DBSTATUS m_dwUnitsInStockStatus;  71.     DBSTATUS m_dwUnitsOnOrderStatus;  72.     DBSTATUS m_dwReorderLevelStatus;  73.     DBSTATUS m_dwDiscontinuedStatus;  74  75.     // The following wizard-generated data members contain length  76.     // values for the corresponding fields.  77.     // NOTE: For variable-length columns, you must initialize these  78.     //       fields before setting/inserting data!  79  80.     DBLENGTH m_dwProductIDLength;  81.     DBLENGTH m_dwProductNameLength;  82.     DBLENGTH m_dwSupplierIDLength;  83.     DBLENGTH m_dwCategoryIDLength;  84.     DBLENGTH m_dwQuantityPerUnitLength;  85.     DBLENGTH m_dwUnitPriceLength;  86.     DBLENGTH m_dwUnitsInStockLength;  87.     DBLENGTH m_dwUnitsOnOrderLength;  88.     DBLENGTH m_dwReorderLevelLength;  89.     DBLENGTH m_dwDiscontinuedLength;  90.  91.  92.     void GetRowsetProperties(CDBPropSet* pPropSet)  93.     {  94.         pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS,  95.                               true,  96.                               DBPROPOPTIONS_OPTIONAL);  97.         pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS,  98.                               true,  99.                               DBPROPOPTIONS_OPTIONAL);  100.    }  101.}; 

 

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

• Lines 9 through 17: As mentioned earlier, you should always try to use Windows NT Integrated security as your authentication mechanism. If you choose SQL Server authentication, the username and password that you enter will show up in clear text in this file. Because of this possibility, the OLE DB Consumer Wizard will generate this #error directive by default. You have to remove it manually in order for this file to compile.

• In line 9, the db_source attribute generates code behind-the-scenes to create instances of CDataSource and CSession . Attributes are essentially mechanisms introduced in Visual C++ .NET to generate code. In the next section you ll look at how you can modify this code to use a data source from the cache that ATL Server provides.

• Line 18: The db_table attribute generates code behind-the-scenes to create an instance of CTable for accessing the specified table.

• Lines 23 through 51: These db_column attributes are used to determine which columns in the database table to associate with which members in the class. For example, the first column in the table is associated with, or bound to, the m_productID member variable.

Before you modify the code in Listing 16-1, you ll look at what you need to do to expose the data source cache to an ATL Server application in the next section.