none
.net (4) - n:many table , 1 tableAdapter.FillByPrimaryKey() , wrong output (tomany) RRS feed

  • Question

  • I will try to explain what i'm doing and what I want.

    I have a xaml output with 2 datagrids everything is working oke when using it in other ways, then what I want.

    I define this

    private registratieDataSetTableAdapters.klantTableAdapter klantTableAdapt = new registratieDataSetTableAdapters.klantTableAdapter();
    private registratieDataSetTableAdapters.domeinnamenTableAdapter domeinTableAdapt = new registratieDataSetTableAdapters.domeinnamenTableAdapter();
    
    private registratieDataSet registratieDataSet = new registratieDataSet();
    

    I did made with the wizzard a registratieDataSet.xsd
    The main table is [klant]
    It has a primary key [debnr]

    I made a query FillByDebnr() to get only the records using a [debnr]

    Then there is a second table [domeinnamen]
    With a other primary key, and a Foreign relation klant.debnr == domeinnamen.debnr (1:to many)

    So the output will be this:
    [klant]
    12233 | bla ala | test bla lala
    12234 | vla | test

    [domeinnamen]
    12234 | gama | test
    12234 | gama2 | testd
    12234 | vala | test
    12233 | bbb


    Now I'm using 1 DataContext, that is.

    this.DataContext = registratieDataSet;
    

    To fill this I'm using for example:
    klantTableAdapt.FillByDebnr(registratieDataSet.klant, 12234);
    domeinTableAdapt.FillByDebnr(registratieDataSet.domeinnamen, 12234);

    As you can see i'm not using the foreignkeys to fill my registratieDataSet using only this:
    klantTableAdapt.FillByDebnr(registratieDataSet.klant, 12234);

    I'm only want to use klantTableAdapt.FillByDebnr(registratieDataSet.klant, 12234); he know that there is a domeinnamen table, but that displays all rows, not only debnr: 12234

    I did try stupid things like this:
     domeinTableAdapt.Fill((registratieDataSet.domeinnamenDataTable)registratieDataSet.klant.FindBydebnr(12234).Table.Rows.Find(12234).Table.ChildRelations["FK_domeinnamen_klant"].ChildTable);
    OR
    domeinTableAdapt.Fill((registratieDataSet.domeinnamenDataTable)registratieDataSet.klant.FindBydebnr(12234).Table.ChildRelations["FK_domeinnamen_klant"].ChildTable.DataSet.Tables["domeinnamen"]);

    This is working but not Filling the registratieDataSet
    registratieDataSet.domeinnamenRow[] klant_domeinnamen;
    klant_domeinnamen = (registratieDataSet.domeinnamenRow[])registratieDataSet.klant.FindBydebnr(12234).GetChildRows("FK_domeinnamen_klant");

    So what now ?
    How to fill registratieDataSet with 1 fill command, using the highest primary table made in a .xsd file ?
    Or
    using the main TableAdapter in this case klantTableAdapt

    I hope you understand my question.

    Friday, October 22, 2010 12:58 PM

Answers

  • The way I did it is to create the additional Fill commands in your table adaptors. Go in your DataSet's designer. Under each table you have a "domeinTableAdapter" or a "klantTableAdapter" component. Right-click, select "Add->Query". Enter a query that takes into parameter the PK you're looking for (Something along the lines of "SELECT * FROM KLANT WHERE DEBNR = @inDebnr"). Call it FillByDebnr (like in your example). Do the same for your child table. You now have 4 fill queries. 2 for complete fills, 2 for PK specific fills.

    Additionally, if you want referential integrity to be maintained in your dataset, you can configure the FK component in the designer by double-clicking on it. Select the "Both Relation And Foreign Key" radio button. This has the added advantage of auto-updating any autoincrement PK's in your child tables during a hierarchical update.

    Friday, October 22, 2010 8:33 PM

All replies

  • The way I did it is to create the additional Fill commands in your table adaptors. Go in your DataSet's designer. Under each table you have a "domeinTableAdapter" or a "klantTableAdapter" component. Right-click, select "Add->Query". Enter a query that takes into parameter the PK you're looking for (Something along the lines of "SELECT * FROM KLANT WHERE DEBNR = @inDebnr"). Call it FillByDebnr (like in your example). Do the same for your child table. You now have 4 fill queries. 2 for complete fills, 2 for PK specific fills.

    Additionally, if you want referential integrity to be maintained in your dataset, you can configure the FK component in the designer by double-clicking on it. Select the "Both Relation And Foreign Key" radio button. This has the added advantage of auto-updating any autoincrement PK's in your child tables during a hierarchical update.

    Friday, October 22, 2010 8:33 PM
  • Yes, I know that already, but it it not weird that its not possible at all ?

    That we need 4 fill queries, and not 1 using the foreign keys and primary keys like we made using the database designer.

    When I look in the debugging mode and watch what is inside klantTableAdapt.FillByDebnr(registratieDataSet.klant, 12234);

    Then I see that he is already know about the foreign and primary keys.
    He already know what the child tables are, but he is not filling that.

    I did what you did by using morde fills like this:
    klantTableAdapt.FillByDebnr(registratieDataSet.klant, 12234);
    domeinTableAdapt.FillByDebnr(registratieDataSet.domein, 12234);

    But then you come to a point when its not working.
    For example I want to show a datagrid with some rowdetails (a second datagrid) I do something like this:
    [klant] onSelectionChange(){

    do a new fill because its a other [klant] / client
    domeinTableAdapt.FillByDebnr(registratieDataSet.domein, [theselected]);
    this.DataContext = registratieDataSet; // -- but nothing worked ! no update on the screen !
    }

    :S

    Saturday, October 23, 2010 6:25 PM
  • As to why DataAdaptors don't automatically fill child tables, I would guess that it's a question of flexibility. You might not ALWAYS want child tables to be filled, and you might not ALWAYS need a fillbyPK, so Microsoft's solution allows for maximum flexibility, even though that means we must code a little more to get exactly what we want.

    Your real issue might be cause by databinding though. You say there is no update on the screen. If you check in debug mode, is your registratieDataSet properly updated? If so your problem might only be that your datagrid needs to be refreshed to display the new values. I suggest you use databinding and the all-important BindingSource class. It will take care of updating your controls when your data changes, and is especially adept at handling DataSets.

    Monday, October 25, 2010 1:28 PM