Help with entity/VB code to get data from multiple tables and set to string

I have 2 tables:

tblTeams (cols are ID, Team, RegionName (this is an int), URL)
and
tblRegions (cols are ID, RegionName)

I want to write some code that first puts every entry of tblRegions into a list
For each item in that list, I want to find each Team and URL from tblTeams where tblRegions.ID = tblTeams.RegionName and add the values to a string.

I have a model setup with the 2 tables and need to do this in VB.NET/Entity. I've gotten so far as to get the first part in a list and cycle through it, but I think I may be overcomplicated things. To help understand, I'm trying to make a dropdown navigation menu where the tblRegions.RegionName are the first level, and the tblTeams data are the submenu where the aforementioned where clause is met.

Honestly if I could just have a string that looked something like...

Region1 TeamR1a R1aURL TeamR1b R1bURL TeamR1c R1cURL Region2 TeamR2a R2aURL TeamR2b R2bURL Region3 TeamR3a R3aURL

That would actually work for me.
Mike MillerSoftware EngineerAsked:
Who is Participating?
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.

Nitin SontakkeDeveloperCommented:
I am not TOO sure if i clearly understand this, however, from whatever i understand this is pretty simple. Just one thing which is not clear to me. Do you want an entire result set in one go?

I would bite the chunk in small pieces. If you are proficient at it (very simple, actually) write a scalar function which would return the string you are referring to, something like this:

declare @returnValue varchar(max) = '';
select @returnValue += Team + ' ' + URL + ' '
from tblTeams
where RegionName = @RegionName;

Open in new window


where @RegionName will be passed as a parameter. Issue resolved for the 1 row in the table.

Should you need entire result set in one use following, assuming above query is in function fnGetTeams:

select RegionName, t.ReturnValue
from tblRegions r
outer apply [dbo].[fnGetTeams](r.RegionName) t

Open in new window


Please give more meaningful names.
Mike MillerSoftware EngineerAuthor Commented:
Step 1. Pull all ID from tblRegions
Step 2. Iterate through each result from step1, pull all Team and URL from tblTeams where tblTeams.RegionName = the ID being iterated through
Step 3. Add the values of Team and URL from step 2 to a string (i.e. strTest = strTest & teamvalue & urlvalue)

I for sure do NOT want everything in one go. I need to pull all of the regions and then each team associated with said region.
More meaningful...

Northeast
    Mets
    Yankees
    Red Sox
Southwest
   Diamondbacks
   Dodgers
Southeast
   Marlins
   Rangers
   Braves

Looking for Entity help here, not really SQL syntax. I'm very new to using data models. An example of doing this or something similar is what I'm looking for.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Mike, is this issue solved?
Mike MillerSoftware EngineerAuthor Commented:
Yes, my apologies. So I never really got an answer to my question. My main focus was to accomplish the request in the OP without having issues with leaving connections open due to heavy traffic. (Kind of unrelated, I was having some other issues with bootstrap recognizing the values resulting from my queries.) I thought going the Entity route would help with this. I ended up using the following code however, which has been working great.

        Dim strTeams = ""
        Dim dt, dt2 As New DataSet
        Dim i As Integer
        Using cnn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
            Using sqlAdp As SqlDataAdapter = New SqlDataAdapter("SELECT ID, RegionName from tblRegions WHERE Active = 'True' ORDER BY ListOrder", cnn)
                Try
                    cnn.Open()
                    sqlAdp.Fill(dt)
                Finally
                    cnn.Close()
                    cnn.Dispose()
                End Try
            End Using
        End Using

        Using cnn2 As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
            Using sqlAdp2 As SqlDataAdapter = New SqlDataAdapter("SELECT ID, RegionName, Team from tblTeams WHERE Active = 'Yes' ORDER BY ListOrder", cnn2)
                Try
                    cnn2.Open()
                    sqlAdp2.Fill(dt2)
                Finally
                    cnn2.Close()
                    cnn2.Dispose()
                End Try
            End Using
        End Using

        Dim table As DataTable = dt2.Tables(0)
        Dim foundRows() As DataRow

        For i = 0 To dt.Tables(0).Rows.Count - 1
            strTeams = strTeams & "<li class=""dropdown-submenu""><a href=""#"" class=""test"">" & dt.Tables(0).Rows(i).Item(1) & "<span class=""caret""></span></a>"
            Dim expression As String = "RegionName = '" & dt.Tables(0).Rows(i).Item(0).ToString & "'"
            foundRows = table.[Select](expression)
            strTeams = strTeams & "<ul class=""dropdown-menu"">"
            For j = 0 To foundRows.GetUpperBound(0)
                strTeams = strTeams & "<li><a href = ""teampage.aspx?ID=" & foundRows(j)(0) & """>" & foundRows(j)(2) & "</a></li>"
            Next
            strTeams = strTeams & "</ul></li>"
        Next
        strTeams = strTeams
        litTeams.Text = strTeams

Open in new window

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
Mike MillerSoftware EngineerAuthor Commented:
Didn't get any answers, came up with an alternative solution.
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
Entity Framework Core

From novice to tech pro — start learning today.