DataColumn的defaultvalue问题

[ajex]

在sql中为相关的列设置了缺省值,到了datatable却没有了,why?
Dim sda As SqlDataAdapter
Dim dt As New DataTable

sda.FillSchema(dt, SchemaType.Source)

For Each dc As DataColumn In dt.Columns
If Not IsDBNull(dc.DefaultValue) Then

Response.Write(dc.DefaultValue.ToString() + "<br>")
End If


Next

[vitamin_ok]

let's up

[LevinForum]

得到expression后，，，

cmd=new SqlCommand "SELECT "+expression
object v=cmd.ExecuteScalar()

不过这样需要多次往返。

如果你想一次搞定，可以这样：

use northwind

declare @tablename nvarchar(50) set @tablename='products'

declare @result table (name nvarchar(50),value sql_variant)
declare @name nvarchar(50)
declare @exp nvarchar(3000)
declare @value sql_variant
declare @sql nvarchar(4000)

set nocount on
declare @cursor cursor
set @cursor = cursor read_only static for
select syscolumns.name,syscomments.text from
sysobjects inner join syscolumns on sysobjects.id=syscolumns.id
inner join syscomments on syscolumns.cdefault=syscomments.id
where sysobjects.xtype='U' and sysobjects.name=@tablename

open @cursor
fetch next from @cursor into @name,@exp
while @@FETCH_STATUS=0
begin
set @sql='select @v='+@exp
exec sp_executesql @sql ,N'@v sql_variant output',@v=@value output
insert into @result (name,value) values (@name,@value)
fetch next from @cursor into @name,@exp
end

close @cursor
deallocate @cursor

set nocount off

select name,value from @result

[saucer]

if you read this page or the corresponding page in the documentation carefully, you will know DefaultValue is never retrieved

http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataCommonDbDataAdapterClassFillSchemaTopic2.asp?frame=true

[ajex]

to:LevinForum(forum.lostinet.com)
感谢回复!我是想问如何查询出计算列的公式,比如我在某列[sumprice]上设置公式[Uniprice*10],如何查询出来[Uniprice*10]?

[ajex]

我也知道,能映射AllowDBNull,AutoIncrement,MaxLength,ReadOnly ,Unique ,搞不懂为啥不把defaultvalue也映射了,还有express也不能映射,看样子,还是等下个版本的framework吧.

多谢思归.

[LevinForum]

不提供DefaultValue是SqlServer的原因。
如果你需要，大不了可以自己查询syscolumns。

select syscolumns.name,syscomments.text from
sysobjects inner join syscolumns on sysobjects.id=syscolumns.id
inner join syscomments on syscolumns.cdefault=syscomments.id
where sysobjects.xtype='U' and sysobjects.name=@tablename

[LevinForum]

>>不提供DefaultValue是SqlServer的原因。
这个是猜的。
也有可能是其他数据库不一定支持。所以DataAdapter干脆设计统一不设置DefaultValue的。

[ajex]

to:LevinForum(forum.lostinet.com)

计算公式(expression)如何查询出来?

[LevinForum]

如果是计算列，那么是这样:

select syscolumns.name,syscomments.text from
sysobjects inner join syscolumns on sysobjects.id=syscolumns.id
inner join syscomments on sysobjects.id=syscomments.id and syscolumns.colid=syscomments.number
where sysobjects.xtype='U' and sysobjects.name=@tablename and syscolumns.iscomputed=1