Best way to check if item exists

Sep 28, 2011 at 4:15 PM

I am trying to address schema update scenario. I'd like to know of the best way to check existence of

  • Table
  • Column
  • Index

I have made my own versions, but those rely heavily on exceptions. Is there an efficient non-exception way?

Here is what I've got:

        private static void CreateOrOpenTable(string tableName, Session session, JET_DBID dbid, out JET_TABLEID tid) {
            try {
                Api.JetOpenTable(session, dbid, tableName, null, 0, OpenTableGrbit.PermitDDL & OpenTableGrbit.DenyRead, out tid);
                Log.TraceEvent(TraceEventType.Verbose, EvIdUndef, tableName + ": opened existing table.");
            } catch (EsentObjectNotFoundException ex) {
                //Log.TraceEvent(TraceEventType.Verbose, EvIdUndef, tableName + ": table does not exist. Message: " + ex.Message);
                ex.ToString();
                Api.JetCreateTable(session, dbid, tableName, 10, 80, out tid);
            }
            //if (Api.TryOpenTable(session, dbid, tableName, OpenTableGrbit.DenyWrite, out tid)) {
            //    // table exists; use it
            //    Log.TraceInformation(tableName + ": table exists.");
            //} else {
            //    Log.TraceInformation(tableName + ": table does not exist.");
            //    Api.JetCreateTable(session, dbid, tableName, 10, 80, out tid);
            //}
        }

         private static bool ColumnExists(Session session, JET_DBID dbid, string tableName, string columnName) {
            bool exists = false;
            try {
                JET_COLUMNDEF columnDef = new JET_COLUMNDEF();
                Api.JetGetColumnInfo(session, dbid, tableName, columnName, out columnDef);
                exists = true;
            } catch (EsentColumnNotFoundException ex) {
                /* exception type is expected; ignore it. */
                ex.ToString();
            }
            return exists;
        }

        private static bool IndexExists(Session session, JET_TABLEID tid, string indexName) {
            JET_INDEXID indexInfo;
            try {
                Api.JetGetTableIndexInfo(session, tid, indexName, out indexInfo, JET_IdxInfo.IndexId);
                return true;
            } catch (EsentIndexNotFoundException ex) {
                // expected ignored exception
                ex.ToString();
                return false;
            }
        }

Developer
Oct 4, 2011 at 2:40 AM

Exceptions are indeed expensive. It's the Api class that creates exceptions. You can avoid exceptions by calling the JetApi varieties (e.g. JetApi.GetJetColumnInfo()), and check the integer return value for JET_err.Success.

It's also possible to enumerate all of the tables/columns/indices, but it is somewhat cumbersome, involving traversing a temptable. If it's only a few specific objects you're interested in, and you know their names, then your approach is much more legible.

 

-martin

Oct 4, 2011 at 4:42 PM

There are several calls that you can use:

Api.TryOpenTable: Opens a table but returns a boolean instead of throwing an exception if the table doesn't exist.

Api.GetTableNames: Gets a list of all tables in the database

Api.GetTableIndexes: Gets a list of all indexes on a table

Api.GetColumnDictionary/GetTableColumns: Gets all columns from a table

The best thing to do is probably to use GetTableNames/GetTableIndexes/GetTableColumns to fully enumerate the meta-data and compare it against the intended meta-data. That will be a lot easier and faster than enumerating the tables, indexes and columns individually.