SQL Server 2008 Master/Detail ( Using Delphi and ADOQuery )

1
votes

I have a SQL Server 2008 DataBase with 2 Table : Master and Detail

Master Fields : ID and ... ;

Detail Fields : ID , MasterID and ... ;

I have been set the relationship between Master and Detail in SQL Server

I want when I Select a record in Master DBGrid , Detail Query returns only records related to selected Master Record and Detail DBGrid Show only Related records

I dont want to Requery Detail ADOQuery everytime I select a Master Record with such as SQL Code ( for Example ) :

SELECT * FROM Detail WHERE MasterID = Master.ID

how can i do this without using ADOTable ( Master Source ) !?

in other words i want this Relationship be in DB Layer !

3
sql-server-2008delphidelphi-2010master-detailtadoquery
as I know, master-detail relations are commonly used with TTable descendant compoents. Do you actually need TADOQuery instead of TADOTable, which has MasterSource property? – teran
"I dont want to Requery Detail ADOQuery everytime ..." Why not? If Sql Server is the back-end, any other type of dataset you might use is going to have to do that under the hood anyway. – MartynA
"in other words i want this Relationship be in DB Layer !" Then set up a stored procedure (or table-UDF) on the server that takes the MasterID as input and returns the detail records. Your Delphi app is still going to have to invoke the stored proc/function, though. – MartynA

3 Answers

1
votes

If you want to flatten the master detail relationship, you can do a JOIN query to obtain all data from the 2 tables:

SELECT m.*, d.* FROM Master m INNER JOIN Detail d ON m.Id = d.MasterId
1
votes

You just need to set up detail DataSet as usual and change the SQL to a parameterized one. SELECT * FROM details WHERE MasterID =: id

This way only the corresponding details are loaded for each master record, which will throttle the load and keep displayed data more actual.

enter image description here

0
votes

You can use filter propery of Tadoquery on detail table.

add this code to AfterScroll event of AdoQueryMaster;

AdoQueryDetail.filter := 'Where MasterID = ' +inttostr(AdoQueryMaster.fieldbyname('ID').asinteger);
AdoQueryDetail.filtered := true;