If you are a newbie at Windows Mobile development and you need to create an application which is using SQLCE database, you might have bumped into this problem. Inserting multiple records into SQLCE database is quite different from doing the same thing on full blown SQL Server.

If you are like me, a developer who likes to be in control of it’s source code you are most likely using INSERT INTO statements to add new records to your database. Doing that on a SQL Server is not a new thing and it is a perfectly normal code to insert new records into your tables. Running this query on a SQL Server would get you to three new rows in a table:

INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyFirstVal, MySecondVal);
INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyThirdVal, MyFourthVal);
INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyFifthVal, MySixthVal);

Or you might have been using this variant:

INSERT INTO MyTable (MyFirstCol, MySecondCol) SELECT 'MyFirstVal' , 'MySecondVal' UNION ALL SELECT 'MyThirdVal' , 'MyFourthVal' UNION ALL SELECT 'MyFifthVal' , 'MySixthVal'

SQL Server 2008 made it even easyer and all you had to do is run a query similar to this one:

INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES ('MyFirstVal', 'MySecondVal'), ('MyThirdVal', 'MyFourthVal'), ('MyFifthVal', 'MySixthVal')

And whichever syntax you might have been using would work. But, SQLCE is not like your regular SQL Server and running this kind of a query against SQLCE would inevitably throw an Exception like similar to this one:

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

And all of a sudden nothing makes sense. Your query is correct but SqlCeCommand is refusing to run this query on a SQLCE database!

Solution is quite simple and there are couple of them. First solution is my choice because I like writing my SQL queries. What you need to do is to create SQL INSERT INTO statement and run it against your SQLCE database. Than repeat it as many times as you need to insert all of your values. Something like this:

string MyInsert
int RowsAffected = 0;
MyInsert = "INSERT INTO MyTable (MyFirstCol, MySecondCol);
VALUES (MyFirstVal, MySecondVal)";
SqlCeCommand MyCommand = new SqlCeCommand(MyInsert, SqlCeConnection);
RowsAffected = MyCommand.ExecuteNonQuery;
MyInsert = "INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyThirdVal, MyFourthVal)";
SqlCeCommand MyCommand = new SqlCeCommand(MyInsert, SqlCeConnection);
RowsAffected = MyCommand.ExecuteNonQuery;
MyInsert = "INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (MyFifthVal, MySixthVal)";
SqlCeCommand MyCommand = new SqlCeCommand(MyInsert, SqlCeConnection);
RowsAffected = MyCommand.ExecuteNonQuery;

While this is OK for inserting fewer values, good practice to follow when inserting hundreds or thousand records is to use parametrized queries using SqlCeCommand. The code to this would be similar to this one

int RowsAffected = 0;
SqlCeCommand MyCommand = SqlCeConnection.CreateCommand();
MyCommand.CommandText = "INSERT INTO MyTable (MyFirstCol, MySecondCol) VALUES (@val01, @val02)";
MyCommand.Parametres.Clear();
MyCommand.Parametres.Add("@val01", "MyFirstValue");
MyCommand.Parametres.Add("@val02", "MySecondValue");
MyCommand.Parametres.Add("@val01", "MyThirdValue");
MyCommand.Parametres.Add("@val02", "MyFourthValue");
MyCommand.Parametres.Add("@val01", "MyFifithValue");
MyCommand.Parametres.Add("@val02", "MySixthValue");
RowsAffected = MyCommand.ExecuteNonQuery();

Both of these methods work just fine when executed against SQLCE database but as I mentioned it earlier, it might be reasonable to use the second example when inserting large sets of data as performance of this example is quite better than of the first example.