Computed Columns

  • Thread starter Thread starter Aamir Mahmood
  • Start date Start date
A

Aamir Mahmood

Hi All

I have DataTable object. Is there a way that I can know which fields
(columns) in the table are computed.

Apparantly the DataTable.Columns returns all columns both computed and
other.

Any help would be appreciated.

Thanks.

Amir
 
Expression property is always an empty string. In case of computed columns
also.

I am using SQL 2005.


Miha Markic said:
Check out each column's Expression property

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Aamir Mahmood said:
Hi All

I have DataTable object. Is there a way that I can know which fields
(columns) in the table are computed.

Apparantly the DataTable.Columns returns all columns both computed and
other.

Any help would be appreciated.

Thanks.

Amir
Click to expand...
Click to expand...
 
You mean computed columns ... on the server.
Well, then try with SqlConnection.GetSchema method.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Aamir Mahmood said:
Expression property is always an empty string. In case of computed
columns also.

I am using SQL 2005.


Miha Markic said:
Check out each column's Expression property

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Aamir Mahmood said:
Hi All

I have DataTable object. Is there a way that I can know which fields
(columns) in the table are computed.

Apparantly the DataTable.Columns returns all columns both computed and
other.

Any help would be appreciated.

Thanks.

Amir
Click to expand...
Click to expand...
Click to expand...
 
Aamir,

Are you sure that you are using the right names (nothing to do with English)
for the column.
A Computed column is a column that creates a value by the DataTable.Compute
statement.

I think that you are talking about an added column wich is added with an
Expression. (That is for sure Miha is as well talking about). Mostly the
programmer know himself which this are. Why don't you not know that?

Cor
 
Cor said:
A Computed column is a column that creates a value by the
DataTable.Compute statement
Click to expand...

From the above statement it seems that you don't know about the computed
column in a sql server database table itself.

Or I didn't make myself clear.

Here is the situation.

I desinged table "T" in SQL Server 2005. One of the columns is a computed
column ** by design **. Lets say columns A is int, column B is int. And
column C is a computed column (C = A + B).

Now I have a DataTable in my .Net application which was created by the
command "select * from T".
This DataTable has a collection of columns called Columns, right.
And it has all three columns (A, B and C).

Is there a way that I can know that C is a computed column?

I want to know it through code, becaues my application can be connected to
any database and it has to show the table structure.
I have to show the computed column differently. And for that I need to know
which columns are computed in the SQL Server database.

I hope I have made myself clear.

Any help would be appreciated.

Thanks.
 
Aamir,

I would not know how to help you, that means that it can be a SP of a
whatever, which you don't know, that gives you a table while the database
table does not even exist at all.

Cor
 
Looks like GetSchema returns a lot of info regarding column collection
but not is_computed dependency. I think the following query will get
you this information:

select t.name, c.name, c.is_computed
from sys.columns as c join sys.tables as t
on c.object_id = t.object_id
where c.is_computed = 1 and t.name = '<table_name>'

I hope this will help.
 
Yep, if GetSchema doesn't return you enough info then the only way is to
query database directly...
 
I posted a response to this in the C# group. In the future, please don't
multi-post. If you want to post to multiple groups, post them all at once,
so if a solution is provided, the people in the other groups can see it and
know it has been answered, and they can go help someone else.

For the people here, here is my other post telling how to access the column
information.
-------------------------------------------


Here are all of the properties (and their types) you can retrieve for the
data columns using a DataReader.

col name = ColumnName, type = System.String
col name = ColumnOrdinal, type = System.Int32
col name = ColumnSize, type = System.Int32
col name = NumericPrecision, type = System.Int16
col name = NumericScale, type = System.Int16
col name = IsUnique, type = System.Boolean
col name = IsKey, type = System.Boolean
col name = BaseServerName, type = System.String
col name = BaseCatalogName, type = System.String
col name = BaseColumnName, type = System.String
col name = BaseSchemaName, type = System.String
col name = BaseTableName, type = System.String
col name = DataType, type = System.Type
col name = AllowDBNull, type = System.Boolean
col name = ProviderType, type = System.Int32
col name = IsAliased, type = System.Boolean
col name = IsExpression, type = System.Boolean
col name = IsIdentity, type = System.Boolean
col name = IsAutoIncrement, type = System.Boolean
col name = IsRowVersion, type = System.Boolean
col name = IsHidden, type = System.Boolean
col name = IsLong, type = System.Boolean
col name = IsReadOnly, type = System.Boolean
col name = ProviderSpecificDataType, type = System.Type
col name = DataTypeName, type = System.String
col name = XmlSchemaCollectionDatabase, type = System.String
col name = XmlSchemaCollectionOwningSchema, type = System.String
col name = XmlSchemaCollectionName, type = System.String
col name = UdtAssemblyQualifiedName, type = System.String
col name = NonVersionedProviderType, type = System.Int32

About halfway down the list you will find [IsExpression]. This is probably
what you are looking for.

Here's how I got this list; tableName is passed in as a String. This shows
the columns you can get, and then shows selected values for each column
defined in the table.

I am using this methodology to create a stored procedure generator for CRUD
sprocs, because I'm tired of writing them myself! I use this to find out
which fields are keys, if they are identity columns, and the data types and
field sizes.

Dim cn As New SqlConnection(My.Settings.DBConnString)
'put the table name in brackets in case it has spaces in it
Dim SQLString As String = "SELECT * FROM [" & tableName & "]"
Try
cn.Open()
Dim cmd As New SqlCommand(SQLString, cn)
Dim rdr As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTable
'This shows all of the information you can access about each
column.
For Each col As DataColumn In tbl.Columns
Debug.Print("col name = " & col.ColumnName & _
", type = " & col.DataType.ToString)
Next
For Each row As DataRow In tbl.Rows
'DataTypeName actually gives the same
' data type name as is displayed in SQLServer
Debug.Print("{0}, ColumnSize = {1}, DataType = {2},
DataTypeName = {3}, IsExpression = {4} ", _
row("ColumnName"), row("ColumnSize"), row("DataType"), _
row("DataTypeName"), row("IsExpression"))
Next
rdr.Close()
Catch
MessageBox.Show("Error opening the connection to the database.")
Finally
cn.Close()
End Try

If [IsExpression] doesn't do it, check IsReadOnly (because you're think
that a calculated column would be read-only), or some of the other columns.

Trial and error (or, as Microsoft calls it, "iterative experimentation") is
your best friend.

Good luck.
Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
 
Back
Top