RAD Studio |  |

Understanding datasets
Collapse All
Topics

Name | Description
Creating an Index with IndexFieldNames | If there is no defined index that implements the sort order you want, you can create a pseudo-index using the IndexFieldNames property. Note: IndexName and IndexFieldNames are mutually exclusive. Setting one property clears values set for the other. For information about IndexName, see Specifying an index with IndexName. The value of IndexFieldNames is a string. To specify a sort order, list each column name to use in the order it should be used, and delimit the names with semicolons. Sorting is by ascending order only. Case-sensitivity of the sort depends on the capabilities of your server. See your... more
Supplying Parameters at Design Time | At design time, you can specify parameter values using the parameter collection editor. To display the parameter collection editor, click on the ellipsis button for the Params or Parameters property in the Object Inspector. If the SQL statement does not contain any parameters, no objects are listed in the collection editor. Note: The parameter collection editor is the same collection editor that appears for other collection properties. Because the editor is shared with other properties, its right-click context menu contains the Add and Delete commands. However, they are never enabled for query parameters. The only way to add or... more
Supplying Parameters at Runtime | To create parameters at runtime, you can use the ParamByName method to assign values to a parameter based on its name (not available for TADOQuery) Params or Parameters property to assign values to a parameter based on the parameter's ordinal position within the SQL statement. Params.ParamValues or Parameters.ParamValues property to assign values to one or more parameters in a single command line, based on the name of each parameter set. The following code uses ParamByName to assign the text of an edit box to the :Capital parameter:
Adding New Records | A dataset must be in dsInsert mode before an application can add new records. In code, you can use the Insert or Append methods to put a dataset into dsInsert mode if the read-only CanModify property for the dataset is True. When a dataset transitions to dsInsert mode, it first receives a BeforeInsert event. After the transition to insert mode is successfully completed, the dataset receives first an OnNewRecord event hand then an AfterInsert event. You can use these events, for example, to provide initial values to newly inserted records:
Applying or Canceling a Range | When you call SetRangeStart or EditRangeStart to specify the start of a range, or SetRangeEnd or EditRangeEnd to specify the end of a range, the dataset enters the dsSetKey state. It stays in that state until you apply or cancel the range.
Calculating Fields | Using the Fields editor, you can define calculated fields for your datasets. When a dataset contains calculated fields, you provide the code to calculate those field's values in an OnCalcFields event handler. The AutoCalcFields property determines when OnCalcFields is called. If AutoCalcFields is True, OnCalcFields is called when A dataset is opened. The dataset enters edit mode. A record is retrieved from the database. Focus moves from one visual component to another, or from one column to another in a data-aware grid control and the current record has been modified. If AutoCalcFields is False, then OnCalcFields is not... more
Canceling Changes | An application can undo changes made to the current record at any time, if it has not yet directly or indirectly called Post. For example, if a dataset is in dsEdit mode, and a user has changed the data in one or more fields, the application can return the record back to its original values by calling the Cancel method for the dataset. A call to Cancel always returns a dataset to dsBrowse state. If the dataset was in dsEdit or dsInsert mode when your application called Cancel, it receives BeforeCancel and AfterCancel events before and after the current... more
Controlling Read/Write Access to Tables | By default when a table type dataset is opened, it requests read and write access for the underlying database table. Depending on the characteristics of the underlying database table, the requested write privilege may not be granted (for example, when you request write access to an SQL table on a remote server and the server restricts the table's access to read only). Note: This is not true for TClientDataSet, which determines whether users can edit data from information that the dataset provider supplies with data packets. It is also not true for TSQLTable, which is a unidirectional dataset,... more
Creating and Deleting Tables | Some table type datasets let you create and delete the underlying tables at design time or at runtime. Typically, database tables are created and deleted by a database administrator. However, it can be handy during application development and testing to create and destroy database tables that your application can use.
Creating Filters | There are two ways to create a filter for a dataset: Set the Filter property. Filter is especially useful for creating and applying filters at runtime. Write an OnFilterRecord event handler for simple or complex filter conditions. With OnFilterRecord, you specify filter conditions at design time. Unlike the Filter property, which is restricted to a single string containing filter logic, an OnFilterRecord event can take advantage of branching and looping logic to create complex, multi-level filter conditions. The main advantage to creating filters using the Filter property is that your application can create, change, and apply filters dynamically, (for... more
Creating Master/detail Relationships | Table type datasets can be linked into master/detail relationships. When you set up a master/detail relationship, you link two datasets so that all the records of one (the detail) always correspond to the single current record in the other (the master). Table type datasets support master/detail relationships in two very distinct ways: All table type datasets can act as the detail of another dataset by linking cursors. This process is described in Making the table a detail of another dataset. TTable, TSQLTable, and all client datasets can act as the master in a master/detail relationship that uses... more
Deleting Records | Use the Delete method to delete the current record in an active dataset. When the Delete method is called, The dataset receives a BeforeDelete event. The dataset attempts to delete the current record. The dataset returns to the dsBrowse state. The dataset receives an AfterDelete event. If want to prevent the deletion in the BeforeDelete event handler, you can call the global Abort procedure:
Editing Records | A dataset must be in dsEdit mode before an application can modify records. In your code you can use the Edit method to put a dataset into dsEdit mode if the read-only CanModify property for the dataset is True. When a dataset transitions to dsEdit mode, it first receives a BeforeEdit event. After the transition to edit mode is successfully completed, the dataset receives an AfterEdit event. Typically, these events are used for updating the user interface to indicate the current state of the dataset. If the dataset can't be put into edit mode for some reason, an... more
Emptying Tables | Many table type datasets supply a single method that lets you delete all rows of data in the table.
Enabling and Disabling Filtering |
Establishing Master/detail Relationships Using Parameters | To set up a master/detail relationship where the detail set is a query-type dataset, you must specify a query that uses parameters. These parameters refer to current field values on the master dataset. Because the current field values on the master dataset change dynamically at runtime, you must rebind the detail set's parameters every time the master record changes. Although you could write code to do this using an event handler, all query-type datasets except TIBQuery provide an easier mechanism using the DataSource property. If parameter values for a parameterized query are not bound at design time or specified at... more
Executing a Search with Find Methods | The Find methods do the same thing as the Goto methods, except that you do not need to explicitly put the dataset in dsSetKey state to specify the key field values on which to search.
Executing a Search with Goto Methods |
Executing Queries That Don't Return a Result Set | When a query returns a set of records (such as a SELECT query), you execute the query the same way you populate any dataset with records: by setting Active to True or calling the Open method. However, often SQL commands do not return any records. Such commands include statements that use Data Definition Language (DDL) or Data Manipulation Language (DML) statements other than SELECT statements (For example, INSERT, DELETE, UPDATE, CREATE INDEX, and ALTER TABLE commands do not return any records). For all query-type datasets, you can execute a query that does not return a result set by calling ExecSQL... more
Executing Stored Procedures That Don't Return a Result Set | When a stored procedure returns a cursor, you execute it the same way you populate any dataset with records: by setting Active to True or calling the Open method. However, often stored procedures do not return any data, or only return results in output parameters. You can execute a stored procedure that does not return a result set by calling ExecProc. After executing the stored procedure, you can use the ParamByName method to read the value of the result parameter or of any output parameters:
Fetching Multiple Result Sets | Some stored procedures return multiple sets of records. The dataset only fetches the first set when you open it. If you are using TSQLStoredProc or TADOStoredProc, you can access the other sets of records by calling the NextRecordSet method:
Limiting Records with Ranges | You can temporarily view and edit a subset of data for any dataset by using filters. Some table type datasets support an additional way to access a subset of available records, called ranges. Ranges only apply to TTable and to client datasets. Despite their similarities, ranges and filters have different uses. The following topics discuss the differences between ranges and filters and how to use ranges: Understanding the differences between ranges and filters Specifying ranges Modifying a range Applying or canceling a range
Making the Table a Detail of Another Dataset | A table type dataset's MasterSource and MasterFields properties can be used to establish one-to-many relationships between two datasets. The MasterSource property is used to specify a data source from which the table gets data from the master table. This data source can be linked to any type of dataset. For instance, by specifying a query's data source in this property, you can link a client dataset as the detail of the query, so that the client dataset tracks events occurring in the query. The dataset is linked to the master table based on its current index. Before you specify the... more
Marking and Returning to Records | In addition to moving from record to record in a dataset (or moving from one record to another by a specific number of records), it is often also useful to mark a particular location in a dataset so that you can return to it quickly when desired. TDataSet introduces a bookmarking feature that consists of a Bookmark property and five bookmark methods. TDataSet implements virtual bookmark methods. While these methods ensure that any dataset object derived from TDataSet returns a value if a bookmark method is called, the return values are merely defaults that do not keep track of the... more
Modifying a Range | Two functions enable you to modify the existing boundary conditions for a range: EditRangeStart, for changing the starting values for a range; and EditRangeEnd, for changing the ending values for the range.
Modifying Entire Records | On forms, all data-aware controls except for grids and the navigator provide access to a single field in a record. In code, however, you can use the following methods that work with entire record structures provided that the structure of the database tables underlying the dataset is stable and does not change. The following table summarizes the methods available for working with entire records rather than individual fields in those records: Methods that work with entire records
Navigating Records in a Filtered Dataset | There are four dataset methods that navigate among records in a filtered dataset. The following table lists these methods and describes what they do: Filtered dataset navigational methods
Obtaining Information About Indexes | Your application can obtain information about server-defined indexes from all table type datasets. To obtain a list of available indexes for the dataset, call the GetIndexNames method. GetIndexNames fills a string list with valid index names. For example, the following code fills a listbox with the names of all indexes defined for the CustomersTable dataset:
Opening and Closing Datasets | To read or write data in a dataset, an application must first open it. You can open a dataset in two ways:
Posting Data | After you finish editing a record, you must call the Post method to write out your changes. The Post method behaves differently, depending on the dataset's state and on whether you are caching updates. If you are not caching updates, and the dataset is in the dsEdit or dsInsert state, Post writes the current record to the database and returns the dataset to the dsBrowse state. If you are caching updates, and the dataset is in the dsEdit or dsInsert state, Post writes the current record to an internal cache and returns the dataset to the dsBrowse state. The edits... more
Preparing Queries | Preparing a query is an optional step that precedes query execution. Preparing a query submits the SQL statement and its parameters, if any, to the data access layer and the database server for parsing, resource allocation, and optimization. In some datasets, the dataset may perform additional setup operations when preparing the query. These operations improve query performance, making your application faster, especially when working with updatable queries. An application can prepare a query by setting the Prepared property to True. If you do not prepare a query before executing it, the dataset automatically prepares it for you each time... more
Preparing Stored Procedures | As with query-type datasets, stored procedure-type datasets must be prepared before they execute the stored procedure. Preparing a stored procedure tells the data access layer and the database server to allocate resources for the stored procedure and to bind parameters. These operations can improve performance. If you attempt to execute a stored procedure before preparing it, the dataset automatically prepares it for you, and then unprepares it after it executes. If you plan to execute a stored procedure a number of times, it is more efficient to explicitly prepare it by setting the Prepared property to True.
Searching On Partial Keys | Each time you call SetKey or FindKey, the method clears any previous values in the Fields property. If you want to repeat a search using previously set fields, or you want to add to the fields used in a search, call EditKey in place of SetKey and FindKey. For example, suppose you have already executed a search of the Employee table based on the City field of the "CityIndex" index. Suppose further that "CityIndex" includes both the City and Company fields. To find a record with a specified company name in a specified city, use the following code:... more
Searching On Partial Keys | If the dataset has more than one key column, and you want to search for values in a subset of that key, set KeyFieldCount to the number of columns on which you are searching. For example, if the dataset's current index has three columns, and you want to search for values using just the first column, set KeyFieldCount to 1. For table type datasets with multiple-column keys, you can search only for values in contiguous columns, beginning with the first. For example, for a three-column key you can search for values in the first column, the first and second, or... more
Setting Filter Options | The FilterOptions property lets you specify whether a filter that compares string-based fields accepts records based on partial comparisons and whether string comparisons are case-sensitive. FilterOptions is a set property that can be an empty set (the default), or that can contain either or both of the following values: FilterOptions values
Setting the Filter Property | To create a filter using the Filter property, set the value of the property to a string that contains the filter's test condition. For example, the following statement creates a filter that tests a dataset's State field to see if it contains a value for the state of California:
Sorting Records with Indexes | An index determines the display order of records in a table. Typically, records appear in ascending order based on a primary, or default, index. This default behavior does not require application intervention. If you want a different sort order, however, you must specify either An alternate index. A list of columns on which to sort (not available on servers that aren't SQL-based). Indexes let you present the data from a table in different orders. On SQL-based tables, this sort order is implemented by using the index to generate an ORDER BY clause in a query that fetches the table's records.... more
Specifying Ranges | There are two mutually exclusive ways to specify a range: Specify the beginning and ending separately using SetRangeStart and SetRangeEnd. Specify both endpoints at once using SetRange.
Specifying the Current Record After a Successful Search | By default, a successful search positions the cursor on the first record that matches the search criteria. If you prefer, you can set the KeyExclusive property to True to position the cursor on the next record after the first matching record. By default, KeyExclusive is False, meaning that successful searches position the cursor on the first matching record.
Specifying the Query | For true query-type datasets, you use the SQL property to specify the SQL statement for the dataset to execute. Some datasets, such as TADODataSet, TSQLDataSet, and client datasets, use a CommandText property to accomplish the same thing. Most queries that return records are SELECT commands. Typically, they define the fields to include, the tables from which to select those fields, conditions that limit what records to include, and the order of the resulting dataset. For example:
Synchronizing Tables | If you have two or more datasets that represent the same database table but do not share a data source component, then each dataset has its own view on the data and its own current record. As users access records through each datasets, the components' current records will differ. If the datasets are all instances of TTable, or all instances of TIBTable, or all client datasets, you can force the current record for each of these datasets to be the same by calling the GotoCurrent method. GotoCurrent sets its own dataset's current record to the current record of... more
Understanding the Differences Between Ranges and Filters | Both ranges and filters restrict visible records to a subset of all available records, but the way they do so differs. A range is a set of contiguously indexed records that fall between specified boundary values. For example, in an employee database indexed on last name, you might apply a range to display all employees whose last names are greater than "Jones" and less than "Smith". Because ranges depend on indexes, you must set the current index to one that can be used to define the range. As with specifying an index to sort records, you can assign the... more
Using Indexes to Search for Records | You can search against any dataset using the Locate and Lookup methods of TDataSet. However, by explicitly using indexes, some table type datasets can improve over the searching performance provided by the Locate and Lookup methods. ADO datasets all support the Seek method, which moves to a record based on a set of field values for fields in the current index. Seek lets you specify where to move the cursor relative to the first or last matching record. TTable and all types of client dataset support similar indexed-based searches, but use a combination of related methods. The following table... more
Using Nested Detail Tables | A nested table is a detail dataset that is the value of a single dataset field in another (master) dataset. For datasets that represent server data, a nested detail dataset can only be used for a dataset field on the server. TClientDataSet components do not represent server data, but they can also contain dataset fields if you create a dataset for them that contains nested details, or if they receive data from a provider that is linked to the master table of a master/detail relationship. Note: For TClientDataSet, using nested detail sets is necessary if you want to apply... more
Using Parameters in Queries | A parameterized SQL statement contains parameters, or variables, the values of which can be varied at design time or runtime. Parameters can replace data values, such as those used in a WHERE clause for comparisons, that appear in an SQL statement. Ordinarily, parameters stand in for data values passed to the statement. For example, in the following INSERT statement, values to insert are passed as parameters:
Using Query-type Datasets |
Using Stored Procedure-type Datasets | How your application uses a stored procedure depends on how the stored procedure was coded, whether and how it returns data, the specific database server used, or a combination of these factors.
Using TDataSet Descendants | TDataSet has several immediate descendants, each of which corresponds to a different data access mechanism. You do not work directly with any of these descendants. Rather, each descendant introduces the properties and methods for using a particular data access mechanism. These properties and methods are then exposed by descendant classes that are adapted to different types of server data. The immediate descendants of TDataSet include TBDEDataSet, which uses the Borland Database Engine (BDE) to communicate with the database server. The TBDEDataSet descendants you use are TTable, TQuery, TStoredProc, and TNestedTable. The unique features of... more
Using Table Type Datasets |
Using the Eof and Bof Properties | Two read-only, runtime properties, Eof (End-of-file) and Bof(Beginning-of-file), are useful when you want to iterate through all records in a dataset.
Using the First and Last Methods | The First method moves the cursor to the first row in a dataset and sets the BOF property to True. If the cursor is already at the first row in the dataset, First does nothing. For example, the following code moves to the first record in CustTable:
Using the MoveBy Method | MoveBy lets you specify how many rows forward or back to move the cursor in a dataset. Movement is relative to the current record at the time that MoveBy is called. MoveBy also sets the BOF and EOF properties for the dataset as appropriate. This function takes an integer parameter, the number of records to move. Positive integers indicate a forward move and negative integers indicate a backward move. Note: MoveBy raises an exception in unidirectional datasets if you use a negative argument. MoveBy returns the number of rows it moves. If you attempt to move past the beginning or... more
Using the Next and Prior Methods | The Next method moves the cursor forward one row in the dataset and sets the BOF property to False if the dataset is not empty. If the cursor is already at the last row in the dataset when you call Next, nothing happens. For example, the following code moves to the next record in CustTable:
Using Unidirectional Result Sets | When a query-type dataset returns a result set, it also receives a cursor, or pointer to the first record in that result set. The record pointed to by the cursor is the currently active record. The current record is the one whose field values are displayed in data-aware components associated with the result set's data source. Unless you are using dbExpress, this cursor is bi-directional by default. A bi-directional cursor can navigate both forward and backward through its records. Bi-directional cursor support requires some additional processing overhead, and can slow some queries. If you do not need to be able... more
Working with Stored Procedure Parameters | There are four types of parameters that can be associated with stored procedures: Input parameters, used to pass values to a stored procedure for processing. Output parameters, used by a stored procedure to pass return values to an application. Input/output parameters, used to pass values to a stored procedure for processing, and used by the stored procedure to pass return values to the application. A result parameter, used by some stored procedures to return an error or status value to an application. A stored procedure can only return one result parameter. Whether a stored procedure uses a... more
Using Locate | Locate moves the cursor to the first row matching a specified set of search criteria. In its simplest form, you pass Locate the name of a column to search, a field value to match, and an options flag specifying whether the search is case-insensitive or if it can use partial-key matching. (Partial-key matching is when the criterion string need only be a prefix of the field value.) For example, the following code moves the cursor to the first row in the CustTable where the value in the Company column is "Professional Divers, Ltd.":
Using Lookup | Lookup searches for the first row that matches specified search criteria. If it finds a matching row, it forces the recalculation of any calculated fields and lookup fields associated with the dataset, then returns one or more fields from the matching row. Lookup does not move the cursor to the matching row; it only returns values from it. In its simplest form, you pass Lookup the name of field to search, the field value to match, and the field or fields to return. For example, the following code looks for the first record in the CustTable where the value of... more
Modifying Data | You can use the following dataset methods to insert, update, and delete data if the read-only CanModify property is True. CanModify is True unless the dataset is unidirectional, the database underlying the dataset does not permit read and write privileges, or some other factor intervenes. (Intervening factors include the ReadOnly property on some datasets or the RequestLive property on TQuery components.) Dataset methods for inserting, updating, and deleting data
Searching Datasets | If a dataset is not unidirectional, you can search against it using the Locate and Lookup methods. These methods enable you to search on any type of columns in any dataset. The following topics discuss Locate and Lookup in greater detail: Using Locate Using Lookup Note: Some TDataSet descendants introduce an additional family of methods for searching based on an index. For information about these additional methods, see Using Indexes to Search for Records.
Specifying an Index with IndexName | Use the IndexName property to cause an index to be active. Once active, an index determines the order of records in the dataset. (It can also be used as the basis for a master-detail link, an index-based search, or index-based filtering.) To activate an index, set the IndexName property to the name of the index. In some database systems, primary indexes do not have names. To activate one of these indexes, set IndexName to a blank string. At design-time, you can select an index from a list of available indexes by clicking the property's ellipsis button in the Object Inspector... more
Displaying and Editing a Subset of Data Using Filters | An application is frequently interested in only a subset of records from a dataset. For example, you may be interested in retrieving or viewing only those records for companies based in California in your customer database, or you may want to find a record that contains a particular set of field values. In each case, you can use filters to restrict an application's access to a subset of all records in the dataset. With unidirectional datasets, you can only limit the records in the dataset by using a query that restricts the records in the dataset. With other TDataSet descendants,... more
Understanding Datasets: Overview | The fundamental unit for accessing data is the dataset family of objects. Your application uses datasets for all database access. A dataset object represents a set of records from a database organized into a logical table. These records may be the records from a single database table, or they may represent the results of executing a query or stored procedure. All dataset objects that you use in your database applications descend from TDataSet, and they inherit data fields, properties, events, and methods from this class. TDataSet is a virtualized dataset, meaning that many of its properties and methods are virtual... more
Determining Dataset States | The state—or mode—of a dataset determines what can be done to its data. For example, when a dataset is closed, its state is dsInactive, meaning that nothing can be done to its data. At runtime, you can examine a dataset's read-only State property to determine its current state. The following table summarizes possible values for the State property and what they mean: Values for the dataset State property
Navigating Datasets | Each active dataset has a cursor, or pointer, to the current row in the dataset. The current row in a dataset is the one whose field values currently show in single-field, data-aware controls on a form, such as TDBEdit, TDBLabel, and TDBMemo. If the dataset supports editing, the current record contains the values that can be manipulated by edit, insert, and delete methods. You can change the current row by moving the cursor to point at a different row. The following table lists methods you can use in application code to move to different records: Navigational methods of... more
Writing an OnFilterRecord Event Handler | You can write code to filter records using the OnFilterRecord events generated by the dataset for each record it retrieves. This event handler implements a test that determines if a record should be included in those that are visible to the application. To indicate whether a record passes the filter condition, your OnFilterRecord handler sets its Accept parameter to True to include a record, or False to exclude it. For example, the following filter displays only those records with the State field set to "CA":
Types of Datasets | 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 TTable, TADOTable, TSQLTable, and 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... more

Copyright(C) 2009 Embarcadero Technologies, Inc. All Rights Reserved.
What do you think about this topic? Send feedback!