Computed Columns

[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

[mangist]

On Feb 22, 7:28 pm, "Aamir Mahmood" <[e-mail]:

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 the DataColumn.Expression property. You can add computed
columns to a DataTable like this:

myDataTable.Columns.Add (new DataColumn ("ComputedValue", typeof
(decimal), "Qty * Price");

The "Qty * Price" expression will calculate the Qty column multiplied
by the Price column.

Hope this helps.

[Aamir Mahmood]

The computed column is already in database. I just want to know in my
program whether this column is a computed field or not.

The Expression property is always empty string for both computed and regular
columns.
<ma*****@gmail.comwrote in message
news:11*********************@v33g2000cwv.googlegro ups.com...

On Feb 22, 7:28 pm, "Aamir Mahmood" <[e-mail]:

>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 the DataColumn.Expression property. You can add computed
columns to a DataTable like this:

myDataTable.Columns.Add (new DataColumn ("ComputedValue", typeof
(decimal), "Qty * Price");

The "Qty * Price" expression will calculate the Qty column multiplied
by the Price column.

Hope this helps.

[=?ISO-8859-2?Q?S=B3awek?=]

Aamir Mahmood wrote:

The computed column is already in database. I just want to know in my
program whether this column is a computed field or not.

The Expression property is always empty string for both computed and regular
columns.

When you receive data from database, you will never receive them as a
computed data. Generally computed data could came from views only, so
check the definition of view if you want to know it is a computed field
or not. From DataTable you can not grab such information, it is too late.

best,
Slawomir

[Aamir Mahmood]

So, you are saying that there is no way to know whether the column is
computed or not?

If DataTable does not give me such information, I am open to any solution,
including querying the dabatase for schema.

Thanks.
"S³awek" <sp****************@o2.plwrote in message
news:er**********@node1.news.atman.pl...

Aamir Mahmood wrote:

>The computed column is already in database. I just want to know in my
program whether this column is a computed field or not.

The Expression property is always empty string for both computed and
regular columns.

When you receive data from database, you will never receive them as a
computed data. Generally computed data could came from views only, so
check the definition of view if you want to know it is a computed field or
not. From DataTable you can not grab such information, it is too late.

best,
Slawomir

[RobinS]

I can give you some code that will query a SQLServer database for schema
information, but it's in VB. Do you want me to post it?

Robin S.
---------------------------------
"Aamir Mahmood" <ab*****@efg.hijwrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...

So, you are saying that there is no way to know whether the column is
computed or not?

If DataTable does not give me such information, I am open to any
solution, including querying the dabatase for schema.

Thanks.
"S³awek" <sp****************@o2.plwrote in message
news:er**********@node1.news.atman.pl...

>Aamir Mahmood wrote:

>>The computed column is already in database. I just want to know in my
program whether this column is a computed field or not.

The Expression property is always empty string for both computed and
regular columns.

When you receive data from database, you will never receive them as a
computed data. Generally computed data could came from views only, so
check the definition of view if you want to know it is a computed field
or not. From DataTable you can not grab such information, it is too
late.

best,
Slawomir

[Aamir Mahmood]

I would really appreciate it, thanks.

But does it really tell about which columns are computed in a given table?

Please post the code, I will look into it.

-
Aamir.
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:rJ******************************@comcast.com. ..

>I can give you some code that will query a SQLServer database for schema
information, but it's in VB. Do you want me to post it?

Robin S.
---------------------------------
"Aamir Mahmood" <ab*****@efg.hijwrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...

>So, you are saying that there is no way to know whether the column is
computed or not?

If DataTable does not give me such information, I am open to any
solution, including querying the dabatase for schema.

Thanks.
"S³awek" <sp****************@o2.plwrote in message
news:er**********@node1.news.atman.pl...

>>Aamir Mahmood wrote:
The computed column is already in database. I just want to know in my
program whether this column is a computed field or not.

The Expression property is always empty string for both computed and
regular columns.

When you receive data from database, you will never receive them as a
computed data. Generally computed data could came from views only, so
check the definition of view if you want to know it is a computed field
or not. From DataTable you can not grab such information, it is too
late.

best,
Slawomir

[RobinS]

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.
-----------------------------------------------
"Aamir Mahmood" <ab*****@efg.hijwrote in message
news:Oc*************@TK2MSFTNGP06.phx.gbl...

>I would really appreciate it, thanks.

But does it really tell about which columns are computed in a given
table?

Please post the code, I will look into it.

-
Aamir.
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:rJ******************************@comcast.com. ..

>>I can give you some code that will query a SQLServer database for schema
information, but it's in VB. Do you want me to post it?

Robin S.
---------------------------------
"Aamir Mahmood" <ab*****@efg.hijwrote in message
news:et**************@TK2MSFTNGP02.phx.gbl...

>>So, you are saying that there is no way to know whether the column is
computed or not?

If DataTable does not give me such information, I am open to any
solution, including querying the dabatase for schema.

Thanks.
"S³awek" <sp****************@o2.plwrote in message
news:er**********@node1.news.atman.pl...
Aamir Mahmood wrote:
The computed column is already in database. I just want to know in
my program whether this column is a computed field or not.
>
The Expression property is always empty string for both computed and
regular columns.

When you receive data from database, you will never receive them as a
computed data. Generally computed data could came from views only, so
check the definition of view if you want to know it is a computed
field or not. From DataTable you can not grab such information, it is
too late.

best,
Slawomir