Delphi has always shone in the area of database programming. For this reason, it is not surprising to see a lot of support for handling datasets within the WebSnap framework. Specifically, you can use the DataSetAdapter component to connect to a dataset and display its values in a form or a table using the AdapterPageProducer component's visual editor.
As an example, I built a new WebSnap application (called WSnapTable) with an AdapterPage-Producer as its main page to display a table in a grid and another AdapterPageProducer in a secondary page to show a form with a single record. I also added to the application a WebSnap Data Module, as a container for the dataset components. The data module has a ClientDataSet that's wired to a dbExpress dataset through a provider and based on an InterBase connection, as shown here:
object ClientDataSet1: TClientDataSet Active = True ProviderName = 'DataSetProvider1' end object SQLConnection1: TSQLConnection Connected = True ConnectionName = 'IBLocal' LoginPrompt = False end object SQLDataSet1: TSQLDataSet SQLConnection = SQLConnection1 CommandText = 'select CUST_NO, CUSTOMER, ADDRESS_LINE1, CITY, STATE_PROVINCE, ' + ' COUNTRY from CUSTOMER' end object DataSetProvider1: TDataSetProvider DataSet = SQLDataSet1 end
Now that you have a dataset available, you can add a DataSetAdapter to the first page and connect it to the web module's ClientDataSet. The adapter automatically makes available all of the dataset's fields and several predefined actions for operating on the dataset (such as Delete, Edit, and Apply). You can add them explicitly to the Actions and Fields collections to exclude some of them and customize their behavior, but this step is not always required.
Like the PagedAdapter, the DataSetAdapter has a PageSize property you can use to indicate the number of elements to display in each page. The component also has commands you can use to navigate among pages. This approach is particularly suitable when you want to display a large dataset in a grid. These are the adapter settings for the main page of the WSnapTable example:
object DataSetAdapter1: TDataSetAdapter DataSet = WebDataModule1.ClientDataSet1 PageSize = 6 end
The corresponding page producer has a form containing two command groups and a grid. The first command group (displayed above the grid) has the predefined commands for handling pages: CmdPrevPage, CmdNextPage, and CmdGotoPage. The last command generates a list of numbers for the pages, so that a user can jump to each of them directly. The AdapterGrid component has the default columns plus an extra column hosting Edit and Delete commands. The bottom command group provides a button used to create a new record. You can see an example of the table's output in Figure 20.10 and the complete settings of the AdapterPageProducer in Listing 20.2.
object AdapterPageProducer: TAdapterPageProducer object AdapterForm1: TAdapterForm object AdapterCommandGroup1: TAdapterCommandGroup DisplayComponent = AdapterGrid1 object CmdPrevPage: TAdapterActionButton ActionName = 'PrevPage' Caption = 'Previous Page' end object CmdGotoPage: TAdapterActionButton... object CmdNextPage: TAdapterActionButton ActionName = 'NextPage' Caption = 'Next Page' end end object AdapterGrid1: TAdapterGrid TableAttributes.CellSpacing = 0 TableAttributes.CellPadding = 3 Adapter = DataSetAdapter1 AdapterMode = 'Browse' object ColCUST_NO: TAdapterDisplayColumn ... object AdapterCommandColumn1: TAdapterCommandColumn Caption = 'COMMANDS' object CmdEditRow: TAdapterActionButton ActionName = 'EditRow' Caption = 'Edit' PageName = 'formview' DisplayType = ctAnchor end object CmdDeleteRow: TAdapterActionButton ActionName = 'DeleteRow' Caption = 'Delete' DisplayType = ctAnchor end end end object AdapterCommandGroup2: TAdapterCommandGroup DisplayComponent = AdapterGrid1 object CmdNewRow: TAdapterActionButton ActionName = 'NewRow' Caption = 'New' PageName = 'formview' end end end end
There are a couple of things to notice in this listing. First, the grid's AdapterMode property is set to Browse (other possibilities are Edit, Insert, and Query). This dataset display mode for adapters determines the type of user interface (text or edit boxes and other input controls) and the visibility of other buttons (for example, Apply and Cancel buttons are only present in the edit view; the opposite is true for the Edit command).
You can also modify the adapter mode by using server-side script and accessing Adapter.Mode.
Second, I modified the display of the commands in the grid using the ctAnchor value for the DisplayType property instead of the default button style. Similar properties are available for most components of this architecture to tweak the HTML code they produce.
Some of the commands are connected to a different page, which will be displayed after the commands are invoked. For example, the edit command's PageName property is set to formview. This second page of the application has an AdapterPageProducer with components hooked to the same DataSetAdapter as of the other table, so that all the requests will be automatically synchronized. If you click the Edit command, the program will open the secondary page that displays the data of the record corresponding to the command.
Listing 20.3 shows the details of the page producer for the program's second page. Again, building the HTML form visually using the Delphi-specific designer (see Figure 20.11) was a very fast operation.
object AdapterPageProducer: TAdapterPageProducer object AdapterForm1: TAdapterForm object AdapterErrorList1: TAdapterErrorList Adapter = table.DataSetAdapter1 end object AdapterCommandGroup1: TAdapterCommandGroup DisplayComponent = AdapterFieldGroup1 object CmdApply: TAdapterActionButton ActionName = 'Apply' PageName = 'table' end object CmdCancel: TAdapterActionButton ActionName = 'Cancel' PageName = 'table' end object CmdDeleteRow: TAdapterActionButton ActionName = 'DeleteRow' Caption = 'Delete' PageName = 'table' end end object AdapterFieldGroup1: TAdapterFieldGroup Adapter = table.DataSetAdapter1 AdapterMode = 'Edit' object FldCUST_NO: TAdapterDisplayField DisplayWidth = 10 FieldName = 'CUST_NO' end object FldCUSTOMER: TAdapterDisplayField ... end end end
In the listing, you can see that all the operations send the user back to the main page and that the AdapterMode is set to Edit, unless there are update errors or conflicts. In this case, the same page is displayed again, with a description of the errors obtained by adding an AdapterErrorList component at the top of the form.
The second page is not published, because selecting it without referring to a specific record would make little sense. To unpublish the page, you comment the corresponding flag in the initialization code. Finally, to make the changes to the database persistent, you can call the ApplyUdpates method in the OnAfterPost and OnAfterDelete events of the ClientDataSet component hosted by the data module.
Another problem (which I haven't fixed) relates to the fact that the SQL server assigns the ID of each customer, so that when you enter a new record, the data in the ClientDataSet and in the database are no longer aligned. This situation can cause Record Not Found errors.
The DataSetAdapter component has specific support for master/detail relationships between datasets. After you've created the relationship among the datasets, as usual, define an adapter for each dataset and then connect the MasterAdapter property of the detail dataset's adapter. Setting up the master/detail relationship between the adapters makes them work in a more seamless way. For example, when you change the work mode of the master or enter new records, the detail automatically enters into Edit mode or is refreshed.
The WSnapMD example uses a data module to define such a relationship. It includes two ClientDataSet components, each connected to a SQLDataSet through a provider. The data access components each refer to a table, and the ClientDataSet components define a master/detail relationship. The same data module hosts two dataset adapters that refer to the two datasets and again define the master/detail relationship:
object dsaDepartment: TDataSetAdapter DataSet = cdsDepartment end object dsaEmployee: TDataSetAdapter DataSet = cdsEmployee MasterAdapter = dsaDepartment end
I originally tried to use a SimpleDataSet component to avoid cluttering the data module, but this approach didn't work. The master/detail portion of the program was correct, but moving from a page to the next or a previous page with the related buttons kept failing. The reason is that if you use a SimpleDataSet, a bug closes the dataset at each interaction, losing the status information.
This WebSnap application's only page has an AdapterPageProducer component hooked to both dataset adapters. The page's form has a field group hooked to the master and a grid connected with the detail. Unlike other examples, I tried to improve the user interface by adding custom attributes for the various elements. I used a gray background, displayed some of the grid borders (HTML grids are often used by Web Surface Designer), centered most of the elements, and added spacing. Notice that I added extra spaces to the button captions to prevent them from being too small. You can see the related code in the following detailed excerpt and the effect in Figure 20.12:
object AdapterPageProducer: TAdapterPageProducer object AdapterForm1: TAdapterForm Custom = 'Border="1" CellSpacing="0" CellPadding="10" ' + 'BgColor="Silver" align="center"' object AdapterCommandGroup1: TAdapterCommandGroup DisplayComponent = AdapterFieldGroup1 Custom = 'Align="Center"' object CmdFirstRow: TAdapterActionButton... object CmdPrevRow: TAdapterActionButton... object CmdNextRow: TAdapterActionButton... object CmdLastRow: TAdapterActionButton... end object AdapterFieldGroup1: TAdapterFieldGroup Custom = 'BgColor="Silver"' Adapter = WDataMod.dsaDepartment AdapterMode = 'Browse' end object AdapterGrid1: TAdapterGrid TableAttributes.BgColor = 'Silver' TableAttributes.CellSpacing = 0 TableAttributes.CellPadding = 3 HeadingAttributes.BgColor = 'Gray' Adapter = WDataMod.dsaEmployee AdapterMode = 'Browse' object ColEMP_NO: TAdapterDisplayColumn... object ColFIRST_NAME: TAdapterDisplayColumn... object ColLAST_NAME: TAdapterDisplayColumn... object ColDEPT_NO: TAdapterDisplayColumn... object ColJOB_CODE: TAdapterDisplayColumn... object ColJOB_COUNTRY: TAdapterDisplayColumn... object ColSALARY: TAdapterDisplayColumn... end end end