I've not been having a lot of luck finding a neat solution to this issue so I thought I'd give a question a try.
I have an ASP.NET web application accessing an MS SQL database. The Data Access Layer is all currently done using TableAdapters and for the most part they're working very nicely with the ObjectDataSource component (kind of skipped Business Logic in most cases).
I want to populate a few drop down boxes with SELECT DISTINCT queries. The problem is two-fold.
First I can't create those queries and execute them successfully within the schema for an existing Table Adapter (unique / not null constraints).
And secondly, I can't create another TableAdapter including only the columns without constraints, it crashes (An unexpected error has occurred) on the last step and won't create the FillBy and GetBy part.
Here's where I get a bit stuck. I don't really understand anything in the source for the table adapters well enough to manually add stuff. And I'm having trouble thinking of an easy alternative without resorting to littering my code with SQL queries.
Is placing an SQL query to populate a DataSet for this in the Code Behind file a poor plan (in terms of ongoing maintenance)? Or is there a better place to put such things? Or a better / simpler alternative?