VB.NET Winforms Dataset/Datatable offline SQL JOIN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

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?

ASKER

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

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

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

Any thoughts?

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

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-

ASKER

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

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-

ASKER

I just used your original code verbatim:

 Dim view As New DataView(query.ConvertToDataTable("vwFinal"))

When I hover over "view", it's just "(local variable) view As DataView"

My apologies, I meant the configuration for ds.vwFinal since this is where you are trying to place the query results.

-saige-

ASKER

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

ASKER

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