ADO List of Tables in DB...

[JaedenRuiner]

I know that I can basically use my dataset (typed or untyped) to access the Tables() collection, but I was wondering if there was another way to request a list of tables from the db at run time?

Or perchance, say that when dealing with a strongly typed dataset (as created by the xsd designer) and I'm accessing a database with specific tables and table adapters pre-scripted to handle the ones that already exist. however, on the off chance that I "add" a table to the database at run time, can I tell the typed dataset to refresh itself from the database, utilizing all the typed adapters & datatables for the tables it recognizes, but simply adding the additional tables as default datatables to the tables() collection so I can keep my active tables list up to date as the database changes in real time.

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[JohnH]

another way to request a list of tables from the db at run time?

With Sql Manager Express, and likely the Manager included in full version SQLs, there are SMO libraries included, you can do the same managing with these at runtime as you can with the Manager application.

can I tell the typed dataset to refresh itself from the database

If you mean refreshing it afterwards in Designer, then perhaps Depends on whether you're connecting to same db in Designer and runtime, or if you have a project db and a runtime deployed db. If you connect to same db in Designer and runtime then you can run the dataset wizard again and select the new tables that were added in your debug runtime.

If you mean refreshing dataset at runtime, then you have to do manual fills like I showed in your other thread.

 

[JaedenRuiner]

Actually, none of the above. I found what I was looking for..

sys.tables contains a list of all the tables in the current database, with which I can create a dataset and bindingsource to bind to the ListBox and voila everything works. After a Select Into or Create Table, the sys.tables is updated with the new table name so I can keep a current listing of the tables and generate DataSets for them manually at run-time as necessary. I was looking for the "list" of tables not for the specific TAs or DataTables to be generated upon the creation of a new table in the database at runtime.

Thanks though, this issue is resolved.

Jaeden "Sifo Dyas" al'Raec Ruiner

 

[cjard]

I know that I can basically use my dataset (typed or untyped) to access the Tables() collection, but I was wondering if there was another way to request a list of tables from the db at run time?

It is worth noting that the Tables() collection contains the tables in the dataset NOT THE DATABASE

There is no rule that states a dataset must contain all, some or none of the tables in a db. Indeed, frequently in my datasets the tables are views, or the results of join queries and have no direct table representation

however, on the off chance that I "add" a table to the database at run time, can I tell the typed dataset to refresh itself from the database, utilizing all the typed adapters & datatables for the tables it recognizes, but simply adding the additional tables as default datatables to the tables() collection so I can keep my active tables list up to date as the database changes in real time.

No, you cannot tell the dataset to alter its design after you change the table structure in the DB. You are free to write all the same code that the designer does, and hence create your own local representation of a db table. This can be done on the fly but it is a serious waste of programming effort.

If youre in a situation where table structures in your db are changing often, you have a flaw in your design. There is however, nothing stopping you from making a temporary table and having the designer create a local representation of the table, then removing it from the db. If at any point in the future you choose to recreate the table in the db, the client side code will work without a problem. I cannot think of a valid reason why you would drop and recreate tables though. SUrely just emptying them will suffice

 

[alim]

i think i knw he solution...

if u r using oracle db..
try this

select * from tab

to return whole database tables list in a datatable
if its won't work then try this

Dim Cno as data.oledbconnection//connector
Dim Rows() as datarows
CnO.ConnectionString = ConnectionString

'MsgBox(CnO.ConnectionString)
Try
CnO.Open()
Catch ex As Exception
msgbox ("error")
End Try
Rows = CnO.GetSchema("Tables").Select("table_type='table'")

 

[cjard]

i think i knw he solution...

if u r using oracle db..
try this

I think you mean

SELECT * FROM all_tables

 

[JaedenRuiner]

I already know oracle, i'm learning SQl Server Express, so i am using sys.objects or sys.tables. (I also need to grab views, so sys.objects type = U is table, and type = V is view. seems to work perfectly for my needs.