Fill a dataset with multiple linked tables.

I have a database with events, each event has multiple organizers and multiple classifications. I am wondering the best manner in which to fill a dataset with the data so I can map the dataset to my event class.  Any advice would be greatly appreciated.

A little framework - I have a eventID that is a foreign key in two tables one that contains organizerID and  the other that contains a classificationID.   I can easly query the database three times and get the data.  Is that the best way?  Or is it possible to get all the data in a single dataset?


   
shanemayAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
If you meant Multiple Linked tables as several tables within your database referenced using Foreign keys, then doing that in a single SELECT statement itself as mentioned in your comment should be efficient..
0
 
shanemayAuthor Commented:
Below is the query that I am using.  I used EVENT.* to shorten the code sample.  
SELECT     EVENT.*, EVENT_CLASSIFICATIONS.classificationID, EVENT_ORGANIZERS.organizerID
FROM         EVENT INNER JOIN
                      EVENT_CLASSIFICATIONS ON EVENT.eventID = EVENT_CLASSIFICATIONS.eventID INNER JOIN
                      EVENT_ORGANIZERS ON EVENT.eventID = EVENT_ORGANIZERS.eventID
ORDER BY EVENT.eventID, EVENT_CLASSIFICATIONS.classificationID, EVENT_ORGANIZERS.organizerID

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
anasmohdConnect With a Mentor Commented:
Create sql that fill the data set with data from the three tables. Fetching the data from each table is not practical and would be slower. use the folwoing fuction to get you ds.


Public Shared Function FillDataSet(ByVal connStr As String, ByVal sqlQuery As String) As DataSet
            Dim ds As DataSet = New DataSet()
            Try
                Dim cn As SqlConnection = New SqlConnection(connStr)
                Dim cmd As SqlCommand = cn.CreateCommand()
                cmd.CommandText = sqlQuery
                Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                da.Fill(ds)
                da.Dispose()
                cmd.Dispose()
                cn.Close()
            Catch sqlex As SqlException
                Throw sqlex
            Catch ex As Exception
                Throw ex
            End Try
            Return ds
        End Function

0
 
shanemayAuthor Commented:
Thank you for the responses.  I think I might be on the correct path.  However,
Filling the dataset in one sql statement is what I am doing currently, then I have a DataMap method that maps the data from the dataset into the Event Class.  However, with all the data contained in a single dataset, the logic is rather complex to ensure that the correct contributorIDs and classificationIDs are mapped into ArrayLists.  Because all the data is in the same dataset multiple copies of the data must be sorted and discarded.  Is there a better way to map the data?  
0
 
shanemayAuthor Commented:
Thank you for your comments, time, and consideration on my behalf.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.