Vahid Vahid - 13 days ago 6
C# Question

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

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());
}

Answer

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. :(

Comments