In C# how to connect to fetch two tables without connecting to the database twice

I have 2 tables as follows -

table 1 -  modelno, eqno, modeldet, modelcolor,...etc
table 2 - modelno, eqno, partno, modelinfo1, modeldet, modelplace....etc

in table 1 modelno and eqno is composite key
in table 2 modelno, eqno and partno are composite primary key.

In C# - I made a connection to database first I fetch table 1 fields where clause based on modelno, then I have to fetch table 2 fields where clause based on modelno and eqno.

For example

table 1 -  modelno, eqno, modeldet, modelcolor
           1        1     redblue   red
           1        2     greenblue pink
           2        2     eeee      eeee 

table 2 - modelno, eqno, partno, modelinfo1, modeldet, modelplace
          1        1     1       ghhh         sss      ffff
          1        1     2       ffff         eee      rrrr
          2        1     1       dddd         ddd      dddd

I want to fetch first all the columns with modelno = '1' from table 1. and then foreach row fetched in above step get the rows from table2. example after fetching the rows from table1 we get 2 rows. then for first row fetched with modelid and eqno combination fetch the records from table2.

Is there a way in C# to connect to database only once and fetch the table1 and table2 information??? I dont want to connect twic开发者_Python百科e to database first to get the table1 and then to get the table2.

Use a JOIN in your SQL Query to do this:

SELECT * FROM Table1 as t1
LEFT JOIN Table2 as t2
ON t1.ModelNo = t2.ModelNo

This will return a row for every combination of Table 1 and table 2 based on a matching ModelNo

Think you are ab,e to retrieve data from both table. There is multiple result set concept at http://msdn.microsoft.com/en-us/library/ms378758(v=SQL.100).aspx.

This way you don't need to join the tables.