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

calculated-columnsdatasetnetsql serverstrongly-typed-dataset

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!

Best Answer

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.