Insert multiple rows into sdf file database with one insert

Question

I have function to insert into my SQL Server CE database.

private void update_database(string SQL_string)
{
        DBconnection.Open();
        SqlCeCommand SQL_querry = DBconnection.CreateCommand();
        SQL_querry.CommandText = SQL_string;
        SQL_querry.ExecuteNonQuery();
        DBconnection.Close();
}

I'm passing to it SQL string calling

SQL_string = @"insert into Cities(City, destination, distance) values('liege','aberdeen','386'), ('liege','kassel','348');";
update_database(SQL_string);

and it throws an exception:

There was an error parsing the query. [ Token line number = 1,Token line offset = 81,Token in error = , ]"

which clearly states, that problem is with comma after first set of values... What is the most confusing for me is that when passing this sql_string:

SQL_string = @"insert into Cities(City, destination, distance) values('liege','aberdeen','386');";

it works without any problem.

Does SqlCeCommand accept multirow insertions? What am I missing here?

Answer 1

I can't imagine why something like the following wouldn't work. (I separated the lines for ease of readability).

var SQL_string = "insert into Cities(City, destination, distance)";
SQL_string += " SELECT 'liege', 'aberdeen', '386'";
SQL_string += " UNION";
SQL_string += " SELECT 'liege', 'kassel', '348'";
update_database(SQL_string);

Answer 2

Try this;

SQL_string = @"insert into Cities(City, destination, distance) values('liege','aberdeen','386');";
SQL_string += @"insert into Cities(City, destination, distance) values('liege','kassel','348');";
update_database(SQL_string);

Tiny note: If this is a homework or a simple project this may suffice but for anything bigger I recommend using a framework to deal with DB. For example; this code suspiciously looks vulnerable to SQL Injection :)

Answer 3

You can't do this in SQL Server Compact Edition. However you should be able to either execute individual insert statements, or insert multiple rows from a derived table. https://msdn.microsoft.com/en-us/library/ms174633

INSERT INTO Cities(City, destination, distance)
SELECT s.City, s.Destination, s.distance
FROM (
    SELECT 'liege' AS CITY, 'aberdeen' AS DESTINATION, '386' AS DISTANCE
    UNION ALL
    SELECT 'liege' AS CITY, 'kassel' AS DESTINATION, '348' AS DISTANCE
) s