Question about Querying a dataset

[dsk96m]

So I added a datasource as a dataset to my project called ORAP_DS. It contains all the tables I need.

On a side not, I am trying to convert a vb6 program to vb.net. I have gotten the login to work and if I drag a table from the data source on the form, the data shows up. So I know the connections are working.

In vb6, there was some code:

        oracle_rs = New ADODB.Recordset

        sqlstr = "select distinct w.wsum_payyr from fset.tfsetwsum w order by 1 desc"
        oracle_rs.Open(sqlstr, oracle_conn)
        oracle_rs.MoveFirst()

        If oracle_rs.RecordCount <> 0 Then
            lngreccnt = oracle_rs.RecordCount
            mintmnuyrs = lngreccnt
            intcol = 0
            ReDim Preserve BaseYr(intcol)
            BaseYr(intcol) = CInt(Year(Date.Now))
            BaseYTD = BaseYr(intcol)
            frm_mainmenu.Year1ToolStripMenuItem.Text = BaseYTD
            Do Until oracle_rs.EOF
                If CInt(oracle_rs(0).Value) <> Year(Date.Now) Then
                    intcol = intcol + 1
                    ReDim Preserve BaseYr(intcol)
                    frm_mainmenu.Year1ToolStripMenuItem.DropDownItems.Add(oracle_rs(0).Value.ToString)
                    BaseYr(intcol) = CInt(oracle_rs(0).Value)
                End If
                oracle_rs.MoveNext()
            Loop
        Else
            ReDim Preserve BaseYr(0)
            BaseYr(0) = CInt(Year(Date.Now))
        End If

        oracle_rs.Close()

I know there are no equivalents to record sets in .net, so how would I go about converting this. I was thinking LINQ or something, but not sure how to do it uses the dataset that is in my project. Do I need to create code to connect to the server? I am just not sure how to query ORAP_DS. I want to do it the right way. Any pointers? I have read about LINQ to Dataset, but everything i see are examples where they create a new dataset in code before doing anything. Since my dataset already exists, what do I need to do?

 

[dsk96m]

Resolved in most part. I just added the appropriate tables tableadapter.

        Me.TFSETWSUMTableAdapter.Fill(Me.ORAP_DS.TFSETWSUM)

        Dim query = (From py In ORAP_DS.TFSETWSUM.AsEnumerable
                    Select py.WSUM_PAYYR Order By 1 Descending).Distinct

        For Each payyr In query
                 'do something
        Next

 

[dsk96m]

I have another sort of related question. More a LINQ question.
I have this sql:

        sqlstr = "SELECT   y.yapp_no pp,y.yapp_start_dt beg_dt,y.yapp_weekending_dt end_dt,y.yapp_yr yr," & vbCrLf
        sqlstr = sqlstr & "         NVL(d.a_hrs,0) avail " & vbCrLf
        sqlstr = sqlstr & "FROM     tdwhyapp y LEFT JOIN " & vbCrLf
        sqlstr = sqlstr & "         (SELECT   a.dypd_yapp_no pp,a.dypd_yapp_yr yr," & vbCrLf
        sqlstr = sqlstr & "                   SUM(8) a_hrs " & vbCrLf
        sqlstr = sqlstr & "          FROM     tdwhdypd a " & vbCrLf
        sqlstr = sqlstr & "          WHERE    dypd_weekend_ind='N' " & vbCrLf
        sqlstr = sqlstr & "                   AND a.dypd_yapp_yr BETWEEN " & BaseYTD - 1 & " AND " & BaseYTD + 4 & " " & vbCrLf
        sqlstr = sqlstr & "          GROUP BY a.dypd_yapp_no,a.dypd_yapp_yr " & vbCrLf
        sqlstr = sqlstr & "          ORDER BY 2,1 ASC) d ON y.yapp_no=d.pp AND y.yapp_yr=d.yr " & vbCrLf
        sqlstr = sqlstr & "WHERE    y.YAPP_YR BETWEEN " & BaseYr(UBound(BaseYr, 1)) & " AND " & BaseYr(LBound(BaseYr, 1)) & " " & vbCrLf
        sqlstr = sqlstr & "ORDER BY 4,1 ASC "

which I am trying to convert to LINQ. Below is what I have so far:

        Dim query = From y In ds.TDWHYAPP.AsEnumerable Join
                    d In (From a In ds.TDWHDYPD.AsEnumerable Select a.DYPD_YAPP_NO, a.DYPD_YAPP_YR
                          Where dypd_weekend_ind = "N" And DYPD_YAPP_YR >= BaseYTD - 1 And DYPD_YAPP_YR <= BaseYTD + 4
                          Order By 2, 1 Ascending Group By DYPD_YAPP_NO, DYPD_YAPP_YR Into Group)
                    On y.YAPP_NO Equals d.DYPD_YAPP_NO
                    Select y.YAPP_NO, y.YAPP_START_DT, y.YAPP_WEEKENDING_DT, y.YAPP_YR, 
                    Where y.YAPP_YR >= BaseYr(UBound(BaseYr, 1)) And y.YAPP_YR <= BaseYr(LBound(BaseYr, 1))
                    Order By 4, 1 Ascending

several things,
Can I do select abc as xyz or from above select y.yapp_start_dt beg_dt?
How do I convert NVL(d.a_hrs,0), which is related to the next question as well?
how do I do select sum(8) a_hrs?

What do I do with those?