Question Datarow retrieves name of tables in database?

Skuch

Member
Joined
Jul 26, 2011
Messages
5
Location
UK
Programming Experience
Beginner
Recently required to loop through SQLCe database to get all the names of the tables it holds. Couldn't get anywhere for a while good info about this task, while found this web article Get All SQL Database Table Names and Fields Using VB.NET | Online Marketing | Web Malama, Ft Collins, Loveland, Kona... That code worked as required for me after little changes, but.... I couldn't find any information at the moment why datarow retrieves tablename and why datarow has to be set to 2 arraynumber in code to get name (tried to set different - doesn't work). I would appreciate any info or sources...
Thanks
p.s.
here is revised code

Dim query As String = "select * from INFORMATION_SCHEMA.tables"
Dim myCmd As SqlCeDataAdapter = New SqlCeDataAdapter(query, Conn)
Dim myData As New DataSet()
myCmd.Fill(myData)




For Each table As DataTable In myData.Tables
For Each row As DataRow In table.Rows
Debug.WriteLine(row(2).ToString)
Next row
Next table
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,457
Location
Sydney, Australia
Programming Experience
10+
Don't do it that way. The SqlCeConnection class has a GetSchema method that will return that same information more simply.

The reason that you need to use index 2 is because you are retrieving all the columns from that table. The table name is contained in the third column.
 

Skuch

Member
Joined
Jul 26, 2011
Messages
5
Location
UK
Programming Experience
Beginner
Thanks jmcilhinney... however i can't get getschema working, it throws an error that "Specified method is not supported" exception.... then I searched through net and found this BUG: Error message when you try to obtain schema information from a SQL Server 2005 Compact Edition database or a SQL Server 2005 Mobile Edition database by using the SqlCeConnection.GetSchema method: "NotSupportedException" showing that it might be a bug...
At the moment found a different method to retrieve database names, do you think this better way than previous?

Code:

conn.Open()


Dim SelectCommand As New System.Data.SqlServerCe.SqlCeCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", conn)


Dim DataReader As System.Data.SqlServerCe.SqlCeDataReader = SelectCommand.ExecuteReader()


Do While DataReader.Read()
Debug.WriteLine(DataReader("TABLE_NAME").ToString)
Loop


SelectCommand.Dispose()
conn.Close()
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,457
Location
Sydney, Australia
Programming Experience
10+
Ah, sorry for that. I've never actually tried that with CE specifically, so I've never encountered that issue.

That method that you're using now is basically the same as what you were doing before, with slight variation.
 
Top Bottom