How to execute a SHOW statement with OleDb, C#?

Question

Tried following c# code to get all tablenames in the database, but I get an error-message saying:

"System.Data.OleDb.OleDbException (0x80040E14): Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'..."

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tbx_Source.Text + ";");
OleDbCommand cmd = new OleDbCommand("SHOW TABLES;", conn);
OleDbDataReader reader;

try
{
    conn.Open();
    reader = cmd.ExecuteReader();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            cbx_Tables.Items.Add(reader.GetValue(0).ToString());
        }
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

How do I execute this kind of commands with OleDb?

Thanks :)

Answer 1

Since you want to show all tables in database (i.e mdb) ,you can try

           DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

           DataTable userTables = null;
           using (DbConnection connection = factory.CreateConnection())
           {

               connection.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data  
               Source=C:\test\testfile.accdb;Jet OLEDB:Database Password=yourrPassword;";

                string[] restrictions = new string[4];
                restrictions[3] = "Table";

                connection.Open();

                // Get list of user tables
                userTables = connection.GetSchema("Tables", restrictions);


                foreach (DataRow item in userTables.Rows) // You can use simple 
                                                          //looping to retreive the table Name
                {



                }
            }

Worked for me

Answer 2

While Kyle's answer is the one I would prefer through the use of a DataTable, you can use an OleDBDataReader as you have in your question code by making the OleDbCommand command text

Select TABLE_NAME From INFORMATION_SCHEMA.TABLES

Answer 3

I think you can't.

SHOW is not a data-manipulation-statement.

Data manipulation language =

SELECT ... FROM ... WHERE ...
INSERT INTO ... VALUES ...
UPDATE ... SET ... WHERE ...
DELETE FROM ... WHERE ...

Data definition language =

CREATE TABLE ... 
DROP TABLE ... ;
ALTER TABLE ... ADD ... INTEGER;