none
Query between two Datatables RRS feed

  • Question

  • Hello

    I'm very new to using Datasets and Datatables so forgive my ignorance; I have extensively searched through the forums but am having problems working out the best way forward. I have two Datatables in an application which are derived from an SQL Data Connection using stored SQL queries. The two tables are dtIncrementComplianceManagers and dtIncrementCompliance. 

    At run time, I need the application loop through the rows of dtIncrementComplianceManagers and each time create a new datatable which contains all the rows in dtIncrementCompliance that share the ManagerEmail field with the current row in dtIncrementComplianceManagers . This would be used to compose an email to the manager regarding their staff and the application would then move on to the next manager. 

    As an SQL query it would look like this 

    SELECT A.* 
    FROM dtIncrementComplianceas A, dtIncrementComplianceManagers as B 
    WHERE A.ManagerEmail = B.ManagerEmail

    I'm not sure how to user a query of the existing datatables to populate a new table. This is where it would be located:

        Public Sub RunManagerEmails()
            Me.DtIncrementComplianceManagersTableAdapter.Fill(Me.HRRecruitmentDataSet.dtIncrementComplianceManagers)
            Me.DtIncrementComplianceTableAdapter.Fill(Me.HRRecruitmentDataSet.dtIncrementCompliance)
            For Each Row As DataRow In HRRecruitmentDataSet.dtIncrementComplianceManagers.Rows
                <-----NEW CODE TO GO HERE---->
            Next Row
        End Sub

    Any help would be much appreciated. 

    Tuesday, March 17, 2015 6:28 PM

Answers

  • Hello Leo,

    >>I'm not sure how to user a query of the existing datatables to populate a new table. This is where it would be located

    You could use LINQ to DataSet which has a similar syntax with SQL query:

    LINQ to DataSet

    In your case, you could perform a JOIN operation:

    Query Expression Syntax Examples: Join Operators (LINQ to DataSet)

    For storing the result to a new datatable, you could use the CopyToDataTable:

    https://msdn.microsoft.com/en-us/library/bb386921(v=vs.110).aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 4:27 AM
    Moderator
  • Hello Leo,

    I don't know what your starting point is, but the purpose of SQL database is that there is not searched for this kind of data. 

    Relations must do the job. The best way to set a relation is in the database itself. The TableAdapterManager will than create code for you which is generated including the names which leads direct to collections of data you are searching for.

    I always try to avoid giving coding for your way, because that can lead to wrong use of database data (probably I'm not the only one and you got not get a way to do that).


    Success
    Cor

    Thursday, March 19, 2015 9:59 AM

All replies

  • Hi Leo,

    I am moving your thread into the ADO.NET DataSet Forum for dedicated support. Thanks for your understanding.

    Best Regards,

    Jack


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, March 18, 2015 9:28 AM
  • Thanks Jack. 
    Wednesday, March 18, 2015 12:17 PM
  • Hello Leo,

    >>I'm not sure how to user a query of the existing datatables to populate a new table. This is where it would be located

    You could use LINQ to DataSet which has a similar syntax with SQL query:

    LINQ to DataSet

    In your case, you could perform a JOIN operation:

    Query Expression Syntax Examples: Join Operators (LINQ to DataSet)

    For storing the result to a new datatable, you could use the CopyToDataTable:

    https://msdn.microsoft.com/en-us/library/bb386921(v=vs.110).aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 19, 2015 4:27 AM
    Moderator
  • Hello Leo,

    I don't know what your starting point is, but the purpose of SQL database is that there is not searched for this kind of data. 

    Relations must do the job. The best way to set a relation is in the database itself. The TableAdapterManager will than create code for you which is generated including the names which leads direct to collections of data you are searching for.

    I always try to avoid giving coding for your way, because that can lead to wrong use of database data (probably I'm not the only one and you got not get a way to do that).


    Success
    Cor

    Thursday, March 19, 2015 9:59 AM