CODEWITHSUNDEEP
c#sqlite
Sebastian
Sebastian

Reputation: 43

SQLite: create table and add a row if the table doesn't exist

I have a problem with a query in SQLite. I can't achieve my goal because I get a syntax error. I'm trying to check if table accounts exists and if not exists create it and add a row. I've tried several codes, and the last one was this:

IF NOT EXISTS (SELECT * FROM account) THEN
   BEGIN
       CREATE TABLE account (rowID INT, user VARCHAR(20), pass VARCHAR(20))
       INSERT INTO account (rowID, user, pass) VALUES (0, '', '')
   END

I'm using SQLite 3 with C#

I can get it to work with the following code:

CREATE TABLE IF NOT EXISTS account (rowID INT, user VARCHAR(20), pass VARCHAR(20))

But I don't know how to add here the part of inserting a new row. The row must be only inserted if the table is created at the moment, so I can't do the work in 2 queries.

Upvotes: 4

Views: 12976

Answers (2)

Varun
Varun

Reputation: 1

using (SQLiteConnection con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"))
using (SQLiteCommand command = con.CreateCommand())

    con.Open();
    command.CommandText = "SELECT name FROM sqlite_master WHERE name='account'";
    var name = command.ExecuteScalar();

    // check account table exist or not 
    // if exist do nothing 
    if (name == null)
       {
        // account table not exist, create table and insert 
          command.CommandText = "CREATE TABLE account (rowID INT, user VARCHAR(20), pass   VARCHAR(20))";
          command.ExecuteNonQuery();
          command.CommandText = "INSERT INTO account (rowID, user, pass) VALUES (0, '', '')";
          command.ExecuteNonQuery();
       }

Upvotes: 0

Damith
Damith

Reputation: 63065

try below code:

 using (SQLiteConnection con = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"))
using (SQLiteCommand command = con.CreateCommand())
{
    con.Open();
    command.CommandText = "SELECT name FROM sqlite_master WHERE name='account'";
    var name = command.ExecuteScalar();

    // check account table exist or not 
    // if exist do nothing 
    if (name != null && name.ToString() == "account")
        return;
    // acount table not exist, create table and insert 
    command.CommandText = "CREATE TABLE account (rowID INT, user VARCHAR(20), pass VARCHAR(20))";
    command.ExecuteNonQuery();
    command.CommandText = "INSERT INTO account (rowID, user, pass) VALUES (0, '', '')";
    command.ExecuteNonQuery();
}

Upvotes: 3

Related Questions