Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Filtering and joining data from dataset and DataTables

Posted on 2008-06-24
Medium Priority
Last Modified: 2013-11-26
Using: VB.NET 2005

I have a Windows service that calls a stored procedure and then uses UDP to broadcast the resultset to listeners on a specific port. I also have a Windows application. Upon form load, the app calls a stored procedure to get the assigned "skills" (ID) for the current user and puts that data into a DataTable ("Table1"). The app then listens for data sent to the specific port and reads the data into a dataset ("DataSet1").

My dilemma is that I need to show this information in a datagridview. However, I first need to filter the contents of "DataSet1" to only records with the "skills" (ID) that I have been assigned in "Table1". Then, I need to bind this information to the datagridview in the format specified below. How should I do this? Examples, please?

I have read about data relations and merging but couldn't get the format to the way I want and got confused between the child/parent relations. I'm not even sure if I should have used a relation. Isn't there a way to do this in VB.NET 2005?
DataTable ("Table1") from Windows app: 
ID	Type		Col1		Col2			
----	---------	--------	---------
2	Blue		XYZ		ABC
4	Green		LMN		CDF
1	Orange		OPE		BGD
DataSet ("DataSet1") from Windows service:
ID	Part		Quantity	
----	---------	---------
1	425687		10
2	425659		25
3	395678		30
4	426589		50
5	423568		25
How the DataGridView should look: 
ID	Type	        Part		Quantity		
----	---------	---------	---------
2	Blue		425659		25
4	Green		426589		50
1	Orange		425687		10

Open in new window

Question by:blitzzy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 53

Accepted Solution

Dhaest earned 2000 total points
ID: 21863196
I've created the class below (called dataSetHelper)
I've created the tables and put them into a dataset.

Dim myData As New DataSet
myData.Tables.Add(FirstTable) ' --> table is called "table1", coming from Windows app
myData.Tables.Add(secondTable) --> table is called "table2", coming from Windows service
' Now create a relation between those 2 tables !
Dim myRelation As New DataRelation("FirstSecond", myData.Tables("table2").Columns("id"), myData.Tables("table1").Columns("id"))
' Call the join method
Dim dsHelper As New DataSetHelper(myData)
Dim dt As DataTable = dsHelper.SelectJoinInto("TestTable", myData.Tables("table1"), "ID,Type,FirstSecond.Part,FirstSecond.Quantity", "", "")
' To test it, I print the values on my debug-output
PrintValues(dt, "Result")

    Private Sub PrintValues(ByVal table As DataTable, ByVal label As String)
        ' Display the values in the supplied DataTable:
        For Each row As DataRow In table.Rows
            For Each col As DataColumn In table.Columns
                Console.Write(ControlChars.Tab + " " + row(col).ToString())
            Next col
        Next row
    End Sub

