How to know if a table exists in an Access Database in an OleDb connection

Question

I'm using the below code to connect to an Access Database using OleDb connection in C# .Net

How can I know if the table that I have hard-coded into the program actually exists in the file, so that I can show the user the appropriate message?

try
{
    var dbSource = "Data Source = " + source;
    const string dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;";

    using (var con = new OleDbConnection())
    {
        con.ConnectionString = dbProvider + dbSource;
        con.Open();

        using (var cmd = new OleDbCommand())
        {
            cmd.Connection = con;
            cmd.CommandText = "SELECT * FROM [Concrete Design Table 1]";

            // How do I know the table name is valid? It results in errors when it is not?
            // How to prevent it?
            using (var dataReader = cmd.ExecuteReader())
            {
                while (dataReader != null && dataReader.Read())
                {
                    // read the table here
                }
            }
        }
    }
}
catch (Exception e)
{
    MessageBox.Show(e.ToString());
}

Porett

You can get the list of tables with var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });, if I am understanding what you want correctly. – Natal

@Natal Thanks svinja for the clue, but can you tell me where in schema the list of the names are saved? I couldn't find it. – Porett

The code started to add up so I posted an answer. – Natal

Answer 1

You can get the list of tables with

var schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

and go through them:

foreach (var row in schema.Rows.OfType<DataRow>())
{
    string tableName = row.ItemArray[2].ToString();
}

or check for existence:

if (schema.Rows
          .OfType<DataRow>()
          .Any(r => r.ItemArray[2].ToString().ToLower() == tablename.ToLower()))
{
    // table exists
}

Ugly, I know. :(

Natal

the strange thing is that the tables exists in my exe folder database but it does not get displayed in the above code! – Accommodative

Answer 2

Here's a working solution I used (in VB.NET, but I guess you can figure out the C# variation):

Dim myTable As Object, TableSchema As Object

Dim con As OleDb.OleDbConnection
con = New System.Data.OleDb.OleDbConnection
con.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" & myFileName & ";"

If Not con.State = ConnectionState.Open Then con.Open()
TableSchema = con.GetSchema("TABLES")

myTable = TableSchema.select("TABLE_NAME='MYTABLENAME'")
If myTable.length = 0 Then
    Dim cmd As New OleDb.OleDbCommand
    cmd.Connection = con
    cmd.CommandText = "CREATE TABLE MYTABLENAME;"
    nAffected = cmd.ExecuteNonQuery
End If

Welldone

Answer 3

SQL error will throw OleDbException and you can check SQLState for specified error. If the table does not exists, The code should be 3376 according to Microsoft's documentation. So your code should look like

catch (OleDbException e)
{ 
    switch (dbException.Errors[0].SQLState)
    {
        case "3376":
            MessageBox.Show(dbException.Errors[0].Message); // or any message
            break;
        default:
            MessageBox.Show(e.Message);
    }
}

Petrozavodsk