DataAdapter Select string from base table schema?

Question

When I built my .xsd, I had to choose the columns for each table, and it made a schema for the tables, right? So how can I get that Select string to use as a base Select command for new instances of dataadapters, and then just append a Where and OrderBy clause to it as needed?

That would keep me from having to keep each DataAdapter's field list (for the same table) in synch with the schema of that table in the .xsd file.

Isn't it common to have several DataAdapters that work on a certain table schema, but with different params in the Where and OrderBy clauses? Surely one does not have to maintain (or even redundently build) the field list part of the Select strings for half a dozen DataAdapters that all work off of the same table schema.

I'm envisioning something like this pseudo code:

BaseSelectString = MyTypedDataSet.JobsTable.GetSelectStringFromSchema()  // Is there such a method or technique?

WhereClause = " Where SomeField = @Param1 and SomeOtherField = @Param2"
OrderByClause = " Order By Field1, Field2"

SelectString=BaseSelectString + WhereClause + OrderByClause

OleDbDataAdapter adapter = new OleDbDataAdapter(SelectString, MyConn)

Answer 1

Each table has a default query (The one on top with the check on it). When you dragged your tables in to the dataset to create the query, it wrote a SQL statement which it uses to schema your table. Keep that query simple, you might not actually use it in code, and you can always edit that query to update the table schema.

Every time you open the default query it connects to your datasource and allows you to select new columns that weren't in there before. If you want to update your existing columns, delete all the columns out of the table before you attempt to open the query. When you save the query, your updated columns get added back.

Make sure your connection string has permissions to view column information.

You can add multiple queries to a single TableAdapter. TableAdapters in the designer appear sectioned with a table schema at the top, and queries on the bottom. The default query will control which columns are available for output from the other queries. To add an additional query, right click on the TableAdapter and select "Add->Query" or if you are selecting the bottom part of the TableAdapter you can select "Add Query...". Any new SQL query you create will start off with the SQL from the default query. You give each new query a method name which you can use instead of the default query's "Fill" or "GetData" methods. The assumption is that each new query will have a result set that matches the default query even though they can have different "where" clause parameters.

In short

• You may have a single TableAdapter for each table, just add multiple queries.
• Each additional query can have different "Where" clause parameters as long as they all return the same columns.