Public Class DataSetHelper
    Private Class FieldInfo
        Public RelationName As String
        Public FieldName As String
        'source table field name 
        Public FieldAlias As String
        'destination table field name 
        Public Aggregate As String
    End Class
    Public ds As DataSet
    Public Sub New(ByRef DataSet As DataSet)
        ds = DataSet
    End Sub
    Public Sub New()
        ds = Nothing
    End Sub
    Private m_FieldInfo As System.Collections.ArrayList
    Private m_FieldList As String
    Public Function CreateJoinTable(ByVal TableName As String, ByVal SourceTable As DataTable, ByVal FieldList As String) As DataTable
        If FieldList Is Nothing Then
            'return CreateTable(TableName, SourceTable); 
            Throw New ArgumentException("You must specify at least one field in the field list.")
            Dim dt As New DataTable(TableName)
            ParseFieldList(FieldList, True)
            For Each Field As FieldInfo In m_FieldInfo
                If Field.RelationName Is Nothing Then
                    Dim dc As DataColumn = SourceTable.Columns(Field.FieldName)
                    dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression)
                    Dim dc As DataColumn = SourceTable.ParentRelations(Field.RelationName).ParentTable.Columns(Field.FieldName)
                    dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression)
                End If
            If ds IsNot Nothing Then
            End If
            Return dt
        End If
    End Function
    Private Sub ParseFieldList(ByVal FieldList As String, ByVal AllowRelation As Boolean)
        If m_FieldList = FieldList Then
        End If
        m_FieldInfo = New System.Collections.ArrayList()
        m_FieldList = FieldList
        Dim Field As FieldInfo
        Dim FieldParts As String()
        Dim Fields As String() = FieldList.Split(","c)
        Dim i As Integer
        For i = 0 To Fields.Length - 1
            Field = New FieldInfo()
            'parse FieldAlias 
            FieldParts = Fields(i).Trim().Split(" "c)
            Select Case FieldParts.Length
                Case 1
                    'to be set at the end of the loop 
                    Exit Select
                Case 2
                    Field.FieldAlias = FieldParts(1)
                    Exit Select
                Case Else
                    Throw New Exception("Too many spaces in field definition: '" + Fields(i) + "'.")
            End Select
            'parse FieldName and RelationName 
            FieldParts = FieldParts(0).Split("."c)
            Select Case FieldParts.Length
                Case 1
                    Field.FieldName = FieldParts(0)
                    Exit Select
                Case 2
                    If AllowRelation = False Then
                        Throw New Exception("Relation specifiers not permitted in field list: '" + Fields(i) + "'.")
                    End If
                    Field.RelationName = FieldParts(0).Trim()
                    Field.FieldName = FieldParts(1).Trim()
                    Exit Select
                Case Else
                    Throw New Exception("Invalid field definition: " + Fields(i) + "'.")
            End Select
            If Field.FieldAlias Is Nothing Then
                Field.FieldAlias = Field.FieldName
            End If
    End Sub
    Public Function SelectJoinInto(ByVal TableName As String, ByVal SourceTable As DataTable, ByVal FieldList As String, ByVal RowFilter As String, ByVal Sort As String) As DataTable
        ' * Selects sorted, filtered values from one DataTable to another. 
        ' * Allows you to specify relationname.fieldname in the FieldList to include fields from 
        ' * a parent table. The Sort and Filter only apply to the base table and not to related tables. 
        Dim dt As DataTable = CreateJoinTable(TableName, SourceTable, FieldList)
        InsertJoinInto(dt, SourceTable, FieldList, RowFilter, Sort)
        Return dt
    End Function
    Public Sub InsertJoinInto(ByVal DestTable As DataTable, ByVal SourceTable As DataTable, ByVal FieldList As String, ByVal RowFilter As String, ByVal Sort As String)
        If FieldList Is Nothing Then
            'InsertInto(DestTable, SourceTable, RowFilter, Sort); 
            Throw New ArgumentException("You must specify at least one field in the field list.")
            ParseFieldList(FieldList, True)
            Dim Rows As DataRow() = SourceTable.[Select](RowFilter, Sort)
            For Each SourceRow As DataRow In Rows
                Dim DestRow As DataRow = DestTable.NewRow()
                For Each Field As FieldInfo In m_FieldInfo
                    If Field.RelationName Is Nothing Then
                        DestRow(Field.FieldName) = SourceRow(Field.FieldName)
                        Dim ParentRow As DataRow = SourceRow.GetParentRow(Field.RelationName)
                        DestRow(Field.FieldName) = ParentRow(Field.FieldName)
                    End If
        End If
    End Sub
End Class

Open in new window

LVL 53

Expert Comment

ID: 21863719
Another possibility (and shorter) is to use LINQ
        Dim combinedRows = From p1 In FirstTable.AsEnumerable() Join p2 In secondTable.AsEnumerable On p1("id") Equals p2("id") Select p1, p2
        Dim dt As DataTable = New DataTable
        Dim dcId As DataColumn = New DataColumn("ID", GetType(System.Int32))
        Dim dcType As DataColumn = New DataColumn("Type", GetType(System.String))
        Dim dcPart As DataColumn = New DataColumn("Part", GetType(System.String))
        Dim dcQuantity As DataColumn = New DataColumn("Quantity", GetType(System.String))
        'table is made, now just take and insert your values into the new DataTable
        For Each v In combinedRows
            Dim dr As DataRow = dt.NewRow
            dr("ID") = v.p1("ID")
            'this is in v.p1
            dr("Type") = v.p1("Type")
            dr("Part") = v.p2("Part")
            'notice we are looking in v.p2 here now
            dr("Quantity") = v.p2("Quantity")

Using LINQ in Visual Studio 2005

Author Comment

ID: 21865717
Dhaest, thanks for the detailed code sample. We are still only on .NET Framework 2.0 so I can't use LINQ, but I really appreciated learning something new. Perhaps when we eventually upgrade to .NET 2008 and Framework 3.5 then I can utilize LINQ.

Since my original post, I have gotten the datagrid to display the information that I needed. Basically, I add the column data that I need from my Windows app table and the dataset from the service into 2 new tables. I set a primary key value for both tables and then merge them. The DataSource for the datagridview is the merged table.

I'm curious to see if your first method may be faster in doing the same, so I will try it and let you know. Which method do you think should be faster and more reliable?

Author Closing Comment

ID: 31470300
Made some revisions to your example and it works great. Thanks again! I'll have to give LINQ a try as well.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question