title: Dynamically insert into SQLite from text file
body: <p>I am trying to create dynamical insert statements from text file, with only SQLite. </p>
  
  <p>What I have done so far, is to create the SQL query with necessary parameters, add those parameters during run time, and try to select.</p>
  
  <p>However I get error inside try block, when try to <code>cmd.ExecuteNonQuery();</code></p>
  
  <blockquote>
    <p>Caught exception: SQL logic error or missing database
    near "@0": syntax error </p>
  </blockquote>
  
  <pre><code>using System;
  using System.Data.SQLite;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  using System.IO;
  using System.Data.SqlClient;
  
  namespace TestApp
  
      class Program
      {
          static void Main(string[] args)
          {
             //To store the index and column name in the file
             Dictionary&lt;int, string&gt; Columns = new Dictionary&lt;int, string&gt;();
             char[] delimiterChars = { '\t' };
              string createQuery = @" create table if not exists products(id integer not null primary key, name text);
                                  insert into products (name) values ('A');
                                  insert into products (name) values ('B');
                                  insert into products (name) values ('C');
                                  insert into products (name) values ('D');
                                  insert into products (name) values ('E');
                                  insert into products (name) values ('F');
                                  insert into products (name) values ('G');
                                  create table if not exists orders(id integer, dt datetime, product_id integer, amount real);"; 
          System.Data.SQLite.SQLiteConnection.CreateFile("myDB.db3");
          using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=myDB.db3")){
              using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn)){
                  conn.Open();
                  cmd.CommandText = createQuery;
                  cmd.ExecuteNonQuery();
                  string[] lines = System.IO.File.ReadAllLines(Directory.GetCurrentDirectory() + @"../../../App_Data/import.txt");
                  cmd.CommandText = "INSERT INTO orders (";
                  // Identify the column order from first row of the import file
                  string[] elements = lines[0].Split(delimiterChars);
                  for (int i = 0; i &lt; elements.Length; i++)
                  {
                      Columns[i] = elements[i];
                      cmd.CommandText = cmd.CommandText + "@COLUMN" + i + ", ";
                      cmd.Parameters.AddWithValue("@COLUMN" + i, Columns[i]);
                      System.Console.WriteLine(i + " : " + Columns[i]);
  
                  }
                  cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 2);
                  cmd.CommandText = cmd.CommandText + ") VALUES (";
                  string temp = cmd.CommandText;
                  System.Console.WriteLine(cmd.CommandText);
                  System.Console.WriteLine("Contents of Import File.txt = ");
                  for (int i = 1; i &lt; lines.Length; i++)
                  {
                      cmd.CommandText = temp;
                      elements = lines[i].Split(delimiterChars);
                      for (int j = 0; j &lt; elements.Length; j++)
                      {
                          cmd.CommandText = cmd.CommandText + "@VALUE" + j + ", ";
                          cmd.Parameters.AddWithValue("@VALUE" + j, elements[j]);
                      }
                      cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 2);
                      cmd.CommandText = cmd.CommandText + ")";
                      try
                      {
                          cmd.ExecuteNonQuery();
                      }
                      catch (Exception ex)
                      {
                          Console.WriteLine("Caught exception: " + ex.Message);
                      }
  
                      Console.WriteLine(cmd.CommandText);
                      Console.WriteLine(lines[i]);
                  }
  
                  cmd.CommandText = "Select * from orders";
                  cmd.ExecuteNonQuery();
                  using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader()){
                      while (reader.Read())
                      {
                          Console.WriteLine(reader["ID"] + " | " + reader["dt"] + " | " + reader["product_id"] + " | " + reader["amount"]);
                      }
                      conn.Close();
                  }
              }
          }
          Console.WriteLine("Press any key to exit.");
          Console.ReadLine();
      }
  }
  }
  </code></pre>
  
  <p>I am not sure on what am I doing wrong? </p>
  
  <p>The import.txt file consists of </p>
  
  <pre><code>id  dt  amount  product_id
  1   2017-02-01T10:01:12 343.33  1
  2   2017-02-01T10:02:12 12  2
  3   2017-03-01T10:03:12 344.3   1
  4   2017-04-01T10:04:12 12  3
  5   2017-05-01T10:05:12 66.5    1
  6   2017-06-01T10:06:12 4   
  </code></pre>
  
  <p>All items divided by TAB</p>