Data Access layer Advice

I've been following Soctt Mitchell's tutorials on Data Access and in Tutorial 1 (Step 5) he suggests using SQL Subqueries in TableAdapters in order to pick up extra information for display using a datasource.

I have two tables for a gallery system I'm building. One called Photographs and one called MS_Photographs which has extra information about certain images. When reading the MS_Photograph data I also want to include a couple of fields from the related Photographs table. Rather than creating a table adapter just to pull this data I wanted to use the existing MS_Photographs adapter with a query such as...

1SELECT CAR_MAKE, CAR_MODEL,2 (SELECT DATE_TAKEN3FROM PHOTOGRAPHS4WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID))AS DATE_TAKEN,5 (SELECT FORMAT6FROM PHOTOGRAPHS7WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID))AS FORMAT,8 (SELECT REFERENCE9FROM PHOTOGRAPHS10WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID))AS REFERENCE,11 DRIVER1, TEAM, GALLERY_ID, PHOTOGRAPH_ID12FROM MS_PHOTOGRAPHS13WHERE (GALLERY_ID = @.GalleryID)

This works but I wanted to know if there's a way to get all of the fields using one subquery instead of three? I did try it but it gave me errors for everything I could think of.

Is using a subquery like above the best way when you want this many fields from a secondary table or should I be using another approach. I'm using classes for the BLL as well and wondered if there's a way to do it at this stage instead?

Can't you simply right a query containing a join like so:

SELECTA.CAR_MAKE,A.CAR_MODEL,B.DATE_TAKEN, B.FORMAT,B.REFERENCE,A.DRIVER1,A.TEAM,A.GALLERY_ID,A.PHOTOGRAPH_IDFROMMS_PHOTOGRAPHSAS AINNERJOIN PHOTOGRAPHSAS BON B.PHOTOGRAPH_ID = A.PHOTOGRAPH_IDWHEREA.GALLERY_ID = @.GalleryID

|||

You can use a join but the tutorial explains that this affects the auto-generated methods for inserting, updating and deleting data using the table adapter.

|||

If I understand your problem correctly it sounds like what you want is a join (perhaps an outer join):

select a.car_make, a.car_model, b.date_taken, b.format, b.reference., etc
from ms_photographs a,
photographs b
where a.photographs_id = b.photographs_id
and a.gallery_id = @.GalleryID

The only issue is that the above query is an "inner" join which means that it will only return data that have matching rows in each table. If you might have data from ms_photographs that is not in photograps, then you need an "outer" join, which just means a change to the where clause as follows:

where a.photographs_id *= b.photographs_id
and a.gallery_id = @.GalleryID

There is an alternate syntax involving the use of the words LEFT OUTER JOIN (which is what the above example is) or RIGHT OUTER JOIN, but I prefer the *= syntax (only because I'm old and that's how I learned it This alternate syntax is the new standard, but the old way is still quite common. Here is the above rewritten to the new standard:

select a.car_make, a.car_model, b.date_taken, b.format, b.reference., etc
from ms_photographs a left outer join photographs b
on a.photographs_id = b.photographs_id
where a.gallery_id = @.GalleryID

BTW, the 2 syntaxes do not actually produce identical output in all cases, but the differences are very subtle and have to do with what happens if you include a where condition on the outer table -- it's not worth going into at this point

|||

Thanks for your suggestion. I can see how that would work but I'm trying to avoid it as the tutorial states that it will affect the other autogenerated statements in the tableadapter if i use a join.

Is there any sensible way to do this without a join or in the BLL?

Cheers

|||

nevets2001uk2:

Is there any sensible way to do this without a join or in the BLL?

I don't see how. Either you use a join or your do all the lookups yourself.

|||

nevets2001uk2:

I've been following Soctt Mitchell's tutorials on Data Access and in Tutorial 1 (Step 5) he suggests using SQL Subqueries in TableAdapters in order to pick up extra information for display using a datasource.

I have two tables for a gallery system I'm building. One called Photographs and one called MS_Photographs which has extra information about certain images. When reading the MS_Photograph data I also want to include a couple of fields from the related Photographs table. Rather than creating a table adapter just to pull this data I wanted to use the existing MS_Photographs adapter with a query such as......

You can have multiple select methods - for example, the GetAll and GetByID are common. So if you are not dealing with ginormous volumes and millions of hits, just stick the 3 subqueries in and see how it goes. I dont think SQL Server will physically read the second table three times per row. You can another select method without the subqueries for perfomance if you like. You don'tneed another adapter, though youcould have one if you wanted.

|||

Thanks for the advice. I'll stick with what I have for now and see how I go.