How can i select from more than one table in a SqlDataSource asp.net

Question

i have a web page, that will load data from three tables is there a way to load all the data at once using a Sqldatasource, or must i do it in coding?

i also want to sort it. but that i know how to do. and all the data goes into a gridview, which is why i want to use just one Sqldatasource.

this is what i have so far:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ 
ConnectionStrings:testpicsConnectionString %>" SelectCommand="SELECT * FROM [tball]
select * from tb1
select * from tb2"></asp:SqlDataSource>

and do i just add

sort id desc

to them all?

i just want to know can it be done with one Sqldatasource?

btw, I'm using c# and sql (obviously)

sorry forgot to add the schema from the tables

all the tables are the same: they have id, desc and pic

id int primary key identity not null,
[desc] varchar(max) null,
pic varbinary(max) null

that is used for all tables. so if i had to join, on where or what would i join?

Did you try with UNION in one Sqldatasource ? see my answer below. You could of course still use WHERE-clause when using unions. E.g. select... where id = xx... — MrSimpleMind
– MrSimpleMind, Commented Sep 14, 2013 at 10:03

MrSimpleMind · Accepted Answer · 2014-12-19 10:11:27Z

4

you could use UNION to select the columns from the different tables...

select * from tb1
union all 
select * from tb2
union all
select * from tb3
order by id desc;

union all - will show all records in your result.
union - will remove duplicate records from your result.

Union all will give better performance as the database engine won't have any overhead for managing the result to remove the duplicates.

edited Dec 19, 2014 at 10:11

answered Sep 14, 2013 at 9:55

MrSimpleMind

8,8376 gold badges44 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damith Over a year ago

1+ , The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION

Ghost93 · Accepted Answer · 2013-09-14 09:54:32Z

1

You can also create a view in your database that will collect all the data you need and then query it in your c# application.

answered Sep 14, 2013 at 9:54

Ghost93

1811 gold badge2 silver badges13 bronze badges

Comments

Ranjan Dudeja · Accepted Answer · 2013-09-14 10:16:16Z

0

Go here: Union

select * from Table1 union all select * from Table2 order by 1

answered Sep 14, 2013 at 10:16

Ranjan Dudeja

55 bronze badges

Collectives™ on Stack Overflow

How can i select from more than one table in a SqlDataSource asp.net

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related