Solved

Filtering and joining data from dataset and DataTables

Posted on 2008-06-24
4
2,414 Views
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

0
Comment
Question by:blitzzy
  • 2
  • 2
4 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 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"))
myData.Relations.Add(myRelation)
' 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:
        Console.WriteLine(label)
        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
            Console.WriteLine()
        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.")

        Else

            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)

                Else

                    Dim dc As DataColumn = SourceTable.ParentRelations(Field.RelationName).ParentTable.Columns(Field.FieldName)

                    dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression)

                End If

            Next

            If ds IsNot Nothing Then

                ds.Tables.Add(dt)

            End If

            Return dt

        End If

    End Function

    Private Sub ParseFieldList(ByVal FieldList As String, ByVal AllowRelation As Boolean)

        If m_FieldList = FieldList Then

            Return

        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

            m_FieldInfo.Add(Field)

        Next

    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.")

        Else

            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)

                    Else

                        Dim ParentRow As DataRow = SourceRow.GetParentRow(Field.RelationName)

                        DestRow(Field.FieldName) = ParentRow(Field.FieldName)

                    End If

                Next

                DestTable.Rows.Add(DestRow)

            Next

        End If

    End Sub
 

End Class

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
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))
        dt.Columns.Add(dcId)
        dt.Columns.Add(dcType)
        dt.Columns.Add(dcPart)
        dt.Columns.Add(dcQuantity)
        '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")
            dt.Rows.Add(dr)
        Next


Using LINQ in Visual Studio 2005
http://www.c-sharpcorner.com/uploadfile/nsatheeshk/linq06302006030119am/linq.aspx
0
 
LVL 1

Author Comment

by:blitzzy
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?
0
 
LVL 1

Author Closing Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now