none
Relationships with DataSet RRS feed

  • Question

  • Hello,

    I have questions about relationships with DataSet.

    I have a Dataset with several Datatable (Project, Companies, Activities, Persons) and join tables (PROJECT_COMPANIES_ACTIVITIES, COMPANIES_PERSONS). See picture.

    I created relationships between the tables represented in color on the picture.

    A ) For the chosen project, get the list of activities ?  : how to do ?                                

    PROJECT                       ACTIVITIES               

    School =>            Painting               Carpentry          

     

    B )For the project, get the list of people : how to do ?

    School => John, Superman, Toto

    (do DISTINCT because TOTO should only appear once)

     

    C) The result must be displayed in a DataGridView

    If there is a change in the tables, automatically update the result in the Datagridview ?

    Example :    ID_COMPANY = 3 with ID_PROJET = 1 (RED)                                                     

    ID_PROJECT

    ID_COMPANY

    ID_ACTIVITY

    1

    1

    1

    1

    3

    3

     
    Result :                School => John, Rambo, Toto    (do DISTINCT because TOTO should only appear once)

    how to do ?

    D) I found some of the solutions by adding columns to the PROJECT_COMPANIES_ACTIVITIES junction table

    Example:

    PROJECT_COMPANIES_ACTIVITIES.columns.add ( "ACTIVITIES", GetType (String) "PARENT (ACTIVITIES) .Name")

    Dim DTV = New DataView (PROJECT_COMPANIES_ACTIVITIES)

    Datagridview1.DataSource = DTV

    DTV.Rowfilter = String.format ("ID_PROJECT = {0}", ID_PROJECT)

    and I hide the other columns except the name of the activities

    but add columns to the junction table, I do not know if this is optimal if I have to add columns each time?

     

    E) What I did above, can solve my 2 points (For the project, get the list of people?) I do not see how to do ?


    F) What I ask above, we can very well do it via simple DataTables using filters via Dataview or select!

    Because I read a lot of information on the subject and it is often said that working with a DataSet slowed the performance as soon as the tables are large (loaded in memory)

    Should I use a dataset with relationships ??

     

    I ask a lot but I would like to start towards the right solution (optimum) so as not to have to change afterwards

    Thanks for your help.


    Steeve.

    sorry for my English


    Steeve

    Thursday, April 26, 2018 11:44 AM

All replies

  • There is a property for a DataSet. For example, customers to orders

    customerOrders.Relations.Add("CustOrders", customerOrders.Tables("Customers").Columns("CustID"), customerOrders.Tables("Orders").Columns("CustID"))

    In the following code sample I show setting up relations. This sample uses SQL-Server but the same holds true for MS-Access, MySql and Oracle too. Note in the code sample I use a language extension to create relations e.g.

    Public Module DataRelationsExtensions 
        ''' <summary> 
        ''' USed to create a one to many relationship for a master-detail in a DataSet. 
        ''' </summary> 
        ''' <param name="sender"></param> 
        ''' <param name="pMasterTableName">master table</param> 
        ''' <param name="pChildTableName">child table of master table</param> 
        ''' <param name="pMasterKeyColumn">master table primary key</param> 
        ''' <param name="pChildKeyColumn">child table of master's primary key</param> 
        <DebuggerStepThrough()> 
        <Runtime.CompilerServices.Extension()> 
        Public Sub SetRelation( 
            sender As DataSet,  
            pMasterTableName As String,  
            pChildTableName As String,  
            pMasterKeyColumn As String,  
            pChildKeyColumn As String) 
     
            sender.Relations.Add(New DataRelation( 
                String.Concat(pMasterTableName, pChildTableName), sender.Tables(pMasterTableName).Columns(pMasterKeyColumn),  
                sender.Tables(pChildTableName).Columns(pChildKeyColumn))) 
     
        End Sub 
     
    End Module 

    So in the following relationship I can reference it by ProductColor or ordinal index of the relations

    ds.SetRelation("Product", "ProductColor", "Id", "ProductId") 

    You can get at child rows in a relationship via GetChildRows. From the child, GetParentRow.

    Before getting into relations, setup the relations in the database e.g. in SQL-Server to test the relations. MS-Access has a similar method for setting up relations.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Padpanix Tuesday, May 15, 2018 10:20 AM
    • Unmarked as answer by Padpanix Tuesday, May 15, 2018 10:20 AM
    Thursday, April 26, 2018 12:12 PM
    Moderator
  • Many here can give you endless reply. But if we don't know what kind of database server (file) you use, then it is mostly very average help. 

    With SQL server you can use Linq to Entities, which I certainly would use then instead of a dataset.

    With MS Access which is a file, that is not possible and then you can with Microsoft products only use a dataset.

    But most of all definitely create the relations in your database itself.   

     


    Success
    Cor

    Thursday, April 26, 2018 2:35 PM
  • Hello,

    Thank you for your answers.

    I am using a file (serialize and deserialize dataset).It is a simplicity use but at first it is just to understand the mechanisms of relations in a Dataset.However, I still do not see the interest in visual ((with Table.select and filterow we can reach the same result)
    I did some testing with SQL Server Compact.

    Thx


    Steeve

    Tuesday, May 15, 2018 10:45 AM
  • Because I read a lot of information on the subject and it is often said that working with a DataSet slowed the performance as soon as the tables are large (loaded in memory)

    Should I use a dataset with relationships ??

     I ask a lot but I would like to start towards the right solution (optimum) so as not to have to change afterwards

    Thanks for your help.

    http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

    https://dzone.com/articles/reasons-move-datatables

    https://www.codeguru.com/vb/gen/vb_misc/oop/article.php/c7063/Data-Transfer-Object-Pattern-Goes-VBNET.htm

    https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/auto-implemented-properties


    Tuesday, May 15, 2018 12:21 PM
  • Hello,

    Thank you for your answers.

    I am using a file (serialize and deserialize dataset).It is a simplicity use but at first it is just to understand the mechanisms of relations in a Dataset.However, I still do not see the interest in visual ((with Table.select and filterow we can reach the same result)
    I did some testing with SQL Server Compact.

    Thx


    Steeve


    You should learn how to use SQL Command Objects and a custom object like a DTO in a collection and not use datasets and datatables, IMO.
    Tuesday, May 15, 2018 12:25 PM