[RESOLVED] Select data from 2 tables (without SQL connection)

[soft903]

Hi All,

My project is a standalone application with lots data retrieval and sometimes data update.
(No SQL server or connection provided)
Here is sample code

Dim objDS As New DataSet("Quesetion")
Dim dtBook As DataTable = objDS.Tables.Add("Book")
Dim dtChapter As DataTable = objDS.Tables.Add("Chpater")
Dim objDR As DataRow

With dtBook
    .Columns.Add("BookID", Type.GetType("System.Int32"))
    .Columns.Add("BookName", Type.GetType("System.String"))
End With

With dtChapter
    .Columns.Add("ChapterID", Type.GetType("System.Int32"))
    .Columns.Add("BookID", Type.GetType("System.Int32"))
    .Columns.Add("ChapterName", Type.GetType("System.String"))
End With

objDR = dtBook.NewRow()
objDR("BookID") = 1
objDR("BookName") = "VB .Net"
dtBook.Rows.Add(objDR)

objDR = dtChapter.NewRow()
objDR("ChapterID") = 1
objDR("BookID") = 1
objDR("ChapterName") = "Intro"
dtChapter.Rows.Add(objDR)

Dim expression As String = "ChapterName = 'Intro'"
Dim foundRows() As DataRow = dtChapter.Select(expression)

For Each tData As DataRow In foundRows
    Debug.Print(tData("BookID").ToString())
Next

I used DataSet and DataTable to store data
It works fine when retrieve data from single table
But when retrieve data across multiple tables
e.g. Get the name of book with ChapterName = 'Intro'
Things get compacted.
Silly way to do so:

For Each tData As DataRow In foundRows
    expression = "BookID = " & tData("BookID").ToString()
    foundRows2 = dtBook.Select(expression)
    For Each tData2 As DataRow In foundRows2
        Debug.Print(tData2("BookName").ToString())
    Next
Next

As DataTable.Select works only for that instance (which is very reasonable)
And seem DataSet doesn't have method like "Select"
Are there any way to retrieve data across multiple tables?
something like
SELECT BookName FROM Book, Chapter WHERE Book.BookID = Chapter.BookID AND Chapter.ChapterName = 'Intro'

OR should I use other data type / format to store the data ?

Thanks

[cicatrix]

Dataset doesn't have a method called SELECT indeed because a dataset is basically a collection of tables. The DataTable class on the contrary does have this method.

You can't join queries as you would normally do in SQL so you should perform queries in each datatable separately.
OR you can use LINQ but I doubt this will be easier.

[soft903]

Thanks for your quick reply.

I'm studying LINQ as you suggested.
Seem still something I capable to deal with.
And perfectly fit my needs.

Thanks again

[soft903]

Here is my code of above sample using LINQ, maybe helpful for someone

Structure TypeBook
    Public BookID As Integer
    Public Name As String
    Public Sub New(ByVal inID As Integer, ByVal inName As String)
        BookID = inID
        Name = inName
    End Sub
End Structure

Structure TypeChapter
    Public ChapterID As Integer
    Public BookID As Integer
    Public Name As String
    Public Sub New(ByVal inID As Integer, ByVal inBookID As Integer, ByVal inName As String)
        ChapterID = inID
        BookID = inBookID
        Name = inName
    End Sub
End Structure

Private Sub TestLINQ()
    Dim Books As New List(Of TypeBook)
    Books.Add(New TypeBook(1, "VB .Net"))

    Dim Chapters As New List(Of TypeChapter)
    Chapters.Add(New TypeChapter(1, 1, "Intro"))

    Dim queryBkName = From tBook In Books, tChapter In Chapters Where tBook.BookID = tChapter.BookID And tChapter.Name = "Intro" Select tBook.Name

    For Each tName In queryBkName
        Debug.Print("Book Name : " & tName)
    Next
End Sub