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" <(E-Mail Removed)> wrote in message
news:Occ67I$(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:ernk9u$feu$(E-Mail Removed)...
>>>> 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
>>>
>>>
>>
>>
>
>
Similar Threads
