list of tables in database

[ray well]

hi,

how can i get a list of the tabes within a database with ado .net, the only
access to databases that i know are possible is thru a reader, or thru a
dataadpter, in none of which i found any way of getting a list of the
database tables.

also how can i add a new table to a DATABASE, i know how to create a new
table on its own, and add its columns etc., and how to add it to a dataset,
but how would i add a new table to a database? the insert command apparently
works only on existing table.

thanks for any help
ray

 

[Sahil Malik]

Ray,

how can i get a list of the tabes within a database with ado .net, the only
access to databases that i know are possible is thru a reader, or thru a

....

This depends on the exact database you are dealing with. The method would
differ for Oracle, than it would for SQL Server. What DB are you using?

also how can i add a new table to a DATABASE, i know how to create a new
table on its own, and add its columns etc., and how to add it to a

dataset,

Using the "CREATE TABLE" statement in SQL.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/

 

[ray well]

Shail,

thanks for your answer, i'm using an access database, would it work there?

how would i sent it to the database, using a dataadapter or a dataset?

also how can i get a list of the existing tables?


thanks,

ray

 

[Paul Clement]

¤ hi,
¤
¤ how can i get a list of the tabes within a database with ado .net, the only
¤ access to databases that i know are possible is thru a reader, or thru a
¤ dataadpter, in none of which i found any way of getting a list of the
¤ database tables.

Try GetOleDbSchemaTable:

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"

DatabaseConnection.Open()

SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing,
Nothing, Nothing})

Dim RowCount As Int32
For RowCount = 0 To SchemaTable.Rows.Count - 1
Console.WriteLine(SchemaTable.Rows(RowCount)!TABLE_NAME.ToString)
Next RowCount

DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()


¤ also how can i add a new table to a DATABASE, i know how to create a new
¤ table on its own, and add its columns etc., and how to add it to a dataset,
¤ but how would i add a new table to a database? the insert command apparently
¤ works only on existing table.

Use the ExecuteNonQuery method:

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

Dim strSQL As New String("CREATE TABLE tblCustomers (" & _
"CustomerID INTEGER NOT NULL," & _
"[Last Name] TEXT(5) NOT NULL," & _
"[First Name] TEXT(50) NOT NULL," & _
"Phone TEXT(10)," & _
"Email TEXT(50))")

Dim AccessCommand As New System.Data.OleDb.OleDbCommand(strSQL, AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[ray well]

Paul,

thanks for the snippets, they worked. ray

p.s.

would you by any chance know what sql phrase i would need to use to create
an access database, primary key autonumber field

the following just about does it, but it doesn't auto increment.

"UniqueID INTEGER NOT NULL PRIMARY KEY UNIQUE,"

would u know the phrase for making it auto increment, ?

thanks

ray

 

[Paul Clement]

¤ Paul,
¤
¤ thanks for the snippets, they worked. ray
¤
¤ p.s.
¤
¤ would you by any chance know what sql phrase i would need to use to create
¤ an access database, primary key autonumber field
¤
¤ the following just about does it, but it doesn't auto increment.
¤
¤ "UniqueID INTEGER NOT NULL PRIMARY KEY UNIQUE,"
¤
¤ would u know the phrase for making it auto increment, ?

I believe you need to use COUNTER instead of INTEGER.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)