SQL/.NET TableAdapters – How do I strongly-type calculated columns in either the database or the DataSet?

Question

We use calculated columns in a few SQL Server 2005 tables which always return data of a specific type (bit, varchar(50), etc...).

These tables are consumed by a .NET data layer using strongly-typed datasets and tableadapters, however since the calculated columns are not constrained to a specific type (we always return a specific type, but we could return anything) .NET does not generate strongly-typed columns.

Three questions here:
- The calcuated columns typically check that related records have specific values, or they return a single value from a related XML document using XPath. Are calculated columns the proper tool for this, or is there a better way to return these related pieces of data?

• My searches say no, but is it possible to strongly-type these columns at the database itself?

• Is there a workaround inside of the Visual Studio DataSet designer that would allow me to set the proper types and maintain them across DataSet/TableAdapter updates (since the Designer will query the database to update it's schema)?

Thanks!

Answer 1

First, they do have a type.

Second, the SQL Server will tell clients the type if the client requests it, whether its a C API client, a C# client, a JDBC client, etc. Otherwise, the clients can't correctly cat the type.

Third, you can find out what type they have by looking at the information_schema.columns (or syscolumns) row for the calculated columns.

Fourth, you can use the CAST operator to cast these columns to any type compatible with their actual types.

Answer 2

In the queries that you use to return these values, you can use the SQL CAST() function to cast them as whatever datatype you want. I believe .NET should recognize the casted datatype and put that type in the dataset.