Types of Datasets
Go Up to Understanding Datasets Index
Using TDataSet descendants classifies TDataSet descendants by the method they use to access their data. Another useful way to classify TDataSet descendants is to consider the type of server data they represent. Viewed this way, there are three basic classes of datasets:
Table type datasets: Table type datasets represent a single table from the database server, including all of its rows and columns. Table type datasets include Bde.DBTables.TTable, Data.Win.ADODB.TADOTable, Data.SqlExpr.TSQLTable, and IBX.IBTable.TIBTable.
Table type datasets let you take advantage of indexes defined on the server. Because there is a one-to-one correspondence between database table and dataset, you can use server indexes that are defined for the database table. Indexes allow your application to sort the records in the table, speed searches and lookups, and can form the basis of a master/detail relationship. Some table type datasets also take advantage of the one-to-one relationship between dataset and database table to let you perform table-level operations such as creating and deleting database tables.
Query-type datasets: Query-type datasets represent a single SQL command, or query. Queries can represent the result set from executing a command (typically a SELECT statement), or they can execute a command that does not return any records (for example, an UPDATE statement). Query-type datasets include Bde.DBTables.TQuery, Data.Win.ADODB.TADOQuery, Data.SqlExpr.TSQLQuery, and IBX.IBQuery.TIBQuery.
To use a query-type dataset effectively, you must be familiar with SQL and your server's SQL implementation, including limitations and extensions to the SQL-92 standard. If you are new to SQL, you may want to purchase a third party book that covers SQL in-depth. One of the best is Understanding the New SQL: A Complete Guide, by Jim Melton and Alan R. Simpson, Morgan Kaufmann Publishers.
Stored procedure-type datasets: Stored procedure-type datasets represent a stored procedure on the database server. Stored procedure-type datasets include Bde.DBTables.TStoredProc, Data.Win.ADODB.TADOStoredProc, Data.SqlExpr.TSQLStoredProc, and IBX.IBStoredProc.TIBStoredProc.
A stored procedure is a self-contained program written in the procedure and trigger language specific to the database system used. They typically handle frequently repeated database-related tasks, and are especially useful for operations that act on large numbers of records or that use aggregate or mathematical functions. Using stored procedures typically improves the performance of a database application by:
- Taking advantage of the server's usually greater processing power and speed.
- Reducing network traffic by moving processing to the server.
Stored procedures may or may not return data. Those that return data may return it as a cursor (similar to the results of a SELECT query), as multiple cursors (effectively returning multiple datasets), or they may return data in output parameters. These differences depend in part on the server: Some servers do not allow stored procedures to return data, or only allow output parameters. Some servers do not support stored procedures at all. See your server documentation to determine what is available.
Note: You can usually use a query-type dataset to execute stored procedures because most servers provide extensions to SQL for working with stored procedures. Each server, however, uses its own syntax for this. If you choose to use a query-type dataset instead of a stored procedure-type dataset, see your server documentation for the necessary syntax.
In addition to the datasets that fall neatly into these three categories, TDataSet has some descendants that fit into more than one category:
- Data.Win.ADODB.TADODataSet and Data.SqlExpr.TSQLDataSet have a CommandType property that lets you specify whether they represent a table, query, or stored procedure. Property and method names are most similar to query-type datasets, although TADODataSet lets you specify an index like a table type dataset.
- Datasnap.DBClient.TClientDataSet represents the data from another dataset. As such, it can represent a table, query, or stored procedure. TClientDataSet behaves most like a table type dataset, because of its index support. However, it also has some of the features of queries and stored procedures: the management of parameters and the ability to execute without retrieving a result set.
- Some other client datasets have a CommandType property that lets you specify whether they represent a table, query, or stored procedure. Property and method names are like TClientDataSet, including parameter support, indexes, and the ability to execute without retrieving a result set.
- IBX.IBCustomDataSet.TIBDataSet can represent both queries and stored procedures. In fact, it can represent multiple queries and stored procedures simultaneously, with separate properties for each.