请问微软专家！！！如何用ADO.NET 枚举数据库的表、字段及类型长度等

[火鸟李国平]

DAO时代我本来是会的，ADO时就不知道了
到了ADO.NET，我还是想要知道
要能适用于不同数据库的方法，如可能SQLServer和Access内部结构会不会有所差异，而这种差异通过ADO.net是不是就透明了呢？
请给出代码实现的例子，谢谢了

[iStringTheory]

asp时代用如下代码：

< %'---------------读access库中的表名-------------------------
SET Conn=Server.CreateObject("ADODB.Connection")
Conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ="& Server.MapPath("库名称")
Set rstSchema=Conn.OpenSchema(20)

Do Until rstSchema.EOF
Response.Write "Table name:"& rstSchema(2)& "< br> "& "Table type:"& rstSchema(3)& "< br> < br> "
rstSchema.MoveNext
Loop
%>
< %'---------------读sql server库中的表名-------------------------
SET Conn=Server.CreateObject("ADODB.Connection")
Conn.Open "Server=IP地址;Provider=sqloledb;Database=库名称;UID=用户名;PWD=密码;"
Set rstSchema=Conn.OpenSchema(20)

Do Until rstSchema.EOF
Response.Write "Table name:"& rstSchema(2)& "< br> "& "Table type:"& rstSchema(3)& "< br> < br> "
rstSchema.MoveNext
Loop
%>

< %'---------------读oracle库中的表名-------------------------
SET Conn=Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=MSDAORA.1;Password=密码;User ID=用户名;Data Source=数据源名称"
Set rstSchema=Conn.OpenSchema(20)

Do Until rstSchema.EOF
Response.Write "Table name:"& rstSchema(2)& "< br> "& "Table type:"& rstSchema(3)& "< br> < br> "
rstSchema.MoveNext
Loop
%>
只要用相应的数据库代替以上的中文就可以

[iStringTheory]

读取字段估计也没问题

[xqchang]

dao是支持的，到了ado时不支持了，是因为还有一个adox对象，adox是ado对象的扩展库，它可以创建、修改和删除表，还可维护用户和权限，很方便的。你可以看看acccess的帮助文件，里面有adox比较详细的描述，还有具体的vb和vc的例子。是不是也有个adox.net呢？

[Tongls]

楼上这位先生，你用的不是ADO.net,而是用ADO

[a2z]

Method OleDbConnection.GetOleDbSchemaTable


class OleDbSchemaGuid

[zhang_dz]

楼上这位仁兄没错。我们可以用GetOleDbSchemaTable来得到数据库本身的结构信息。这个函数的具体用法是：
[Visual Basic]
Public Function GetOleDbSchemaTable( _
ByVal schema As Guid, _
ByVal restrictions() As Object _
) As DataTable
[C#]
public DataTable GetOleDbSchemaTable(
Guid schema,
object[] restrictions
);
其中schema指定你要的schema table，restrictions指定限制模式。举个例子会更清楚些：
[C#]
public DataTable GetTables(OleDbConnection conn)
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});
conn.Close();
return schemaTable;
}
以上例子返回一个包含数据库中所有表的列表。

更多关于这个函数的信息可以在以下网页找到：
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbConnectionClassGetOleDbSchemaTableTopic.asp

希望对你有帮助。

- 微软全球技术中心

本贴子没有任何担保，同时也没有授予任何权利。具体事项可参见使用条款
(http://support.microsoft.com/directory/worldwide/zh-cn/community/terms_chs.asp)

[hhuan]

sql server 执行sp_tables不行吗？？

[a2z]

回复人： hhuan(笨拉登) ( ) 信誉：100 2002-09-02 15:52:00 得分:0


sql server 执行sp_tables不行吗？？
__________________________________________________________________

看清楚问题好吗？：“请问微软专家！！！如何用ADO.NET 枚举数据库的表、字段及类型长度等”
并且是与数据库无关的
sp_tables有什么用？看个表名儿？其他的需求能否实现？

[hhuan]

回复人： a2z(xyz就是……) ( ) 信誉：100
sp_tables有什么用？看个表名儿？其他的需求能否实现？
-----------------------------------------------------------
抛开数据库无关性不说，sp_tables后，再用sp_columns难到不行吗？
我用ado.net已经实现了。

[GuoKing]

gz

[a2z]

抛开数据库无关性?
能够么？

如果抛开数据库无关性，那么ado.net的存在意义就小了一半，关于sql数据库的信息当然都已经存贮在dbo.sys***中了，这点相信是个程序员就知道，那么为什么还要用这个呢？——GetOleDbSchemaTable
那么如何获得informix\db2\oracle\sysbase中数据库表的信息呢？__________________________________________________________
并且不用sp_columns也可以找到相关信息。



CREATE PROCEDURE sp_columns (
@table_name nvarchar(384),
@table_owner nvarchar(384) = null,
@table_qualifier sysname = null,
@column_name nvarchar(384) = null,
@ODBCVer int = 2)
AS
DECLARE @full_table_name nvarchar(769)
DECLARE @table_id int

if @ODBCVer <> 3
select @ODBCVer = 2
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @table_name is null
begin /* If table name not supplied, match all */
select @table_name = '%'
end
if @table_owner is null
begin /* If unqualified table name */
SELECT @full_table_name = quotename(@table_name)
end
else
begin /* Qualified table name */
if @table_owner = ''
begin /* If empty owner name */
SELECT @full_table_name = quotename(@table_owner)
end
else
begin
SELECT @full_table_name = quotename(@table_owner) +
'.' + quotename(@table_name)
end
end

/* Get Object ID */
SELECT @table_id = object_id(@full_table_name)
if ((isnull(charindex('%', @full_table_name),0) = 0) and
(isnull(charindex('[', @table_name),0) = 0) and
(isnull(charindex('[', @table_owner),0) = 0) and
(isnull(charindex('_', @full_table_name),0) = 0) and
@table_id <> 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */

SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)),
SS_DATA_TYPE = c.type
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.id = @table_id
AND c.id = o.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @column_name
ORDER BY 17
end
else
begin
/* this block is for the case where there IS pattern
matching done on the table name */

if @table_owner is null /* If owner not supplied, match all */
select @table_owner = '%'

SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3))),
SS_DATA_TYPE = c.type
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.name like @table_name
AND user_name(o.uid) like @table_owner
AND o.id = c.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @column_name
ORDER BY 2, 3, 17
end

GO