Best approach for Child Data

[Arg81]

Ok, may come across a bit dumb but I've been sat thinking on this.

My search form can call 4 different ways to fill - @AccountManager, @Technician, @WorkID or @CustomerID

The searches (except @WorkID) will bring back more than 1 row. Some Account Managers still have +50 active projects.

In my previous app (.net 1.1) and at the start of this app I just loaded the child data after the search, using @WorkID - so only the related child data was loaded.
To overcome selecting different rows, I re-ran the query on the Click event of each navigational button.

Now, is this the best way to access child data? Although the table has quite a lot of rows, the data being recorded isn't that large.
I've now set it so that all rows from the child table are filled on Form_Load. Then no matter what search you use, no matter how many times you press the navigational buttons, the child data will always appear in the grid for the correct WorkID because I have the grid set to display the relation.

I've never played around with Stored Procedures, maybe that is ultimately the best way?

I have roughly 30 users across 3 sites. Normally the maximum number on at any one time is 10 or less because of time differences.

Just wanting advice as to what is the best approach. I've stuck with option 2 for now (fill all rows on form load) because if a search yields 50 results and someone wants to look through all of them, the application is not having to connect and pull the data from SQL every time they click Next or Previous......

Thanks!

 

[cjard]

In my app, i've chosen a different route all together

Suppose your database has 10000 parent rows and 10000 child work items
You appear to be choosing between:

