Get list of tables from database

Question

Can anyone tell me how to obtain a list of tables in a Visual FoxPro database from within C#?

I tried using the GetSchema but the information that comes back seems to just be counts (and not accurate counts at that).

Here is what I have...

string connSrc = @"Provider=VFPOLEDB.1;Data Source=myvfpdb.dbc";
using (var cmdSrc = new OleDbCommand())
using (var dbconnSrc = new OleDbConnection(connSrc))
{
    dbconnSrc.Open();
    DataTable schema = dbconnSrc.GetSchema();
}

I thought there might be some way I could open the dbc file directly and run a SELECT against it but the problem is the database is located in the path to the connection string. I could find no OleDb command that would extract the schema information.

EDIT:

I found a way that works but it seems a little clunky. Anybody know of a better way?

string connSrc = @"Provider=VFPOLEDB.1;Data Source=myvfpdb.dbc";
using (var conn = new OleDbConnection(connSrc))
    dbc = conn.DataSource;

string path = Path.GetDirectoryName(dbc);
string database = Path.GetFileName(dbc);

using (var conn = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=" + path))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM " + database;
        DbDataReader reader = cmd.ExecuteReader();

    }
}

EDIT:

With Tamar's suggestion changed the query statement to this...

 cmd.CommandText = $"SELECT ObjectName FROM {database} WHERE ObjectType = \"Table\"";

You can certainly add a WHERE clause to the query: WHERE objecttype="Table" — Tamar E. Granor, Feb 02 '17 at 21:44
And? Did that solve your problem? If it did, just post it as an answer. — Jeroen Mostert, Feb 03 '17 at 10:18
@JeroenMostert It did but Cetin came up with a much better solution so I marked his as the correct answer. — Gene S, Feb 04 '17 at 01:53

score 5 · Accepted Answer · answered Feb 03 '17 at 14:21

I think using OleDbConnection.GetSchema() is the way to go. The way you do it, it just returns you the available metadata informations. You need to choose what metadata you want. The info you see are counts of 'available restrictions' and 'number of identifier parts'. To see a list of tables only (with additional info like Table_type -view or table-, description, modification and creation dates) you need to pass 'Tables' as a parameter to GetSchema. i.e.:

void Main()
{
    string strCon =
           @"Provider=vfpoledb;Data Source=C:\PROGRAM FILES (x86)\MICROSOFT VISUAL FOXPRO 9\SAMPLES\data\testdata.dbc";
    DataTable tableInfo;
    using (OleDbConnection con = new OleDbConnection(strCon))
    {
        con.Open();
        tableInfo = con.GetSchema("Tables");

        // or get TABLES only
        // tableInfo = con.GetSchema("Tables", new string[] {null,null,null,"TABLE"});

        con.Close();
    }
    foreach (DataRow row in tableInfo.Rows)
    {
        Console.WriteLine(@"Name:[{0}] Type:[{1}]",
            row["TABLE_NAME"],
            row["TABLE_TYPE"]);
    }
}

Awesome...I don't know how I missed that `GetSchema` can have a parameter passed. Your code returns the exact same results as my code and it is the **right** way to do it. Thanks. — Gene S, Feb 04 '17 at 00:10
@transformer, in VFP all tables are user defined. If you meant tables for forms, classlib, menu ... they are not really included in a database or even if they did, you could reason them from their extensions. Other than that, you could check their content, else there is nothing specific as a "user defined table" (even those might be pure user defined tables). — Cetin Basoz, Dec 28 '18 at 14:45

Get list of tables from database

1 Answers1