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();

No, parameters cannot be used in the FROM argument. Anyway, you cannot pass a DataTable and hope that it will be used to insert into a database table. — Steve, Apr 15 '19 at 20:12

score 2 · Answer 1 · answered Apr 15 '19 at 20:29

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

Steve · Answer 2 · 2019-04-16T22:06:03.260

Unfortunately, parameters cannot be used to express names for tables or columns. You can use them only to express values in WHERE statement or in UPDATE/INSERT/DELETE operation.

So you should insert your records one by one, or write code to support batch updates like explained in this question

However if you want to experiment with a very useful thirdy party library you can write a very simple code.

This example is done using Dapper

NuGet
Project Site

using(SQLiteConnection connection = GetOpenedConnection())
{
    string cmdText = @"INSERT OR REPLACE INTO ZZ_DBA_Stop 
                      (Path, StopName, Latitude, Longitude) 
                      VALUES(@Path, @StopName, @Latitude, @Longitude) ";
    connection.ExecuteAsync(cmdText, tempResults);
}

Dapper is a simple ORM that extends the functionality of an IDbConnection. It knows how to handle your models and store and retrieve them from the database.
In the example above you pass your whole list as the second parameter to the ExecuteAsync and Dapper will insert for you the data from the whole list. The only requirement here is that your model's properties have the same name of the fields

GetOpenedConnection is just a placeholder for a method that returns an SQLiteConnection already opened. You can replace it with the code required to create the connection and add a call to open before calling the ExecuteAsync

SQLite Insert from Datatable

2 Answers2