join 2 datatable using linq

[napii21]

Hi all,

i have 2 dataset and i want to join these dataset.

1st dataset:

Dim strSQL As String = "SELECT TblAuditTrail.TransID, TblTrans.TransItem AS summary, COUNT(TblAuditTrail.TransID) AS total FROM TblAuditTrail INNER JOIN TblTrans ON TblAuditTrail.TransID = TblTrans.TransID WHERE (TblAuditTrail.DateCreated BETWEEN '2012-10-11 00:00:00.000' AND '2012-10-18 00:00:00.000')GROUP BY TblAuditTrail.TransID, TblTrans.TransItem"


            Dim sqlcmd As New SqlCommand(strSQL, sqlConn)
            Dim da As New SqlDataAdapter(sqlcmd)
            Dim ds As New DataSet()
            da.Fill(ds, "total")

result :




2nd dataset :

 Dim strSQL1 As String = "SELECT TblAuditTrail.TransID, TblTrans.TransItem AS summary, COUNT(TblAuditTrail.TransID) AS date1 FROM TblAuditTrail INNER JOIN TblTrans ON TblAuditTrail.TransID = TblTrans.TransID WHERE (TblAuditTrail.DateCreated BETWEEN '2012-10-11 00:00:00.000' AND '2012-10-12 00:00:00.000')GROUP BY TblAuditTrail.TransID, TblTrans.TransItem"


            Dim sqlcmd1 As New SqlCommand(strSQL1, sqlConn)
            Dim da1 As New SqlDataAdapter(sqlcmd1)
            Dim dtDate1 As New DataTable()
            da.Fill(ds, "date1")

result:




i expect a result like this:





i tried to join these 2 datasets :

 ds.Locale = CultureInfo.InvariantCulture
           
            Dim total = ds.Tables("total").AsEnumerable()
            Dim date1 = ds.Tables("date1").AsEnumerable()




            Dim query = From t In total Join d1 In date1 On t("summary") Equals d1("summary") _
               Select New With _
    { _
        .summary = t.Field(Of Integer)("summary"), _
        .total = t.Field(Of Integer)("totalAll"), _
        .date1 = d1.Field(Of DateTime)("totalDate1")}




            Dim dtResult As DataTable = query




          gvSummWeek.DataSource = dtResult
                gvSummWeek.DataBind()

but it seems it didn't return any value. can anyone show me the right way to join these 2 dataset please.

 

[IanRyder]

Hi,

Following on from your last post, if you want to use LINQ to do this then have a look at this example. This simply joins two tables in a DataSet using Column 0 in each table as the relationship between the two tables and then returns a List of Anonymous Types which can then be used as the DataSource in the DataGridView.

Dim myListOfAnonymousTypes = (From T1 In ds.Tables(0) Join T2 In ds.Tables(1) On T1(0) Equals T2(0) Select ID = T1(0).ToString, FirstName = T1(1).ToString, LastName = T2(2)).ToList
DataGridView1.DataSource = myListOfAnonymousTypes

Hope that helps.

Cheers,

Ian

 

[Herman]

I might add that using Entity Framework with Linq makes a lot more sense than a dataset. It will still work but it's much less intuitive.

 

[napii21]

ok,this is my latest code and it still give nothing.no record displayed in gridview:

Dim strSQL As String = "SELECT TblAuditTrail.TransID as ID, TblTrans.TransItem AS summary, COUNT(TblAuditTrail.TransID) AS total FROM TblAuditTrail INNER JOIN TblTrans ON TblAuditTrail.TransID = TblTrans.TransID WHERE (TblAuditTrail.DateCreated BETWEEN '2012-10-11 00:00:00.000' AND '2012-10-18 00:00:00.000')GROUP BY TblAuditTrail.TransID, TblTrans.TransItem"

                    Dim sqlcmd As New SqlCommand(strSQL, sqlConn)
                    Dim daDetail As New SqlDataAdapter(sqlcmd)
                    Dim ds As New DataSet()
                    daDetail.Fill(ds.Tables("total"))


                    Dim strSQL1 As String = "SELECT TblAuditTrail.TransID as ID, TblTrans.TransItem AS summary, COUNT(TblAuditTrail.TransID) AS dateTotal FROM TblAuditTrail INNER JOIN TblTrans ON TblAuditTrail.TransID = TblTrans.TransID WHERE (TblAuditTrail.DateCreated BETWEEN '2012-10-11 00:00:00.000' AND '2012-10-12 00:00:00.000')GROUP BY TblAuditTrail.TransID, TblTrans.TransItem"


                    Dim sqlcmd1 As New SqlCommand(strSQL1, sqlConn)
                    Dim daDetail1 As New SqlDataAdapter(sqlcmd1)
                    daDetail1.Fill(ds.Tables("dateTotal"))


                    Dim myListOfAnonymousTypes = (From T1 In ds.Tables("total") Join T2 In ds.Tables("dateTotal") On T1("ID") Equals T2("ID") Select Summary = T1("summary").ToString, Total = T1("total").ToString, date1 = T2("dateTotal")).ToList


                    gvSummWeek.DataSource = myListOfAnonymousTypes
                    gvSummWeek.DataBind()

