Check for MS Access database table if not exist create it

Question

How do you programmatically check for MS Access database table, if not exist then create it?

Answer 1

To check if a table exists you can extend DbConnection like this:

public static class DbConnectionExtensions
{
    public static bool TableExists(this DbConnection conn, string table)
    {
        conn.open();
        var exists = conn.GetSchema("Tables", new string[4] { null, null, table, "TABLE" }).Rows.Count > 0;
        conn.close();
        return exists;
    }
}

Then you can call TableExists in any derived class like OleDbConnection, SQLiteConnection or SqlConnection.

Answer 2

You could iterate though the table names to check for a specific table. See the below code to get the table names.

        string connectionstring = "Your connection string";
        string[] restrictionValues = new string[4]{null,null,null,"TABLE"};
        OleDbConnection oleDbCon = new OleDbConnection(connectionString);
        List<string> tableNames = new List<string>();

        try
        {
            oleDbCon.Open();
            DataTable schemaInformation = oleDbCon.GetSchema("Tables", restrictionValues);

            foreach (DataRow row in schemaInformation.Rows)
            {
               tableNames.Add(row.ItemArray[2].ToString());
            }
        }
        finally
        {
            oleDbCon.Close();
        }