How to use Strongly Typed dataset designer to configure multiple updates?

Question

I am using strongly-typed-dataset as an ORM to wrap around my Microsoft Access database, now I am looking for a way to create an equivalent of

UPDATE table1 SET table1.nationality = 'england'
WHERE table1.nationality in
(
 select table2.nationality from table2
 where table2.gender ='M'
);

in a strongly typed dataset designer, but not sure this is possible or not.

If this is not possible, what is the best way to accomplish this task? I am avoiding

1. Hand-code SQL
2. Store Proc

as much as I can.

Edit: I am not saying that hand-code SQL is not permissable, just that it's not desirable. The same goes for Store Proc

Answer 1

are you using a table adapter?

if so, can you not just right-click and choose Add Query, select new Select/Update/SQL statement, then enter

UPDATE table1 SET 
    table1.nationality = @nationality
WHERE table1.nationality in (
    select table2.nationality 
    from table2 
    where table2.gender = @gender
)

and give it an appropriate name? This should generate a table-adapter method like

updateNationalityByGender(string nationality, string gender)

you'll need to use the namespace for the table adapter to get access to it, e.g.

using your.name.space.datasetname.datasetnametableadapter;

Answer 2

LINQ to DataSet would be your best option.

1) Its all strongly typed.
2) You can use IQueryable/LINQ/lambda to query
3) You can bind to an Access database

Sql Express is free and it would be much better to use that. But if i was forced to use Access, this is how i'd do it.

http://msdn.microsoft.com/en-us/library/bb386977.aspx

Answer 3

Like steven A low said.. but you use MS acces.. so params must be '?'

Right-click adapter and choose Add Query, select new Select/Update/SQL statement, then enter

UPDATE table1 SET 
    table1.nationality = ?
WHERE table1.nationality in (
    select table2.nationality 
    from table2 
    where table2.gender = ?
)

give it an appropriate name, ie. updateNationalityByGender

updateNationalityByGender(string nationality, string gender)