VB.NET Winforms Dataset/Datatable offline SQL JOIN

I am trying to figure out how to perfom a SQL join on a dataset in VB.NET WInforms.  In my SQL SERVER database, I have these 3 tables (fields simplified):

      T1 (ID, colA)
      T2 (ID, ID2, colB)
      T3 (ID2, colC)

and view vwFinal, which is defined as:

      SELECT ID, ID2, colA, colB, colC
      FROM T1 INNER JOIN T2. on T1.ID = T2.ID
      INNER JOIN T3 on T2.ID2 = T3.ID2
(BTW, the second INNER JOIN actually has a double JOIN ... join on 2 different identity fields)


In my VB.NET Winforms application, I have connected to the Development database, and created a strongly-typed dataset ds with the 3 tables and the single view.  The accompanying tableadapters have been automatically created.
In the Production version of the databse, I would like like to leave out the view.  

I am able to fill the 3 tables with the table adapters, but instead of allowing the database server to perform the JOIN as above to fill the vwFinal, can I do it in VB.NET code so that I can insert the results of the JOINed rows into the ds.vwFinal datatable so that I can:

       1) display the data in a DataGrid View
       2) display the results in a Report.

I have heard of using LINQ to SQL, but I could not find an easy example.  Is there a relatively easy way to handle this?

Thanks.
eeyoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
Here is a console example based upon your listed requirements:
Imports System.ComponentModel
Imports System.Runtime.CompilerServices

Module Module1
    Private ds As New DataSet

    Sub Main()
        ds.Tables.AddRange(New List(Of DataTable) From {
            (From i In Enumerable.Range(0, 10) Select New With {.ID = i, .ColA = $"ColA_{i}"}).ConvertToDataTable("T1"),
            (From i In Enumerable.Range(0, 10) Select New With {.ID = i, .ID2 = i * 3, .ColB = $"ColB_{i * 2}"}).ConvertToDataTable("T2"),
            (From i In Enumerable.Range(0, 10) Select New With {.ID2 = i * 3, .ColC = $"ColC_{i * 3}"}).ConvertToDataTable("T3")
        }.ToArray())

        Dim query = From r_T1 In ds.Tables("T1").AsEnumerable()
                    Join r_T2 In ds.Tables("T2").AsEnumerable() On r_T2.Field(Of Integer)("ID") Equals r_T1.Field(Of Integer)("ID")
                    Join r_T3 In ds.Tables("T3").AsEnumerable() On r_T3.Field(Of Integer)("ID2") Equals r_T2.Field(Of Integer)("ID2")
                    Select New With {
                        .ID = r_T1.Field(Of Integer)("ID"),
                        .ID2 = r_T2.Field(Of Integer)("ID2"),
                        .ColA = r_T1.Field(Of String)("ColA"),
                        .ColB = r_T2.Field(Of String)("ColB"),
                        .ColC = r_T3.Field(Of String)("ColC")
                    }

        Dim view As New DataView(query.ConvertToDataTable("vwFinal"))
        view.Table.PrintToConsole()
        Console.ReadLine()
    End Sub
End Module

Module Extensions
    <Extension()>
    Sub DrawHorizontalSeparator(ByVal width As Integer, ByVal separator As Char)
        Console.WriteLine(New String(separator, width))
    End Sub

    <Extension()>
    Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T), Optional ByVal name As String = Nothing) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table As DataTable = New DataTable(If(Not String.IsNullOrWhiteSpace(name), name, String.Empty))

        For i As Integer = 0 To properties.Count - 1
            Dim [property] As PropertyDescriptor = properties(i)
            If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
                table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
            Else
                table.Columns.Add([property].Name, [property].PropertyType)
            End If
        Next

        Dim values(properties.Count - 1) As Object
        For Each item As T In source
            For i As Integer = 0 To properties.Count - 1
                values(i) = properties(i).GetValue(item)
            Next
            table.Rows.Add(values)
        Next

        Return table
    End Function

    <Extension()>
    Public Sub PrintToConsole(ByVal table As DataTable)
        Dim width = (25 * table.Columns.Count) + table.Columns.Count
        Console.WriteLine("Table Name: {0}", table.TableName)
        width.DrawHorizontalSeparator("=")
        Console.WriteLine("|{0}|", String.Join("|", table.Columns.Cast(Of DataColumn)().Select(Function(x) String.Format("  {0}  ", x.ColumnName).PadRight(25))))
        width.DrawHorizontalSeparator("=")
        For Each row As DataRow In table.Rows
            Console.WriteLine("|{0}|", String.Join("|", row.ItemArray.Select(Function(x) String.Format("  {0}  ", x.ToString()).PadRight(25))))
        Next
        width.DrawHorizontalSeparator("-")
    End Sub
End Module

Open in new window

