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.
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();
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句