SQLite Insert from Datatable

Question

Using the below I get an exception with the @table part of the query. Can you use data tables to insert into SQLite this way?

 DataTable table = new DataTable();
 table.Columns.Add("Path", typeof(string));
 table.Columns.Add("StopName", typeof(string));
 table.Columns.Add("Latitude", typeof(string));
 table.Columns.Add("Longitude", typeof(string));

 foreach (Result result in tempResults)
 {
      table.Rows.Add(result.Path, result.StopName, result.Latitude, result.Longitude);
 }

 SQLiteCommand command = new SQLiteCommand("INSERT OR REPLACE INTO ZZ_DBA_Stop (Path, StopName, Latitude, Longitude) SELECT Path, StopName, Latitude, Longitude FROM @table", connection) { CommandTimeout = 3600, CommandType = CommandType.Text };
 command.Parameters.AddWithValue("@table", table);
 await command.ExecuteNonQueryAsync();

Answer 1

You can't pass DataTable as a parameter. I think the main reason that you want use DataTable as parameter is that you want to bulk insert in sqlite. This is an example

using (var transaction = connection.BeginTransaction())
using (var command = connection.CreateCommand())
{
    command.CommandText =
        "INSERT INTO contact(name, email) " +
        "VALUES($name, $email);";

    var nameParameter = command.CreateParameter();
    nameParameter.ParameterName = "$name";
    command.Parameters.Add(nameParameter);

    var emailParameter = command.CreateParameter();
    emailParameter.ParameterName = "$email";
    command.Parameters.Add(emailParameter);

    foreach (var contact in contacts)
    {
        nameParameter.Value = contact.Name ?? DBNull.Value;
        emailParameter.Value = contact.Email ?? DBNull.Value;
        command.ExecuteNonQuery();
    }

    transaction.Commit();
}

Reference: Bulk Insert in Microsoft.Data.Sqlite