Adding DataTables to Dataset

Colonel Angus · September 10th, 2004, 02:41 PM

I have a problem where I want to return the results of several data requests to report out in a table (in the UI, I'll use a repeater).

Normally in this situation I would just use cmd.Fill(myDataset, "tablename") for each data request, and return the dataset to the calling page.

However this time the individual rows in each table share a common link. I want to report them out one row at a time each. That is the first row in my UI will list out the first row in each of my dataset tables (4 of them).

Also to complicate the matter, I need to do this for 5 different groups of data requests. I don't think I can have a method return 5 datasets, so I'd like to use a naming scheme to differentiate between the 5 groups' tables.

Is this code appropriate to fill the table with data from the request and then add that table to the dataset:

Code:

Dim myTable As New DataTable

cmd.SelectCommand.CommandType = CommandType.StoredProcedure
cmd.SelectCommand.CommandText = "sp_GetMyData"

cmd.Fill(myTable)
cmd.Fill(result, "myTable")

(cmd being a SqlDataAdapter)

The page should look like this:

           tbl1 tbl2 tbl3 tbl4
           ---- ---- ---- ----
category1 28 17 9 11
category2 19 22 10 8
category3 37 0 2 17
    .
    .
    .

where vertically (tbl1, 2, etc) are shown the results of data requests (totals for each category), and there will be 4 more of these tables.

Thanks in advance!

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

planoie · September 10th, 2004, 04:48 PM

Is there a reason you can't write a query that builds this summary? Then you can return a single table of results instead of multiple tables and multiple dataset.

Colonel Angus · September 12th, 2004, 07:32 PM

Peter-

That was my original intention, but I think it's unlikely. Long story short, the 4 columns of data would involve 4 differing WHERE criteria; to the point where they are separate queries.

I will not discount the idea entirely however, I will be consulting with a DBA about that option. In the meantime, though I want to pursue this option. Even if a query solution can be found, I'm sure this technique may prove useful in the future.

-Colonel

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

planoie · September 12th, 2004, 07:50 PM

You should be able to create 4 sub queries with the aggregate logic as your 4 virtual table sources, then join those 4 tables together by category to get your final single result table of aggregates:

SELECT category, <t1 agg. col(s)>, <t2 agg. col(s)>, <t3 agg. col(s)>, <t4 agg. col(s)>
FROM
(SELECT category, <agg. col(s)> FROM table1 WHERE ... GROUP BY category) AS t1
JOIN (SELECT category, <agg. col(s)> FROM table2 WHERE ... GROUP BY category) AS t2 ON t1.category=t2.category
JOIN (SELECT category, <agg. col(s)> FROM table3 WHERE ... GROUP BY category) AS t3 ON t1.category=t3.category
JOIN (SELECT category, <agg. col(s)> FROM table4 WHERE ... GROUP BY category) AS t4 ON t1.category=t4.category
ORDER BY category

You may need to use LEFT or RIGHT joins in order to insure you don't loose rows due to missing categories in a table, but that will obviously depending on your data.

Colonel Angus · September 13th, 2004, 07:30 AM

It looks like the 4 queries are effectively temporary tables. I never thought of doing it that way. Thanks for the insight!

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.