Search code examples
c#sqldatabaseoledb

Get The DataBase Table Name in C#

Alright, I know that this case has been discussed all over the web, but I still don't understand.

Here's my search result so far:

"SELECT table_name AS Name FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'";

In this SQL code, I thought that the INFORMATION_SCHEMA was a database name and when I executed the code (of course with the connected database name), an exception was thrown saying that the database name doesn't exist.

Updated:

When I run the SQL query and execute it with:

OleDbDataReader reader = cmd.ExecuteReader();

It says: Could not find: path/INFORMATION_SCHEMA.mdb;

The things I already done and they're good are:

  1. The connection string is filled with the database name.
  2. I can do the usual SQL CommandText to my database.
  3. I'm using the OLEDB DBMS.

SOLVED

And my final code with help of other people is:

DataTable MySchemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });

for (int i = 0; i < MySchemaTable.Rows.Count; i++) {
combobox1.Items.Add(MySchemaTable.Rows[i].ItemArray[2].ToString()); }

Another search is this:

`DataTable T = con.GetSchema("Tables");`

And when I print out the t.ToString();, it's only showwing the Tables.

And many more searches.

CASE CLOSED!

Solution

  • You can not use the INFORMATION_SCHEMA in OLEDB, it has a different way to provide its table information. Take a look at GetOleDbSchemaTable. It looks like:

    MySchemaTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                   New Object() {Nothing, Nothing, Nothing, "TABLE"})

    The Documentation states here:

    The .NET Framework Data Provider for OLE DB also exposes schema information by using the GetOleDbSchemaTable method of the OleDbConnection object. As arguments, GetOleDbSchemaTable takes an OleDbSchemaGuid that identifies the schema information to return, and an array of restrictions on those returned columns. GetOleDbSchemaTable returns a DataTable populated with the requested schema information.