C# Create a temporary table

gwarcher
gwarcher used Ask the Experts™
on
Greetings,

I am creating a brief application that is filling three seperate tables in a SQL server database.  Here is an example

Table 1
----------------------
ID    Name              VotesReceived              %OfVote         %Change
1     Bob                    100                                   51.0               0
2     Bob                     110                                   51.2                .2
3     Bob                     125                                  50.4                -.8

Table 2
-----------------
ID    Name              VotesReceived              %OfVote         %Change
1     Carol                  90                                      49.0               0
2     Carol                  95                                      49.1                .1
3     Carol                   115                                    49.8                .7

Table 3
----------------
ID       TimeReported         VoteTotal                TotalChange
1               11:00AM            190                                0
2                11:05AM            205                                15
3                11:10 AM             240                              35

I need to display the data in all three of these tables in a dataset that I would be able to scroll through using the ID field.  

What would be the best way to go about getting this information.  Should I re-adjust the application and place all the data into one table instead of three separate tables, or would it be more feasible to create a temporary table that would populate the data when the class is called?

Thanks for the help, data-driven applications are new to me in C# (heck C# is new to me)




Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
No need for Temp table.  just create a Store procedure that you C# application will call and inside the Store procedure put a SELECT statement that will send back the data the way you want.

you should provide us what the output you need.


Maybe something like this:
CREATE PROC procQueryTable
as 

SELECT 
	t1.id
	, t3.TimeReported
	, t3.VoteTotal
	, t3.TotalChange
	, t1.Name as NameTable1
	, t1.VotesReceived as VotesReceivedTable1
	, t1.[%OfVote] as [%OfVoteTable1]
	, t2.Name as NameTable2
	, t2.VotesReceived as VotesReceivedTable2
	, t2.[%OfVote] as [%OfVoteTable2]
FROM 
	table1 t1
	left join table2 t2
		on t1.id = t2.id
	left join table3 t3
		on t1.id = t3.id
 

RETURN 0

GO

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Making use of code from cboudroz

You may find it easier to use a table function, or a view that does the same thing.
Either allows you to use it as a "table" in a from clause so that you can use additional filters, whereas that would be harder to achieve in an SP.
-- table valued function (returns a table)
CREATE FUNCTION Tables123() returns TABLE AS RETURN
SELECT 
	t1.id
	, t3.TimeReported
	, t3.VoteTotal
	, t3.TotalChange
	, t1.Name as NameTable1
	, t1.VotesReceived as VotesReceivedTable1
	, t1.[%OfVote] as [%OfVoteTable1]
	, t2.Name as NameTable2
	, t2.VotesReceived as VotesReceivedTable2
	, t2.[%OfVote] as [%OfVoteTable2]
FROM 
	table1 t1
	left join table2 t2
		on t1.id = t2.id
	left join table3 t3
		on t1.id = t3.id
GO

-- This is a sample select statement with a filter
select * from Tables123()
where id between 1 and 3

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial