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

Check out each column's Expression property

Show quoteHide quote

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

I am using SQL 2005.


Show quoteHide quote

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

Show quoteHide quote

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

Show quoteHide quote

Cor you wrote:

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.



Show quoteHide quote

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

Show quoteHide quote

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...

Show quoteHide quote

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.
-----------------------------------------------
Show quoteHide quote

Help calling an Oracle procedure from vb.net
What is the purpose of AcceptChanges?
Concurrency notification question
Please Heeelp I don't know what's wrong
Memo field trouble