Dataset question...

  • Thread starter Thread starter McGurk
  • Start date Start date
M

McGurk

Is there a simple way to fill a dataset with a proper subset of a
database? I can run a query on a database to get related data, but
the result is a single table with all columns mashed together. I want
to have a dataset with the related data, each in its original tables,
with relation objects and constraints intact. The only way I know how
to do it is to manually re-create the database objects I want in the
dataset, then run a query to get the parent data I want inserted into
the parent table of the dataset then loop thorugh the data creating
new queries to fill the related data in the child tables. Way
annoying. Please help!
 
This question has been asked and addressed (but probably not fully answered)
many times before. We (generally) concur that the "best" approach is to
fetch just the rows you need for the base table (like "FROM Lawyers where
State = 'NY' and Type = 'Cheap' ") so your client, the network and the
server won't be overwhelmed by the volume. Once a specific attorney (base
row) is chosen, do another SELECT for the child rows ("Invoices WHERE
LawyerID = @LawyerIDChosen") and possibly another SELECT (perhaps in the
same query) where the invoice items are returned. Add your relationships
(yes, this is a PIA), and navigate around. Sure, you can fetch more than one
Attorney's invoices, but as you widen the rowsets, the problems associated
with locking, scalability and other issues increase logarithmically.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I agree, Bill. And given the choice of using some great new single line
command to get what you want or using some method that contains a dozen
lines, the difference is often just keystrokes. Very often, the compiler
does the same thing you'd type in anyway, or very similar things. So, while
I generally search for new and better ways to do things I'm not sure about,
if there's not a lot written on how to do something easier or faster, then
it is likely that the just typing in the sequence of commands is a
reasonable way to do it even in production.

IMHO.

Dale
 
Hi Mc,

You mean something as this?

Cor

\\\
Dim Sql As String = "SELECT * from A, B Where " & _
"A.n = B.n"
Dim Conn As New OleDbConnection(connString)
Dim da As New OleDbDataAdapter(Sql, Conn)
da.Fill(ds, "A")
da.Fill(ds, "B")
Conn.Close()
Dim drlA As New DataRelation _
("AA", ds.Tables("A").Columns("A.n"), _
ds.Tables("B").Columns("B.n"))
ds.Relations.Add(drlA)
Dim dv As New DataView(ds.Tables("A"))
DataGrid1.DataSource = dv
DataGrid1.Expand(-1)
///
 
Back
Top