Search this site:

2008-12-30

Check if datablase table exists


I am recently working with SQLite database and I was stuck with a very simple task: writing a function to create a table, existing table should be dropped. In other database engines this can be achieved using a simple CREATE OR REPLACE TABLE statement but this is not supported in current version of SQLite. Therefore I need to check the existence of a table before running the CREATE TABLE statement, but how?

Having a simple Google search, I got the clue at here: using the DbConnection.GetSchema() method.

Here is the complete code:

public static bool IsTableExists(SQLiteConnection conn, string table_name)
{
return conn.GetSchema("Tables").Select("Table_Name = '" + table_name + "'").Length > 0;
}


Since this is a method defined in ADO standard, so probably it will work for those ADO implementations of other database engine. (Not tested)


4 comments:

  1. If it is programming in python , pysqlite do not support similar operation. Instead , it need to query the existing available tables from sqlite_master table:

    SELECT name FROM sqlite_master WHERE type='table';

    ReplyDelete
  2. oh! i didn't know this! thanks for the information :D

    ReplyDelete
  3. because i define my connection within the class and want to make sure the connection is Closed(), this is the way I have it defined;

    public static bool IsTableExists(string table_name)
    {
    string connStr = @"data source=""F:\DEV\Visual Studio 2008\Projects\Prices2\prices.s3db""";
    SQLiteConnection conn = new SQLiteConnection(connStr);
    conn.Open();
    bool result = conn.GetSchema("Tables").Select("Table_Name = '" + table_name + "'").Length > 0;
    conn.Close();
    return result;
    }

    ReplyDelete
  4. Thanks for your sharing! :)
    It's my pleasure to start the interesting discussion by the blog post. Hope these information helps somebody in need :)

    ReplyDelete

HTML Tags allowed (e.g. <b>, <i>, <a>)

Disclaimer

ALL CONTENTS AND INFORMATION IN THIS WEB SITE ARE PROVIDED "AS IT" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED. THE ACCURACY AND AVAILABILITY OF THE CONTENTS, INFORMATION AND THE WEB SITE ITSELF ARE NOT GUARANTEED. THE AUTHOR TAKES NO RESPONSIBILITIES ON ANY COSTS OR DAMAGES (DIRECT OR INDIRECT) ARISING OUT OF OR IN CONNECTION WITH THE ACCESS, USAGE OR INABILITY OF USAGE OF THIS WEB SITE.