Question regarding TableAdapters.

Question

I was reading the overview of the TableAdapter, and noticed this sentence:

"In other words, you can have as many queries as you want on a TableAdapter
as long as they return data that conforms to the same schema."


What if it doesnt?  What one of the columns I do not need?  Example:  The main Query schema is Select * from tbl, but I need a query that is only Select Name,ID FROM tbl ?
Any damage?

Answer 1

Hi,

in this case you'd have to make sure other columns in the data set will allow null values [AllowDBNull = True].

Andrej

Answer 2

no damage I believe.

your select statement tells SQL to get me all the columns available in this table, therefore all columns in that table will be returned.

if you specify a specific column to retrieve:

SELECT [Address] FROM table

 

and execute this query, then there maybe a SQL Exception thrown saying that the column was not found, or perhaps there will be no data returned at all (but I would believe most likely that the SQL Exception will be thrown complaining that there was no column "address")

 

hope it helps!

Answer 3

I just tried it to see for myself and you will get a warning and perhaps an exception in the code depending on the schema.

First, while adding your new query and the GetBy and FillBy methods to the TableAdapter, if the schema returned by your query is different from the main query you will get the following warning:

"The new command text returns data with schema different from the schema of the main query. Check your query's command text if this is not desired."

The TableAdapter will not tell you if this new query will work. It depends on the constraints of the columns you chose not to include in the new query.

If the new query violates the constraints, by not including a column that cannot be null for example, you will find out about it during runtime when you call the new GetBy or FillBy Method

ProductAttributesDataTable attributes = adapter.GetDataByProductId(productId);

Above is where I received the following exception when I didn't include a necessary column:

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

So, you may have problems not returning certain values from the query.

Although not a best practice, if you allow all your columns to be null, you can probably avoid the exception. I wouldn't recommend doing it, but it is a way to work around the schema issue.

Regards,

Dave

Answer 4

Hi,

In an application, there can be many queries where only subset of columns are required to be selected from various tables. 

Does it mean that we cannot use DataSet for such queries without removing the constraints on the columns???  Is there any other was we could handle such queries?

regards,
Asha.

Answer 5

Hi, All

I'm joining Asha and asking the same question. Is there any way we can encapsulate different queries returning different schema into the same strongly typed dataset?