Produces the following output -Capture.PNG
-saige-
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eeyoAuthor Commented:
Thanks, saige.  This seems to work pretty well.  

        Dim query = From r_T1 In ds.Tables("T1").AsEnumerable()
                    Join r_T2 In ds.Tables("T2").AsEnumerable() On r_T2.Field(Of Integer)("ID") Equals r_T1.Field(Of Integer)("ID")
                    Join r_T3 In ds.Tables("T3").AsEnumerable() On r_T3.Field(Of Integer)("ID2") Equals r_T2.Field(Of Integer)("ID2")

Open in new window

For this part of the query, how do I make one part LEFT JOIN?  I couldn't figure out where to put the "DefaultIfEmpty()" or does that require a different syntax?
And to make it a double, would I just add another EQUALS statement, separated by an AND?
0
eeyoAuthor Commented:
I was able to get the above code to work, but I am stuck with my actual VB.Net Winform.  I have created a dataset based on SQL Server Tables, and I am able to call the tableadapters to fill the tables.  When I try to substitute my dataset, I get an error.  Here is what I have so far, simplified to simple JOIN between 2 tables:
Dim query =
	 From T1 In ds.T1
	 Join T2 In ds.T2 On T1.ID Equals T2.ID
	 Select T1.ID, T2.ColA, T2.colB

Open in new window

The problem now is that I can't get the data from "query" into my strongly typed datatable "ds.vwFinal".  I have tried:
query.ConvertToDataTable

Open in new window

but I get dbnull conversion errors.  

In the module extensions, I have narrowed it down to this line that causes the conversion errors:
    Public Function ConvertToDataTable2(Of T)(ByVal source As IEnumerable(Of T), Optional ByVal name As String = Nothing) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table As DataTable = New DataTable(If(Not String.IsNullOrWhiteSpace(name), name, String.Empty))

        For i As Integer = 0 To properties.Count - 1
            Dim [property] As PropertyDescriptor = properties(i)
            If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
                table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
            Else
                table.Columns.Add([property].Name, [property].PropertyType)
            End If
        Next
        Dim values(properties.Count - 1) As Object
>>>        For Each item As T In source <<<
            For i As Integer = 0 To properties.Count - 1
                values(i) = properties(i).GetValue(item)
            Next
            table.Rows.Add(values)
        Next
        Return table
    End Function

Open in new window




Any thoughts?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

it_saigeDeveloperCommented:
So first question, what is the type being returned from query.  If you hold your mouse over query in the designer you should get an intellisense notification; e.g. -Capture.PNG
Second, what is the definition of your strongly-typed datatable?

And finally, what is the exception or error being thrown on the indicated line

One thing that does stand-out is that you did not *Select New With* in your query statement which is why I want to know the type returned from your query statement.

You also may be able to simply use CopyToDataTable since you are dealing with strongly-typed sets.

-saige-
0
eeyoAuthor Commented:
When I hover over query, I get the same type as in your screenshot: IEnumerable (Of 'a)

T1, T2, and T3 are strongly typed as in I created them in SQL Server, then dragged them over to the Dataset.xsd file, which automatically generates the code such as the .Fill command to load the data from SQL Server.

I added the *Select New With*.  At this point, the view is now working (I can see the appropriate data in the console/output window).  I just can't get the data directly in ds.vwFinal.  If I just load the data directly for SQL Server View using the .Fill command, it will load, but I want to use the above LINQ to SQL so that I can manipulate the data in the T1, T2, and T3 tables first before joining the tables.

I couldn't get CopyToDataTable to work off "query".
0
it_saigeDeveloperCommented:
How about the specification for the view?  What is it's configuration (or how did you achieve it).  I am going to try to create a small db on my side based off your example configuration(s) with 3 tables and the view.

-saige-
0
eeyoAuthor Commented:
I just used your original code verbatim:
 Dim view As New DataView(query.ConvertToDataTable("vwFinal"))

Open in new window

When I hover over "view", it's just "(local variable) view As DataView"
0
it_saigeDeveloperCommented:
My apologies, I meant the configuration for ds.vwFinal since this is where you are trying to place the query results.

-saige-
0
eeyoAuthor Commented:
After bumbling around, I was able to figure out the last part to getting the data back into the strongly typed datatable.   For anyone else who needs this, here is the code:
        Dim view As New DataView(query.ConvertToDataTable("vwFinal"))

        For Each dr As DataRow In view.ToTable.Rows
            ds.vwFinal.ImportRow(dr)
        Next dr

Open in new window

0
eeyoAuthor Commented:
My apologies, I meant the configuration for ds.vwFinal since this is where you are trying to place the query results.
No apologies needed.  You have helped me tremendously already.

Late entry, but here is the SQL statement for the SQL server view vwFinal:

SELECT        dbo.T1.ID, dbo.T2.ID2, dbo.T1.colA, dbo.T2.colB, dbo.T3.colC
FROM            dbo.T1 INNER JOIN
                         dbo.T2 ON dbo.T1.ID = dbo.T2.ID LEFT OUTER JOIN
                         dbo.T3 ON dbo.T2.ID2 = dbo.T3.ID2

Open in new window

And the hover-over details:
Readonly Property ds.vwFinal as ds.vwFinalDataTable
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.