SqlDataAdapters are great. They provide a nice and maintanable way to get data from the database in and out of typed datasets. The drawback is that you need a new adapter for every new query. Usualy you need at least two select queries to read data, one to get an ordered list of rows and one to get a single (details) row. And often you need even more, one for all, one for a selection and one for a single row. This pattern is better recognized in VS 2005 which will generate overloaded methods to return selected data. But right now I had to add the possibility to select data in an existing 2003 app.
I could use a dataview but this would involve loading all data from the database before selecting. Instead of creating new adapters for all new queries I slightly modified my existring queries and the code using them. The Select sql went from
SELECT idMedewerker, MedewerkerNaam, LoginNaam, Email, Beheerder FROM Medewerkers
ORDER BY MedewerkerNaam
to
SELECT idMedewerker, MedewerkerNaam, LoginNaam, Email, Beheerder FROM Medewerkers
WHERE (Vestiging = @vestiging) OR (@vestiging IS NULL)
ORDER BY MedewerkerNaam
The WHERE clause uses a parameter (@vestiging) but will not check database values against it when that parameter is null. The decision to select is brought to the code
if (select)
mySqladapter.SelectCommand.Parameters["@vestiging"].Value = vestiging;
else
mySqladapter.SelectCommand.Parameters["@vestiging"].Value = DBNull.Value;
No rocket science but it works (on my database server). The good thing is that I do not have to create (and maintain !) new adapters and the dataset is unchanged so it will not break code. It has a little overhead on the sqlServer side, but I consider sqlServer samrt enough for the price to be neglectable.
Peter