[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Fill a dataset with multiple linked tables.

Posted on 2010-01-01
6
Medium Priority
?
362 Views
Last Modified: 2012-05-08
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?


   
0
Comment
Question by:shanemay
6 Comments
 

Author Comment

by:shanemay
ID: 26159777
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
 
LVL 29

Expert Comment

by:Kumaraswamy R
ID: 26159839
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 750 total points
ID: 26160033
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Assisted Solution

by:anasmohd
anasmohd earned 750 total points
ID: 26160036
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
 

Author Comment

by:shanemay
ID: 26161539
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
 

Author Closing Comment

by:shanemay
ID: 31671872
Thank you for your comments, time, and consideration on my behalf.  
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question