Foxpro: Check whether table exists via vfpoledb

Question

I access data in .dbf files via System.Data.OleDb (vfpoledb.dll). How can I find out whether table exists via SQL command? Something similar to the following on SQL server:

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

Answer 1

I don't know how to do it only using SQL but maybe you could check for the existence of the file on disk using the File.Exists Method or you could write some code to check for the existence of the dbf using the OleDb classes:

private bool DbfExists(string dbfName, string connectionString)
{
    bool dbfExists = true;

    using(OleDbConnection conn = new OleDbConnection(connectionString))
    {
        string sql = string.Format("SELECT * FROM {0}", dbfName);

        using(OleDbCommand command = new OleDbCommand(sql, conn))
        {
            OleDbDataReader reader = null;

            try
            {
                conn.Open();
                reader = command.ExecuteReader();
            }
            catch(Exception ex)
            {
                dbfExists = false;
            }
            finally
            {
                conn.Close();
                reader = null;
            }
        }
    }

    return dbfExists;
}

I have not tried compiling this code so it may need to be tweaked a bit.

Answer 2

If you have a dbc file you can query it to see if the table exists.

string dbc = "northwind.dbc";

using (OleDbConnection conn = new OleDbConnection(connectionString)) {
    DataTable dt = new DataTable();
    string sql = string.Format(@"SELECT * FROM {0} WHERE ALLTRIM(ObjectType) = 'Table' AND UPPER(ALLTRIM(ObjectName)) = '{1}'", dbc, tableName.ToUpper());
    OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
    da.Fill(dt);
    bool tableExists = dt != null && dt.Rows.Count == 1;
}

But really you don't need a sql command or a dbc file to get that information. You can get it straight from the OleDbConnection using the GetSchema method.

using (OleDbConnection conn = new OleDbConnection(connectionString)) {
    conn.Open();
    DataTable tables = conn.GetSchema("Tables");
    conn.Close();

    var tableExists = (from row in tables.AsEnumerable()
                        where row.Field<string>("Table_Name").Equals(tableName, StringComparison.CurrentCultureIgnoreCase)
                        select row.Field<string>("Table_Name")).FirstOrDefault() != null;
}

Answer 3

Additionally, if you are connecting to DBF tables that are "FREE" tables and NOT actually part of a connected "database" (.dbc), then you can just check for the file's existence or not... Such as in C# via

if( File.Exists( PathToTheDatabaseDirectory + TableYouExpect + ".DBF" ))
   file is there
else
   file is missing