On parent click, download child data (1 parent, download 1 child)
Download all chiuld data before parents, let datarelation do the work (download 10000 children, then each parent is guaranteed to match without
Create a view that joins child data in and use the view instead (download just 1 result)

The approach you havent considered, i think, is one that I ultimately took when facing a similar wondering:

Load X parent records (load 50 parents)
Iterate each and load their child records (load 50 children)
Let datarelation do the work


This way youre not downloading ALL work items, Youre only downloading the details for the 50+ items this supervisor has open. It's really quite quick, but you can use a backgroundworker to load the items if it becomes a long job.. just make sure you use the child primary key. If your database driver supports bind arrays (like oracle does) then you can submit all the child PKs in an array and get it to run multiple statements per single execution, but with prepared statements (like the datasets use) youll be amazed how quick it is.. certainly thousands of records a second in my app (faster than a datatable can draw them on screen)

 

[Arg81]

Cool, thanks for the info.

I'm using SQL Server 2000 as the backend.

I was thinking of creating a view / stored procedure, but wasn't sure how I would then run updates and inserts.

Sorry for the next dumb question, but what exactly do you mean iterate each parent record and load their child?
The only link I have between parent and child is the ProjectID field.

So if I load Projects for Supervisor A - they may be projectIDs 30000, 30010, 30020, 30021, 30050.

Basically the @Parameter is selected (supervisor A in this case), and when search is clicked it loads the parent data matching that parameter.
How do I then get the query to only load the child data for those ID's there and then?

 

[cjard]

If you had a stored procedure or view, then you would simply write update statemetns that either ran SQLSs or called stored procedures that updated the relevant data.. its not so different from a single table when you think about it - if a sproc is based on 3 tables, and you write an update that updates all 3 at the same time, or you write 3 updates that update each one, then nex ttime you call the sproc to read, it will show changed data..

Typically in my apps ive gone the other way round - I write normal selects and use sprocs for update and insert because i can control and validate the data on the server side, thereby allowing for changes without rollout and as a final security measure.

As regards selecting the parent, then the children, have a look at this code:

    Public Sub ShowBankee(ByVal bankeeRef As String, ByVal clearFirst As Boolean)


        If clearFirst Then
            HubDSInstance.DDH_BANKEES.Clear()
            HubDSInstance.DDH_BANKS.Clear()
            HubDSInstance.DDH_ADDRESSES.Clear()
        End If


        Try
            HubDSInstance.DDH_BANKEES.BeginLoadData()
            DDH_BANKEESTableAdapter.FillByBankeeRef(HubDSInstance.DDH_BANKEES, bankeeRef)
            HubDSInstance.DDH_BANKEES.EndLoadData()

            HubDSInstance.DDH_BANKS.BeginLoadData()
            For Each r As HubDS.DDH_BANKEESRow In HubDSInstance.DDH_BANKEES
                If r IsNot Nothing Then
                    DDH_BANKSTableAdapter.FillBySortCode(HubDSInstance.DDH_BANKS, r.BANK_SORT_CODE)
                End If
            Next
            HubDSInstance.DDH_BANKS.EndLoadData()

            HubDSInstance.DDH_ADDRESSES.BeginLoadData()
            For Each r As HubDS.DDH_BANKSRow In HubDSInstance.DDH_BANKS
                If r IsNot Nothing Then
                    DDH_ADDRESSESTableAdapter.FillByAddressRef(HubDSInstance.DDH_ADDRESSES, r.ADDRESS_REF)
                End If
            Next
            HubDSInstance.DDH_ADDRESSES.EndLoadData()

        Catch ex As System.Exception
            ExceptionDuringDatabaseOperation(ex)
        End Try
    End Sub

A bankee is a person who has an associated bank - sort code, but they can have had a few banks in their history, so all the possible banks are loaded. Each bank can maybe have changed address too, so all the banks addresses are loaded

 

[Arg81]

ah ha.

I think something just clicked

Off to have a play....

 

[Arg81]

OK, I had a go but I only get Child Data appearing in my grid for the first ProjectID, no other Projects loaded show child data in the grid....Here is my code
(I use a variable to declare which parameter query to load - everything is fine if I load ALL child table rows on form load, so it's not an issue with that)
PS - DWRNumber is my ProjectID

[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR.BeginLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] blAccMngr = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWRTableAdapter.FillByAccountManager([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR, varAccountManagerID, varStatus)[/SIZE]
[SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] blTech = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWRTableAdapter.FillByTechnologist([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR, varTechnologistID, varStatus)[/SIZE]
[SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] blCust = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWRTableAdapter.FillByCustomerSite([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR, varSiteID, varStatus)[/SIZE]
[SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] blDWRNo = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWRTableAdapter.FillByDWRNumber([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR, varDWRNumber)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR.EndLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Revision.BeginLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] dsDevelopment.DWRRow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]IsNot [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Nothing [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWR_RevisionTableAdapter.FillByDWRNumber([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Revision, r.DWRNumber)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Revision.EndLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]MessageBox.Show(ex.Message)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]

 

[Arg81]

I've now tried extending this to include the time spent on each revision....again, if I load all data at Form_Load everything works OK, but thrying to get it to work like in your example just isn't displaying any child data - and no errors are showing either, which is what has confused me.

here's the full code;

[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR.Clear()[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Revision.Clear()[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Work.Clear()[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[COLOR=#0000ff][/COLOR] 
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR.BeginLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] blAccMngr = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWRTableAdapter.FillByAccountManager([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR, varAccountManagerID, varStatus)[/SIZE]
[SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] blTech = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWRTableAdapter.FillByTechnologist([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR, varTechnologistID, varStatus)[/SIZE]
[SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] blCust = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWRTableAdapter.FillByCustomerSite([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR, varSiteID, varStatus)[/SIZE]
[SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] blDWRNo = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWRTableAdapter.FillByDWRNumber([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR, varDWRNumber)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR.EndLoadData()[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Revision.BeginLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]For [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] dsDevelopment.DWRRow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]IsNot [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Nothing [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWR_RevisionTableAdapter.FillByDWRNumber([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Revision, r.DWRNumber)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Revision.EndLoadData()[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Work.BeginLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]For [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] dsDevelopment.DWR_RevisionRow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Revision[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]IsNot [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Nothing [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DWR_WorkTableAdapter.FillByDWRNumber([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Work, r.DWRNumber)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR_Work.EndLoadData()[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]MessageBox.Show(ex.Message)[/SIZE]
 
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]

I understand what you have suggested to do, that's what is annoying me. what I can't understand is why no data is showing, everything looks OK to me...

 

[cjard]

Note that ALL your tableadapters used here must be set to ClearBeforeFill = FALSE otherwise youll only see the last set of data loaded..

I suggest you single step it in the debugger and find out whether any data is being loaded or not...

 

[Arg81]

Resolved

thats what it was

Thanks for that, was expecting a very annoying Monday morning...