Someone please tell me how can "check if a table exists in sqlite db".

I want to insert logs my db. I want to create a log so I can view it later. I don't want to have all the data in one table. As a result, I wanted to create a separate table for each day but I can't process to query if a table exists.I tried using this line of code but

DateTime time = DateTime.Now;
        string logname = time.ToString("MM-dd");
        SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=data.db3;Version=3;");
        m_dbConnection.Open();
        string sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=@p1;";
        SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
        command = m_dbConnection.CreateCommand();
        command.Parameters.AddWithValue("@p1", logname);
        SQLiteDataReader rdr = command.ExecuteReader();

I always get null value in this line of code even when the table exists

SQLiteDataReader rdr = command.ExecuteReader();

it's my code and i try sql string like this

SELECT name FROM sqlite_master WHERE type='table' AND name=@p1;
SELECT name FROM data WHERE type='table' AND name=@p1;
SELECT name FROM data WHERE type = 'table' AND name = '@p1';

its my code

public static void create_dayend_kk(double ht)
    {
        string apppath = AppDomain.CurrentDomain.BaseDirectory;
        DateTime time = DateTime.Now;
        string logname = time.ToString("MM-dd");
        SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=data.db3;Version=3;");
        m_dbConnection.Open();
        string sql = "SELECT name FROM data WHERE type = 'table' AND name = '@p1';";
        SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
        command = m_dbConnection.CreateCommand();
        command.Parameters.AddWithValue("@p1", logname);
        SQLiteDataReader rdr = command.ExecuteReader();
        int intout = rdr.GetInt32(0);
        if (intout == 0)
        {
            sql = "create table @p1 (ID INTEGER, KK REAL, NK REAL, TK REAL,PRIMARY KEY(ID AUTOINCREMENT))";
            command = m_dbConnection.CreateCommand();
            command.Parameters.AddWithValue("@p1", logname);
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            sql = "insert into @p1 (KK, TK) values (@p2, @p3)";
            command.Parameters.AddWithValue("@p1", logname);
            command.Parameters.AddWithValue("@p2", ht);
            command.Parameters.AddWithValue("@p3", ht);
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
        }
        else if (intout == 1)
        {
            sql = "SELECT * FROM @p1";
            command = m_dbConnection.CreateCommand();
            command.Parameters.AddWithValue("@p1", logname);
            rdr = command.ExecuteReader();
            double kk = rdr.GetFloat(1);
            double nk = rdr.GetFloat(2);
            double tk = rdr.GetFloat(3);
            tk  = ht;
            sql = "insert into @p1 (KK, TK) values (@p2, @p3)";
            command.Parameters.AddWithValue("@p1", logname);
            command.Parameters.AddWithValue("@p2", ht);
            command.Parameters.AddWithValue("@p3", tk);
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();

        }

        m_dbConnection.Close();

        
    }

Please tell me how i can? What’s going wrong?

CodePudding user response：

To start, and know the structure of the SQLite_Master table, getting the schema as shown below.

sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

What I did next was to create a class structure to match

    private class CSQLite_Master
    {
        public string type { get; set; }
        public string name {get; set;}
        public string tbl_name { get; set; }
        public int rootpage { get; set; }
        public string sql { get; set; }
    }

From that, I used the connection .Query() feature like below

using (var db = new SQLiteConnection(YourPathToDatabase) )
{
   var tmp1 = db.Query<CSQLite_Master>(
        @"select * from sqlite_master where type = 'table' and name = ?", 
            new object[] { logname });
   if( tmp1 is null || tmp1.Count == 0 )
      return;

   // else we DO have an entry, and you can see all the parts of it
   // by doing basic debug inspection in the watch window 
}

The .Query executes whatever command available, and based on the class/structure <CSQLite_Master> in this case, the data is retrieved into a list of this object type. Now for the parameter. The "?" is a place-holder for the parameters to be provided.

The last parameter to the Query() call is an array of values added in the same sequence as the "?" place-holders. In this case, a list of objects new object[] { logname } and that list is created with the logname you prepared.

You should get either the record, or records if you did not include the AND name= portion of the query, it would return all table names, for you.

CodePudding user response：

By this command you can check table is exist or no:

SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';

this, return lists of special table.