gvSummWeek is a gridview and this the markup code :

<asp:GridView ID="gvSummWeek" runat="server" AllowPaging="True" 
                            AutoGenerateColumns="False" PageSize="50" style="margin-right: 0px" Width="90%">
                            <PagerStyle BorderStyle="Solid" />
                            <HeaderStyle BackColor="AliceBlue" />
                            <Columns>
                                <asp:BoundField DataField="summary" HeaderText="Summary">
                                    <ItemStyle HorizontalAlign="Left" Width="20%" />
                                </asp:BoundField>
                                <asp:BoundField DataField="total" HeaderText="Total">
                                    <ItemStyle HorizontalAlign="Center" Width="10%" />
                                </asp:BoundField>
                                
                                   
                                <asp:BoundField DataField="date1" HeaderText="date1" />
                                
                                   
                            </Columns>
                        </asp:GridView>

where did i do wrong?

 

[napii21]

finally, i managed to show some output.
now i need to find LINQ for inner join.

current query is

Dim myListOfAnonymousTypes = (From T1 In ds.Tables("dateTotal") Group Join T2 In ds.Tables("Total") On T2("summary") Equals T1("summary") Into left1 = Group From T2 In left1.DefaultIfEmpty Select Summary = T2("summary"), Total = If(T2("total"), 0), date1 = If(T1("dateTotal"), 0))

then i need to inner join to multiple datasets.

 

[napii21]

everything works fine now. this the code.

Public Function fGetSumm2()


        Dim objConn As SqlConnection
        Dim objCmd As SqlCommand
        Dim strConnString As String = clsConn.ConnectionString


        Try


            Dim sqlConn As New SqlConnection(strConnString)


            If sqlConn.State = ConnectionState.Closed Then sqlConn.Open()


            Dim strSQL As String = "SELECT TblAuditTrail.TransID as ID, TblTrans.TransItem AS summary, COUNT(TblAuditTrail.TransID) AS total FROM TblAuditTrail INNER JOIN TblTrans ON TblAuditTrail.TransID = TblTrans.TransID WHERE (TblAuditTrail.DateCreated BETWEEN '2012-10-11 00:00:00.000' AND '2012-10-18 00:00:00.000')GROUP BY TblAuditTrail.TransID, TblTrans.TransItem"


            Dim sqlcmd As New SqlCommand(strSQL, sqlConn)
            Dim da As New SqlDataAdapter(sqlcmd)
            Dim ds As New DataSet()
            da.Fill(ds, "total")


            Dim strSQL1 As String = "SELECT TblAuditTrail.TransID as ID, TblTrans.TransItem AS summary, COUNT(TblAuditTrail.TransID) AS dateTotal FROM TblAuditTrail INNER JOIN TblTrans ON TblAuditTrail.TransID = TblTrans.TransID WHERE (TblAuditTrail.DateCreated BETWEEN '2012-10-11 00:00:00.000' AND '2012-10-12 00:00:00.000')GROUP BY TblAuditTrail.TransID, TblTrans.TransItem"


            Dim sqlcmd1 As New SqlCommand(strSQL1, sqlConn)
            Dim da1 As New SqlDataAdapter(sqlcmd1)
            Dim dtDate1 As New DataTable()
            da1.Fill(ds, "dateTotal")


            Dim myListOfAnonymousTypes = (From T1 In ds.Tables("Total") Group Join T2 In ds.Tables("dateTotal") On T1("summary") Equals T2("summary") Into right1 = Group From T2 In right1.DefaultIfEmpty Select Summary = If(right1 Is Nothing, String.Empty, T1("summary")), Total = If(right1 Is Nothing, String.Empty, T1("total")), date1 = If(T2 Is Nothing, 0, T2("dateTotal")))


            


                gvSummWeek.DataSource = myListOfAnonymousTypes
                gvSummWeek.DataBind()




            If sqlConn.State = ConnectionState.Open Then sqlConn.Close()


        Catch ex As Exception
            clslog.sErrorWriter("fGetSumm2 - " & ex.Message)
            Return Nothing
        End Try


    End Function

just 1 more question. if i put this function in a class and call it from web service, how do i return the value of 'myListOfAnonymousTypes' to gridview?