在建立OLE DB连接的前提下,用VB.NET什么方法可以获得数据库中所有表名?
我现在的问题是我已经建立OLE DB连接(可能是ACCESS,SQL Server,Oracle等),那么接下来用什么方法可以得到数据库中所有的表名?我想用OleDbSchemaGuid.Tables,思路对么?
...全文
请发表友善的回复…
发表回复
pink0763 2004-10-22
- 打赏
- 举报
help!
pink0763 2004-10-20
- 打赏
- 举报
up
pink0763 2004-10-15
- 打赏
- 举报
在VB下,可以:
Private Sub Form_Load()
Dim adoCN As New ADODB.Connection '定义数据库的连接存放数据和代码Dim rstSchema
Dim strCnn As New ADODB.Recordset
str1 = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=wktrade;Data Source=net5"
adoCN.Open str1
Set rstSchema = adoCN.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
out = out & "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close
adoCN.Close
Debug.Print out
End Sub
VB.NET有类似的方法么?
Private Sub Form_Load()
Dim adoCN As New ADODB.Connection '定义数据库的连接存放数据和代码Dim rstSchema
Dim strCnn As New ADODB.Recordset
str1 = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=wktrade;Data Source=net5"
adoCN.Open str1
Set rstSchema = adoCN.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
out = out & "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close
adoCN.Close
Debug.Print out
End Sub
VB.NET有类似的方法么?
wenww 2004-10-15
- 打赏
- 举报
up
pink0763 2004-10-15
- 打赏
- 举报
读过上面链接的文章,还是不明白。
我在MSDN中见过相类似的例子:
Public Function GetTables(conn As OleDbConnection) As DataTable
conn.Open()
Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
conn.Close()
Return schemaTable
End Function
不过,就算我得到了DataTable,我又如何从中取出所有的表名呢?
从我看过的例子,一般是用DataAdapter向DataTable里放数据,而DataAdapter
需要一个SELECT * FROM mytable的语句,我希望是先从库的所有表中选出我需要
的mytable表,然后建立DataAdapter和DataTable。
我在MSDN中见过相类似的例子:
Public Function GetTables(conn As OleDbConnection) As DataTable
conn.Open()
Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
conn.Close()
Return schemaTable
End Function
不过,就算我得到了DataTable,我又如何从中取出所有的表名呢?
从我看过的例子,一般是用DataAdapter向DataTable里放数据,而DataAdapter
需要一个SELECT * FROM mytable的语句,我希望是先从库的所有表中选出我需要
的mytable表,然后建立DataAdapter和DataTable。
cancersyf 2004-10-15
- 打赏
- 举报
使用Ado.net获取数据库架构信息
http://blog.csdn.net/zhzuo/archive/2004/07/03/33273.aspx
http://blog.csdn.net/zhzuo/archive/2004/07/03/33273.aspx
happyRose 2004-10-15
- 打赏
- 举报
在sql server中,用:
select name from sysobjects where xtype = 'u' and status >= 0
是可以选出所有的表名的
Oracle 中应该也会有这样的数据存储表,要看下帮助。
ACCESS 中就没有了,不过可以用老式的DAO方法来获得所有的表名称
Dim MyDBOEngine As DAO.DBEngine = New DAO.DBEngine
Dim MyDB As DAO.Database
Dim MyWS As DAO.Workspace
Dim MyTB As DAO.TableDef
Dim i As Integer
MyWS = MyDBOEngine.Workspaces(0)
MyDB = MyWS.OpenDatabase("C:\data\dbTest.mdb", False, False)
For Each MyTB In MyDB.TableDefs '循环取得表名
'If strDTName = MyTB.Name Then
If MyTB.Name = "UTIMPORTDATA" Then
MyDB.TableDefs.Delete("UTIMPORTDATA") '如果该表已经存在先删除
End If
Next
select name from sysobjects where xtype = 'u' and status >= 0
是可以选出所有的表名的
Oracle 中应该也会有这样的数据存储表,要看下帮助。
ACCESS 中就没有了,不过可以用老式的DAO方法来获得所有的表名称
Dim MyDBOEngine As DAO.DBEngine = New DAO.DBEngine
Dim MyDB As DAO.Database
Dim MyWS As DAO.Workspace
Dim MyTB As DAO.TableDef
Dim i As Integer
MyWS = MyDBOEngine.Workspaces(0)
MyDB = MyWS.OpenDatabase("C:\data\dbTest.mdb", False, False)
For Each MyTB In MyDB.TableDefs '循环取得表名
'If strDTName = MyTB.Name Then
If MyTB.Name = "UTIMPORTDATA" Then
MyDB.TableDefs.Delete("UTIMPORTDATA") '如果该表已经存在先删除
End If
Next
wangsaokui 2004-10-15
- 打赏
- 举报
使用Ado.net获取数据库架构信息
http://blog.csdn.net/zhzuo/archive/2004/07/03/33273.aspx
http://blog.csdn.net/zhzuo/archive/2004/07/03/33273.aspx
pink0763 2004-10-15
- 打赏
- 举报
在sql server中,用:
select name from sysobjects where xtype = 'u' and status >= 0
是可以选出所有的表名的,
不过在ACCESS,Oracle中怎么实现呢?
有没有通用的办法,就是无论我连接的是哪一种数据库,都可以通过代码列出
所有表名?
select name from sysobjects where xtype = 'u' and status >= 0
是可以选出所有的表名的,
不过在ACCESS,Oracle中怎么实现呢?
有没有通用的办法,就是无论我连接的是哪一种数据库,都可以通过代码列出
所有表名?
dino2000 2004-10-15
- 打赏
- 举报
关注哦,我也不知道。谢谢楼主!
roapzone 2004-10-15
- 打赏
- 举报
你连接以后用
参考如下sql语句:
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
参考如下sql语句:
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder