[ Pobierz całość w formacie PDF ]
.Under s t andi ng dat as et s 24-47 Us i n g q u e r y - t y p e d a t a s e t sTo illustrate how this works, consider two tables: a customer table and an orderstable.For every customer, the orders table contains a set of orders that the customermade.The Customer table includes an ID field that specifies a unique customer ID.The orders table includes a CustID field that specifies the ID of the customer whomade an order.The first step is to set up the Customer dataset:1 Add a table type dataset to your application and bind it to the Customer table.2 Add a TDataSource component named CustomerSource.Set its DataSet property tothe dataset added in step 1.This data source now represents the Customer dataset.3 Add a query-type dataset and set its SQL property toSELECT CustID, OrderNo, SaleDateFROM OrdersWHERE CustID = :IDNote that the name of the parameter is the same as the name of the field in themaster (Customer) table.4 Set the detail dataset s DataSource property to CustomerSource.Setting thisproperty makes the detail dataset a linked query.At runtime the :ID parameter in the SQL statement for the detail dataset is notassigned a value, so the dataset tries to match the parameter by name against acolumn in the dataset identified by CustomersSource.CustomersSource gets its datafrom the master dataset, which, in turn, derives its data from the Customer table.Because the Customer table contains a column called ID, the value from the IDfield in the current record of the master dataset is assigned to the :ID parameter forthe detail dataset s SQL statement.The datasets are linked in a master-detailrelationship.Each time the current record changes in the Customers dataset, thedetail dataset s SELECT statement executes to retrieve all orders based on the currentcustomer id.Preparing queriesPreparing a query is an optional step that precedes query execution.Preparing aquery submits the SQL statement and its parameters, if any, to the data access layerand the database server for parsing, resource allocation, and optimization.In somedatasets, the dataset may perform additional setup operations when preparing thequery.These operations improve query performance, making your applicationfaster, especially when working with updatable queries.An application can prepare a query by setting the Prepared property to True.If you donot prepare a query before executing it, the dataset automatically prepares it for youeach time you call Open or ExecSQL.Even though the dataset prepares queries foryou, you can improve performance by explicitly preparing the dataset before youopen it the first time.CustQuery.Prepared := True;24-48 Dev el oper s Gui de Us i n g q u e r y - t y p e d a t a s e t sWhen you explicitly prepare the dataset, the resources allocated for executing thestatement are not freed until you set Prepared to False.Set the Prepared property to False if you want to ensure that the dataset is re-preparedbefore it executes (for example, if you add a parameter).Note When you change the text of the SQL property for a query, the dataset automaticallycloses and unprepares the query.Executing queries that don t return a result setWhen a query returns a set of records (such as a SELECT query), you execute thequery the same way you populate any dataset with records: by setting Active to Trueor calling the Open method.However, often SQL commands do not return any records.Such commands includestatements that use Data Definition Language (DDL) or Data ManipulationLanguage (DML) statements other than SELECT statements (For example, INSERT,DELETE, UPDATE, CREATE INDEX, and ALTER TABLE commands do not returnany records).For all query-type datasets, you can execute a query that does not return a result setby calling ExecSQL:CustomerQuery.ExecSQL; { query does not return a result set }Tip If you are executing the query multiple times, it is a good idea to set the Preparedproperty to True.Although the query does not return any records, you may want to know the numberof records it affected (for example, the number of records deleted by a DELETEquery).The RowsAffected property gives the number of affected records after a call toExecSQL
[ Pobierz całość w formacie PDF ]