CODEWITHSUNDEEP
c#sqlite
Den Nemoff
Den Nemoff

Reputation: 31

IF EXISTS when creating table query

I need to write q query where I check if the table exists, and if it does I need to insert value in there. I'm always getting compile errors cause my syntax is wrong. Cab anyone please point out to me the proper code?Appreciate it

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{

    public Form1()
    {
        InitializeComponent();
        createDataBase();
        createDataTable();
    }

    private void createDataBase()
    {
       SQLiteDataAdapter  dataBase = new SQLiteDataAdapter();
    }


    private SQLiteConnection getConnection() 
    { 
        return new SQLiteConnection("Data Source=file1.db"); 
    }


    private void ExecuteQuery(string txtQuery)
    {            
     using (SQLiteConnection sqlcon =  getConnection())
     {
            using (SQLiteCommand sqlcmd = sqlcon.CreateCommand())
            {
                sqlcmd.CommandText = txtQuery;
                sqlcon.Open();
                sqlcmd.ExecuteNonQuery();
            }
        }
    }


    private void createDataTable()
    {
        //ExecuteQuery("DROP TABLE IF EXISTS 'RECORDS'");
        //ExecuteQuery("CREATE TABLE RECORDS ( ID varchar(255))");
        //ExecuteQuery("CREATE TABLE (IF NOT EXISTS) 'RECORDS'");
        //("IF (EXISTS (SELECT ID FROM sqlite_master WHERE NAME = 'RECORDS'))");
    }


    private void button1_Click(object sender, EventArgs e)
    {
          AddValue(textBox1.Text);  
        ////ExecuteQuery("IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> NULL AND TABLE_NAME = 'RECORDS')) BEGIN INSERT INTO RECORDS (ID) VALUES ('" + textBox1.Text + "')");    
    }


    private void AddValue(string value)
    {
      ExecuteQuery("INSERT INTO RECORDS (ID) VALUES ('" + value + "')");
    }
}

}

Upvotes: 0

Views: 3296

Answers (2)

Steve
Steve

Reputation: 216243

This is an example 

  SQLiteCommand cmd = new SQLiteCommand();
  cmd.Connection = con;
  cmd.Connection.Open();
  cmd.CommandText = "SELECT name FROM sqlite_master WHERE name='MAIN'";
  SQLiteDataReader rdr = cmd.ExecuteReader();
  if (rdr.HasRows)
  {
        cmd.CommandText = "DROP TABLE 'MAIN'";
        cmd.ExecuteNonQuery();
  }

but this is directly from the SQLite site

  SQLiteCommand cmd = new SQLiteCommand();
  cmd.Connection = con;
  cmd.CommandText = "DROP TABLE IF EXIST 'MAIN'";
  cmd.Connection.Open();
  cmd.ExecuteNonQuery();

Upvotes: 1

s_nair
s_nair

Reputation: 812

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --your insert logic
END

Upvotes: 1

Related Questions