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
  • 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…
Suggested Courses

627 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