SQLite - how to check if table exists?

Does anyone know how to check if a SQLite table exists? I've been Googling and trying things to no avail :(

Posts

  • TeHaTeHa DEMember ✭✭✭

    1.) Solution:

    SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'YourTableName'

    2.) Solution:

    try to select some data from the table within a try catch block

  • Le-royStaines.7824Le-royStaines.7824 NZMember ✭✭✭

    Solution 1 - I tried running a query but I couldn't get it to work. Is there any chance you could elaborate by showing the C# implementation of this?

    Solution 2 - We could use this but it just feels like a messy solution! I was hoping for something simple like a TableExists method or something.

  • Le-royStaines.7824Le-royStaines.7824 NZMember ✭✭✭

    Ok I tried this...

    object[] ps = new object[0];
    Int32 tableCount = conn.Execute("SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'JobInstance'", ps);

    But I get exception "Row"

    Stack trace:

    SQLite.SQLiteException: Row
    at SQLite.SQLiteCommand.ExecuteNonQuery () [0x0008d] in d:\Dropbox\Mobile Time Clock\Android Apps\Mobile Time Clock\Mobile Time Clock\Mobile Time Clock\Resources\Classes\Database\SQLite.cs:1524

    at SQLite.SQLiteConnection.Execute (string,object[]) [0x0004e] in d:\Dropbox\Mobile Time Clock\Android Apps\Mobile Time Clock\Mobile Time Clock\Mobile Time Clock\Resources\Classes\Database\SQLite.cs:467

    at Mobile_Time_Clock.Data.CreateDatabaseIfItDoesntExist () [0x00029] in d:\Dropbox\Mobile Time Clock\Android Apps\Mobile Time Clock\Mobile Time Clock\Mobile Time Clock\Resources\Classes\Database\Data.cs:55

    at Mobile_Time_Clock.MainMenuActivity.OnCreate (Android.OS.Bundle) [0x0001d] in d:\Dropbox\Mobile Time Clock\Android Apps\Mobile Time Clock\Mobile Time Clock\Mobile Time Clock\MainMenuActivity.cs:58

    at Android.App.Activity.n_OnCreate_Landroid_os_Bundle_ (intptr,intptr,intptr) [0x00010] in /Users/builder/data/lanes/monodroid-mac-monodroid-4.4-series/6418373f/source/monodroid/src/Mono.Android/platforms/android-10/src/generated/Android.App.Activity.cs:1490

    at (wrapper dynamic-method) object.193ab304-67c7-450e-af76-f82f92b25b07 (intptr,intptr,intptr)

  • Le-royStaines.7824Le-royStaines.7824 NZMember ✭✭✭

    Problem solved!

    I'm not a C# guru so please excuse if it's untidy. But it does the job...

            /// <summary>
            /// Checks the database to see if the table exists
            /// </summary>
            public static Boolean TableExists (String tableName, SQLiteConnection connection)
            {
                SQLite.TableMapping map = new TableMapping (typeof(SqlDbType)); // Instead of mapping to a specific table just map the whole database type
                object[] ps = new object[0]; // An empty parameters object since I never worked out how to use it properly! (At least I'm honest)
    
                Int32 tableCount = connection.Query (map, "SELECT * FROM sqlite_master WHERE type = 'table' AND name = '" + tableName + "'", ps).Count; // Executes the query from which we can count the results
                if (tableCount == 0) {
                    return false;
                } else if (tableCount == 1) {
                    return true;
                } else {
                    throw new Exception("More than one table by the name of " + tableName + " exists in the database.",null);
                }
    
            }
    
  • TeHaTeHa DEMember ✭✭✭

    I would do something like this:

    public static bool TableExists (String tableName, SQLiteConnection connection)
    {
      SqliteCommand cmd = connection.CreateCommand();
      cmd.CommandText = "SELECT * FROM sqlite_master WHERE type = 'table' AND name = @name";
      cmd.Parameters.Add("@name", DbType.String).Value = tableName;
      return (cmd.ExecuteScalar() != null);
    }
    

    But beware, I've written the code blind. And maybe enclose the 'Execute' method with a try catch.

  • EternalBardEternalBard BRBeta ✭✭

    Another variation:

    public static bool TableExists (String tableName, SQLiteConnection connection)
    {    
        using (SqliteCommand cmd = new SqliteCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;
            cmd.CommandText = "SELECT COUNT(*) AS QtRecords FROM sqlite_master WHERE type = 'table' AND name = @name";
            cmd.Parameters.AddWithValue("@name", tableName);
            if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
                return false;
            else
                return true;
        }
    }
    

    or:

    public static bool TableExists (String tableName, SQLiteConnection connection)
    {
        using (SqliteCommand cmd = new SqliteCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connection;
            cmd.CommandText = "SELECT * FROM sqlite_master WHERE type = 'table' AND name = @name";
            cmd.Parameters.AddWithValue("@name", tableName);
    
            using (SqliteDataReader sqlDataReader = cmd.ExecuteReader())
            {
                if (sqlDataReader.Read())
                    return true;
                else
                    return false;
            }
        }
    }
    
  • kabilanakabilana USMember

    public async Task<bool> IsDbExists(string fileName) { try { var item = await ApplicationData.Current.LocalFolder.GetFileAsync(fileName); var db = new SQLiteConnection("Your db path"); var tb1 = db.GetTableInfo("TableName1"); var tb2 = db.GetTableInfo("TableName2"); var tb3 = db.GetTableInfo("TableName3"); var tb4 = db.GetTableInfo("TableName4"); if (item == null || tb1.Count == 0 || tb2.Count == 0 || tb3.Count == 0 || tb4.Count == 0) { return false; } else { return true; } } catch { return false; } }

  • jvinhitjvinhit USMember

    thanks all!!! usefull

  • ChitoSalanoChitoSalano PHUniversity ✭✭

    Just an example and not a good implementation, but hopes this helps.

    //package used

    //model
    [Table("Job")]
    public class Job : IBusinessEntity
    {
    //some properties here
    }

    //sqlite
    public class JobSQLite : SQLiteDB
    {
    public override int GetCountFromTable()
    {
    string typeName = typeof(Job).Name;
    int count = 0;
    string cmdText = "SELECT COUNT(*) AS NoOfRecords FROM sqlite_master WHERE type='table' AND name=?";
    var cmdNonAsync = connNonAsync.CreateCommand(cmdText, typeName);

            try
            {
                Debug.WriteLine("Checking if the {0} table is existing already.", typeName);
                count = cmdNonAsync.ExecuteScalar<Int32>();
            }
            catch (SQLiteException sqlEx)
            {
                Debug.WriteLine("An error occured in AddressSQLiteDB {0}.", sqlEx.Message);
                result = -1;
            }
            catch (Exception ex)
            {
                Debug.WriteLine("An error occured in AddressSQLiteDB {0}", ex.Message);
                result = -1;
            }
    
            return count;
        }
    
        public override bool IsCreated
        {
            get
            {
                if (GetCountFromTable() > 0)
                    return true;
    
                return false;            
            }
        }
    
    }
    
  • TchelidzeTchelidze GEMember
    edited December 2015

    Worked.

  • YooPitaYooPita Member

    Make it.

  • clafferteclafferte Member

    This work for me.-

        public virtual bool TableExists(string tableName)
        {
            bool sw = false;
            try
            {
                using (var connection = new SQLiteConnection(new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid(), PathDataBase))
                {
                    string query = string.Format("SELECT name FROM sqlite_master WHERE type='table' AND name='{0}';", tableName);
                    SQLiteCommand cmd = connection.CreateCommand(query);
                    var item = connection.Query<object>(query);
                    if (item.Count > 0)
                        sw = true;
                    return sw;
                }
            }
            catch (SQLiteException ex)
            {
                Log.Info("SQLiteEx", ex.Message);
                throw;
            }
        }
    
  • DimChrisDimChris USMember ✭✭✭
    select exists(
        select 1
        from tbl_stats_assigned
        where username = 'abc'
    );
    

    If result is 1 then it exists else if result is 0 it doesn't

  • renanbarbosarenanbarbosa Member ✭✭
    edited January 15

    `[Table("TB_DRIVER")]
    public class Driver
    {
    [Column("ID"), PrimaryKey, NotNull]
    public int ID { get; set; }

    [Column("Name")]
        public string Name { get; set; }
    

    }`

    `public bool ExistsTable()
    {
    bool result;
    SQLiteCommand objSqlCommmand;

            try
            {
                objSqlCommmand = objSqlConnection.CreateCommand("SELECT name FROM sqlite_master WHERE type='table' AND name=?", objSqlConnection.GetMapping<T>().TableName);
    
                result = (objSqlCommmand.ExecuteScalar<string>() != null);
            }
            catch (Exception)
            {
                // Define o resultado como falso
                result = false;
            }
    
            return rResult;
        }`
    
  • Esaavedra89Esaavedra89 USMember ✭✭

    @Le-royStaines.7824 said:
    Problem solved!

    I'm not a C# guru so please excuse if it's untidy. But it does the job...

          /// <summary>
          /// Checks the database to see if the table exists
          /// </summary>
          public static Boolean TableExists (String tableName, SQLiteConnection connection)
          {
              SQLite.TableMapping map = new TableMapping (typeof(SqlDbType)); // Instead of mapping to a specific table just map the whole database type
              object[] ps = new object[0]; // An empty parameters object since I never worked out how to use it properly! (At least I'm honest)
    
              Int32 tableCount = connection.Query (map, "SELECT * FROM sqlite_master WHERE type = 'table' AND name = '" + tableName + "'", ps).Count; // Executes the query from which we can count the results
              if (tableCount == 0) {
                  return false;
              } else if (tableCount == 1) {
                  return true;
              } else {
                  throw new Exception("More than one table by the name of " + tableName + " exists in the database.",null);
              }
    
          }
    

    It's works to me with this:

    object[] objeto = new object[0];

    var list = conexion.Query("SELECT IdRuta, Codigo, Nombre FROM Ruta WHERE IdCompany = " + idCompany , objeto);

    To get a list of objects

    Thanks!

Sign In or Register to comment.