CODEWITHSUNDEEP
c#.netoledbdatareaderoledbconnection
Vahid
Vahid

Reputation: 5444

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

Upvotes: 5

Views: 7155

Answers (3)

Siebe Bosch
Siebe Bosch

Reputation: 31

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

Upvotes: 2

tia
tia

Reputation: 9698

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

Upvotes: 1

svinja
svinja

Reputation: 5576

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

Upvotes: 5

Related Questions