Outer Join in SqldataAdapter

[Scott]

I need to use an OUTER JOIN in the SELECT statement of a
SqlDataAdapter to fill a DataSet. Currently the SELECT is
on a VIEW which contains the OUTER JOIN. This works well,
but I am having difficulty knowing how to update the data
source (SQL Server) when it comes time to commit the data
to the database. A standard UPDATE command doesn't work
on the view.

Does any one know of an example of filling a DataSet from
an outer join and then updating the database with the
changes in the DataSet?

Thanks,
Scott.

 

[Tim Stephenson]

One option would be to write a custom stored procedure to handle the update,
and then use that as the update command.
Alternatively, you could get the updated rows from the relevent dataset
/datatable and then write code to loop through each row in turn and run
whatever code or SP's you need to run to update the back-end datasource.

There's no real difference in filling a dataset from an outer join or view.
Either way you'll end up with a dataset containing a table with the rows
returned by SQL Server. The data aggregration takes place at the database
server, and all your dataadapter sees is the resulting result set (no pun
intended!).

 

[Scott]

Thanks Tim,
Sorry about the confusion with the view. I understand
there is no difference between an outer join and a view
that contains an outer join. The point I was trying to
make was that if I use the view, I am unable to use a
simple SQL UPDATE statement on the view.

I had used the stored procedure approach (mainly because
it seemed to involved less code) and it does work. My SP
checks whether the updated data needs to be inserted or
updated in the table that has been outer joined, and then
runs the appropriate INSERT or UPDATE SQL statement. My
original post was really just to find out if there was a
more elegant way of doing this. It would appear not.

Thanks again,
Scott.