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)
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:
ReplyDeleteSELECT name FROM sqlite_master WHERE type='table';
oh! i didn't know this! thanks for the information :D
ReplyDeletebecause i define my connection within the class and want to make sure the connection is Closed(), this is the way I have it defined;
ReplyDeletepublic 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;
}
Thanks for your sharing! :)
ReplyDeleteIt's my pleasure to start the interesting discussion by the blog post. Hope these information helps somebody in need :)