Wednesday, May 23

Compact Framework : Table Exist Method

In PDA based database applications one has to sometimes pull database/tables from the host machine/server onto the PDA (such as specific insurance agent's collection data). The procedure used is Remote Data Access (RDA). Compact framework has a stripped down version of the framework and some functionality available in the full framework isn't present on it.

I had a condition where I needed to I needed to check if a certain table existed on the pda database and if not then pull it from the server. I needed a table exist method and wanted to avoid the try catch code block which I have seen in use to derive that a table doesn't exist if you get a exception on something like this
'select (count(*) from premiums;'

I knew there would be a cleaner way to know if a table exist in a database or not.

Here is the method :

We query the meta data and get a clean answer, no try/catch needed derive the answer.

  430         /// 
  431         /// Queries the information schema to find whether a table exists or not
  432         /// 
  433         /// 
  434         /// 
  435         private bool TableExists(string table)
  436         {
  437             bool retAnswer = false;
  438             try
  439             {
  440                 int exist = (int)ConnectionManager.DBInfoConnection.ExecuteScalar(
  441                     "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='" + table + "'");
  442                 if (exist == 1)
  443                     retAnswer = true;
  444             }
  445             catch (Exception ex)
  446             {
  447                 GenericLogger.Error("Could not query database for list of tables", ex);
  448             }
  449             return retAnswer;
  450         }
Please ignore the non use of SqlParameters. I had to add this as a maintainence method in an 
2-3 years old app and when you have to decide between your urge to make drastic(essential) 
changes to a system and the number of hours that have been alotted 
to this task, I think pragmatism wins.

Comments: Post a Comment

Subscribe to Post Comments [Atom]

<< Home

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]