Looking for an example

[M. Craig]

I'm trying to display multiple resultsets, returned in a SQLDataReader, in a
single table. The number of resultsets returned is variable, usually 3 or 4.
Basically, each resultset has 1 row and I'd like to display each row as a
row in the same table. I'd like to use the DataGrid if possible.

Here is my original idea:
1. Get returned DataReader
2. For each DataSet returned, create a DataGrid control, populate and
display
a. First DataGrid will have a header showing column names
b. Following DataGrids will have no header

Any ideas or places I can do more reading?

Thanks,

Mike

 

[Frank Oquendo]

Thus spake M. Craig:

Any ideas or places I can do more reading?

Is it not possible to use a single query to pull all the rows at once?
If not, you can use a DataAdapter to repeatedly fill a DataSet so long
as your selections come from the same table.

 

[William \(Bill\) Vaughn]

I would investigate the UNION query to create a single rowset on the server.
This would simplify the server-side of the operation as well as make it easy
to use the Fill method to construct the DataTable. I don't use the
DataReader to construct DataTable objects--it's too slow when compared with
Fill. The DataTable can be bound to the DataGrid quite easily.

hth

--
____________________________________
Bill Vaughn
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.
__________________________________

 

[M. Craig]

Thanks all for your suggestions.
I couldn't figure out how to do a UNION for this, but I came across an
article suggesting using #TempTables, which I thought was a GRAND idea.
Here is what I finished up with. If anyone would care to reword this Stored
Proc into something better, I'd be very appreciative!

Thanks again.

--Here's the result:
Promotion State Build Label
Date Started
-------------------------------------------------- -------------------------
------------------------- ------------
QA 01 corm_01_09_01_999
Sep 20 2003
Prod corm_01_09_01_001
Sep 8 2003

--And here is the query:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER procedure usp_GetBuildSummaryByCompId2
@CompID int =5

as

declare @EnvName varchar(25)
declare @EnvID int

declare Environments_Cursor CURSOR FOR

-- Create Temp table to hold Build Summary results

select e.EnvName , e.EnvironmentID from _ProductEnvironments pe
join _Environments e on pe.EnvironmentID = e.EnvironmentID
where CompID=5
order by LayerOrder

OPEN Environments_Cursor

--Do first Fetch
FETCH NEXT FROM Environments_Cursor
INTO @EnvName, @EnvID

-- Do first query to set up table
select top 1 e.EnvName as 'Promotion State', pb.BuildLabel as 'Build Label',
CONVERT(char(12), bp.PromotionDate) as 'Date Started' into #TempTable
from _products p
join _ProductComponents pc on p.ProductID = pc.ProductID
join _ProductBuilds pb on pc.CompID = pb.CompID
join _BuildPromotions bp on pb.BuildID = bp.BuildID
join _ProductEnvironments pe on bp.ProdEnvID = pe.ProdEnvID
join _Environments e on pe.EnvironmentID = e.EnvironmentID
where pc.CompID = @CompID and e.EnvironmentID= @EnvID
order by PromotionDate Desc

-- Get next Fetch
FETCH NEXT FROM Environments_Cursor
INTO @EnvName, @EnvID

-- Check Fetch status to see if there are more rows avail.
WHILE @@FETCH_STATUS = 0
BEGIN

-- Find out the build details for each layer
Insert Into #TempTable
select top 1 e.EnvName as 'Promotion State', pb.BuildLabel as 'Build
Label', CONVERT(char(12), bp.PromotionDate) as 'Date Started'
from _products p
join _ProductComponents pc on p.ProductID = pc.ProductID
join _ProductBuilds pb on pc.CompID = pb.CompID
join _BuildPromotions bp on pb.BuildID = bp.BuildID
join _ProductEnvironments pe on bp.ProdEnvID = pe.ProdEnvID
join _Environments e on pe.EnvironmentID = e.EnvironmentID
where pc.CompID = @CompID and e.EnvironmentID= @EnvID
order by PromotionDate Desc

-- Get Next EnvID
FETCH NEXT FROM Environments_Cursor
INTO @EnvName, @EnvID

END
select * from #TempTable
-- Close Cursor and clean up
CLOSE Environments_Cursor
DEALLOCATE Environments_Cursor
drop table #TempTable

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[M. Craig]

I couldn't figure out how to do this with one SQL Query, but I still wanted
to do most of the crunching on the SQL Server end of things.

See my reply to Bill Vaughn for what I came up with.

Thanks though,

Mike